explain.depesz.com

PostgreSQL's explain analyze made readable

Result: dKKW : Optimization for: plan #AdQm

Settings

Optimization path:

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 7.348 3,595.236 ↑ 1.0 1 1

Aggregate (cost=99,370.47..99,370.48 rows=1 width=8) (actual time=3,595.235..3,595.236 rows=1 loops=1)

2. 11.601 3,587.888 ↑ 1.0 10,000 1

Limit (cost=22.22..99,245.47 rows=10,000 width=8) (actual time=821.622..3,587.888 rows=10,000 loops=1)

3.          

Initplan (forLimit)

4. 0.057 0.057 ↑ 1.0 1 1

Result (cost=0.00..0.01 rows=1 width=8) (actual time=0.055..0.057 rows=1 loops=1)

5. 22.939 3,576.230 ↑ 199.0 10,000 1

Nested Loop Left Join (cost=22.21..19,750,310.03 rows=1,990,490 width=8) (actual time=821.619..3,576.230 rows=10,000 loops=1)

6. 13.505 3,473.291 ↑ 199.0 10,000 1

Merge Semi Join (cost=20.25..6,803,583.97 rows=1,990,490 width=8) (actual time=821.364..3,473.291 rows=10,000 loops=1)

  • Merge Cond: (c.id = c_4.id)
7. 26.274 236.459 ↑ 199.0 10,000 1

Nested Loop Left Join (cost=19.96..6,585,963.65 rows=1,990,490 width=24) (actual time=13.332..236.459 rows=10,000 loops=1)

8. 15.030 40.185 ↑ 199.0 10,000 1

Merge Join (cost=19.12..213,785.14 rows=1,990,490 width=32) (actual time=13.264..40.185 rows=10,000 loops=1)

  • Merge Cond: (request.request_type_id = c.id)
9. 25.111 25.111 ↑ 199.0 10,000 1

Index Scan using fdc_request_rtype_i on fdc_request request (cost=16.93..188,896.11 rows=1,990,490 width=40) (actual time=13.240..25.111 rows=10,000 loops=1)

  • Index Cond: (request_type_id = ANY (ARRAY[nsi.c_request_type_000_id(), nsi.c_request_type_001_id(), nsi.c_request_type_002_id(), nsi.c_request_type_003_id(), nsi.c_request_type_004_id(), nsi.c_request_type_005_id(), nsi.c_request_type_006_id(), nsi.c_request_type_007_id(), nsi.c_request_type_008_id(), nsi.c_request_type_009_id(), nsi.c_request_type_010_id(), nsi.c_request_type_011_id(), nsi.c_request_type_012_id(), nsi.c_request_type_022_id(), nsi.c_request_type_024_id(), nsi.c_request_type_025_id(), nsi.c_request_type_026_id(), nsi.c_request_type_036_id(), nsi.c_request_type_037_id(), nsi.c_request_type_038_id(), nsi.c_request_type_039_id(), nsi.c_request_type_040_id(), nsi.c_request_type_041_id(), nsi.c_request_type_042_id(), nsi.c_request_type_043_id(), nsi.c_request_type_044_id(), nsi.c_request_type_045_id(), nsi.c_request_type_046_id(), nsi.c_request_type_047_id(), nsi.c_request_type_048_id(), nsi.c_request_type_052_id(), nsi.c_request_type_053_id(), nsi.c_request_type_054_id(), nsi.c_request_type_055_id(), nsi.c_request_type_056_id(), nsi.c_request_type_100_id(), nsi.c_request_type_101_id(), nsi.c_request_type_111_id(), nsi.c_request_type_113_id(), nsi.c_request_type_114_id(), nsi.c_request_type_115_id(), nsi.c_request_type_116_id(), nsi.c_request_type_117_id(), nsi.c_request_type_118_id(), nsi.c_request_type_119_id(), nsi.c_request_type_120_id(), nsi.c_request_type_121_id(), nsi.c_request_type_122_id(), nsi.c_request_type_123_id(), nsi.c_request_type_124_id(), nsi.c_request_type_125_id(), nsi.c_request_type_127_id(), nsi.c_request_type_131_id(), nsi.c_request_type_136_id(), nsi.c_request_type_137_id(), nsi.c_request_type_138_id(), nsi.c_request_type_139_id(), nsi.c_request_type_141_id(), nsi.c_request_type_142_id(), nsi.c_request_type_143_id(), nsi.c_request_type_152_id(), nsi.c_request_type_153_id(), nsi.c_request_type_140_id(), nsi.c_request_type_900_id(), nsi.c_request_type_901_id(), nsi.c_request_type_074_id()]))
10. 0.044 0.044 ↑ 2.4 38 1

Index Scan using fdc_request_type_pk on fdc_request_type c (cost=0.14..13.54 rows=93 width=12) (actual time=0.007..0.044 rows=38 loops=1)

11. 30.000 170.000 ↑ 2.0 1 10,000

Nested Loop (cost=0.83..3.18 rows=2 width=8) (actual time=0.014..0.017 rows=1 loops=10,000)

12. 40.000 120.000 ↑ 2.0 1 10,000

Nested Loop Left Join (cost=0.70..2.87 rows=2 width=16) (actual time=0.009..0.012 rows=1 loops=10,000)

13. 60.000 60.000 ↑ 2.0 1 10,000

Index Scan using fdc_request_status_history_request_i on fdc_request_status_history rsh (cost=0.43..0.67 rows=2 width=24) (actual time=0.005..0.006 rows=1 loops=10,000)

  • Index Cond: (request_id = request.id)
  • Filter: ((statement_timestamp() >= date_from) AND (statement_timestamp() <= date_to))
  • Rows Removed by Filter: 1
14. 10.000 20.000 ↓ 0.0 0 10,000

Nested Loop (cost=0.27..1.09 rows=1 width=8) (actual time=0.002..0.002 rows=0 loops=10,000)

15. 10.000 10.000 ↓ 0.0 0 10,000

Index Scan using fdc_change_status_reason_pk on fdc_change_status_reason c_2 (cost=0.14..0.16 rows=1 width=16) (actual time=0.001..0.001 rows=0 loops=10,000)

  • Index Cond: (id = rsh.reason_id)
16. 0.000 0.000 ↓ 0.0 0

Index Only Scan using fdc_object_type_pk on fdc_object_type c_3 (cost=0.13..0.67 rows=1 width=8) (never executed)

  • Index Cond: (id = c_2.object_type_id)
  • Heap Fetches: 0
17. 20.000 20.000 ↑ 1.0 1 10,000

Index Only Scan using fdc_request_status_pk on fdc_request_status c_1 (cost=0.14..0.15 rows=1 width=8) (actual time=0.002..0.002 rows=1 loops=10,000)

  • Index Cond: (id = rsh.status_id)
  • Heap Fetches: 10000
18. 11.545 3,223.327 ↑ 3.0 68 1

Nested Loop (cost=0.29..169,623.32 rows=201 width=16) (actual time=1.175..3,223.327 rows=68 loops=1)

19. 1,585.119 3,169.912 ↑ 3.6 4,187 1

Nested Loop (cost=0.14..166,897.21 rows=14,999 width=24) (actual time=1.051..3,169.912 rows=4,187 loops=1)

  • Join Filter: (gra.authority_id = c_5.id)
  • Rows Removed by Join Filter: 2188320
20. 9.424 30.023 ↑ 4.9 70 1

Nested Loop (cost=0.14..4,232.75 rows=344 width=32) (actual time=0.659..30.023 rows=70 loops=1)

  • Join Filter: (rta.authority_id = c_5.id)
  • Rows Removed by Join Filter: 12971
21. 4.999 10.309 ↑ 5.0 70 1

Nested Loop (cost=0.14..505.35 rows=348 width=24) (actual time=0.063..10.309 rows=70 loops=1)

  • Join Filter: (rta.request_type_id = c_4.id)
  • Rows Removed by Join Filter: 6859
22. 0.180 0.180 ↑ 2.4 38 1

Index Only Scan using fdc_request_type_pk on fdc_request_type c_4 (cost=0.14..13.54 rows=93 width=8) (actual time=0.009..0.180 rows=38 loops=1)

  • Heap Fetches: 38
23. 4.968 5.130 ↑ 1.9 182 38

Materialize (cost=0.00..7.22 rows=348 width=16) (actual time=0.001..0.135 rows=182 loops=38)

24. 0.162 0.162 ↑ 1.9 186 1

Seq Scan on fdc_request_type_authority rta (cost=0.00..5.48 rows=348 width=16) (actual time=0.015..0.162 rows=186 loops=1)

25. 9.595 10.290 ↑ 3.8 186 70

Materialize (cost=0.00..17.75 rows=711 width=16) (actual time=0.001..0.147 rows=186 loops=70)

26. 0.695 0.695 ↑ 1.2 608 1

Seq Scan on fdc_authority c_5 (cost=0.00..14.20 rows=711 width=16) (actual time=0.011..0.695 rows=608 loops=1)

  • Filter: (NOT is_archived)
  • Rows Removed by Filter: 8
27. 1,519.417 1,554.770 ↑ 1.0 31,322 70

Materialize (cost=0.00..749.91 rows=31,394 width=16) (actual time=0.001..22.211 rows=31,322 loops=70)

28. 35.353 35.353 ↓ 1.0 31,774 1

Seq Scan on fdc_group_authority gra (cost=0.00..592.94 rows=31,394 width=16) (actual time=0.027..35.353 rows=31,774 loops=1)

29. 41.870 41.870 ↓ 0.0 0 4,187

Index Only Scan using fdc_group_member_u_g_date_uk on fdc_group_member grm (cost=0.14..0.17 rows=1 width=8) (actual time=0.010..0.010 rows=0 loops=4,187)

  • Index Cond: ((user_id = $0) AND (group_id = gra.group_id))
  • Heap Fetches: 68
30. 7.844 80.000 ↓ 0.0 0 10,000

Nested Loop Left Join (cost=1.96..6.40 rows=10 width=8) (actual time=0.007..0.008 rows=0 loops=10,000)

31. 15.798 50.000 ↓ 0.0 0 10,000

Nested Loop Left Join (cost=0.56..1.32 rows=1 width=16) (actual time=0.004..0.005 rows=0 loops=10,000)

32. 15.034 30.000 ↓ 0.0 0 10,000

Nested Loop Left Join (cost=0.42..0.65 rows=1 width=16) (actual time=0.003..0.003 rows=0 loops=10,000)

33. 10.000 10.000 ↓ 0.0 0 10,000

Index Scan using fdc_user_pk on fdc_user u (cost=0.27..0.29 rows=1 width=32) (actual time=0.001..0.001 rows=0 loops=10,000)

  • Index Cond: (id = request.user_add)
34. 4.966 4.966 ↑ 1.0 1 382

Index Scan using fdc_user_password_user_date_uk on fdc_user_password up (cost=0.14..0.35 rows=1 width=8) (actual time=0.012..0.013 rows=1 loops=382)

  • Index Cond: (user_id = u.id)
  • Filter: ((statement_timestamp() >= date_from) AND (statement_timestamp() <= date_to))
  • Rows Removed by Filter: 0
35. 4.202 4.202 ↑ 1.0 1 382

Index Scan using fdc_person_root_date_uk on fdc_person per (cost=0.14..0.66 rows=1 width=16) (actual time=0.010..0.011 rows=1 loops=382)

  • Index Cond: (person_root_id = u.person_root_id)
  • Filter: ((statement_timestamp() >= date_from) AND (statement_timestamp() <= date_to))
36. 1.528 22.156 ↑ 65.0 1 382

Nested Loop Left Join (cost=1.40..4.43 rows=65 width=8) (actual time=0.048..0.058 rows=1 loops=382)

37. 1.910 14.898 ↑ 8.0 1 382

Nested Loop Left Join (cost=1.26..2.43 rows=8 width=16) (actual time=0.032..0.039 rows=1 loops=382)

38. 1.146 9.168 ↑ 1.0 1 382

Nested Loop Left Join (cost=1.11..2.18 rows=1 width=24) (actual time=0.020..0.024 rows=1 loops=382)

39. 1.146 6.876 ↑ 1.0 1 382

Nested Loop Left Join (cost=0.83..1.56 rows=1 width=24) (actual time=0.015..0.018 rows=1 loops=382)

40. 1.910 4.584 ↑ 1.0 1 382

Nested Loop (cost=0.55..0.93 rows=1 width=24) (actual time=0.009..0.012 rows=1 loops=382)

41. 1.528 1.528 ↑ 1.0 1 382

Index Scan using fdc_official_person_pk on fdc_official_person ofp (cost=0.27..0.31 rows=1 width=24) (actual time=0.003..0.004 rows=1 loops=382)

  • Index Cond: (id = per.id)
42. 1.146 1.146 ↑ 1.0 1 382

Index Only Scan using fdc_person_pk on fdc_person per_1 (cost=0.28..0.62 rows=1 width=8) (actual time=0.003..0.003 rows=1 loops=382)

  • Index Cond: (id = ofp.id)
  • Heap Fetches: 382
43. 1.146 1.146 ↑ 1.0 1 382

Index Scan using fdc_person_pk on fdc_person ind_ver (cost=0.28..0.62 rows=1 width=16) (actual time=0.003..0.003 rows=1 loops=382)

  • Index Cond: (id = ofp.individual_id)
44. 1.146 1.146 ↑ 1.0 1 382

Index Scan using fdc_person_pk on fdc_person leg_ver (cost=0.28..0.62 rows=1 width=16) (actual time=0.003..0.003 rows=1 loops=382)

  • Index Cond: (id = ofp.legal_person_id)
45. 3.820 3.820 ↑ 1.0 1 382

Index Scan using fdc_person_root_date_uk on fdc_person ind_per (cost=0.14..0.24 rows=1 width=16) (actual time=0.009..0.010 rows=1 loops=382)

  • Index Cond: (person_root_id = ind_ver.person_root_id)
  • Filter: ((statement_timestamp() >= date_from) AND (statement_timestamp() <= date_to))
46. 5.730 5.730 ↑ 1.0 1 382

Index Scan using fdc_person_root_date_uk on fdc_person leg_per (cost=0.14..0.24 rows=1 width=16) (actual time=0.014..0.015 rows=1 loops=382)

  • Index Cond: (person_root_id = leg_ver.person_root_id)
  • Filter: ((statement_timestamp() >= date_from) AND (statement_timestamp() <= date_to))
  • Rows Removed by Filter: 12
Planning time : 31.593 ms
Execution time : 3,596.195 ms