explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Nm02k : Optimization for: Optimization for: plan #AdQm; plan #dKKW

Settings

Optimization path:

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 7.280 3,184.640 ↑ 1.0 1 1

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

2. 11.491 3,177.360 ↑ 1.0 10,000 1

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

3.          

Initplan (forLimit)

4. 0.066 0.066 ↑ 1.0 1 1

Result (cost=0.00..0.01 rows=1 width=8) (actual time=0.064..0.066 rows=1 loops=1)

5. 22.048 3,165.803 ↑ 199.0 10,000 1

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

6. 13.313 3,063.755 ↑ 199.0 10,000 1

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

  • Merge Cond: (c.id = c_4.id)
7. 27.016 238.394 ↑ 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.971..238.394 rows=10,000 loops=1)

8. 15.295 41.378 ↑ 199.0 10,000 1

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

  • Merge Cond: (request.request_type_id = c.id)
9. 26.017 26.017 ↑ 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.880..26.017 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.066 0.066 ↑ 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.008..0.066 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.010..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. 8.126 2,812.048 ↑ 3.0 68 1

Nested Loop (cost=0.29..169,623.32 rows=201 width=16) (actual time=1.180..2,812.048 rows=68 loops=1)

19. 1,379.626 2,766.239 ↑ 3.6 4,187 1

Nested Loop (cost=0.14..166,897.21 rows=14,999 width=24) (actual time=1.050..2,766.239 rows=4,187 loops=1)

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

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

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

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

  • Join Filter: (rta.request_type_id = c_4.id)
  • Rows Removed by Join Filter: 6859
22. 0.155 0.155 ↑ 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.014..0.155 rows=38 loops=1)

  • Heap Fetches: 38
23. 4.307 4.484 ↑ 1.9 182 38

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

24. 0.177 0.177 ↑ 1.9 186 1

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

25. 8.281 8.890 ↑ 3.8 186 70

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

26. 0.609 0.609 ↑ 1.2 608 1

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

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

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

28. 36.861 36.861 ↓ 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.013..36.861 rows=31,774 loops=1)

29. 37.683 37.683 ↓ 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.009..0.009 rows=0 loops=4,187)

  • Index Cond: ((user_id = $0) AND (group_id = gra.group_id))
  • Heap Fetches: 68
30. 8.226 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 21.774 ↑ 65.0 1 382

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

37. 1.528 14.516 ↑ 8.0 1 382

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

38. 1.528 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.494 ↑ 1.0 1 382

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

40. 1.528 4.202 ↑ 1.0 1 382

Nested Loop (cost=0.55..0.93 rows=1 width=24) (actual time=0.009..0.011 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 : 32.547 ms
Execution time : 3,185.597 ms