explain.depesz.com

PostgreSQL's explain analyze made readable

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

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 7.387 215.907 ↑ 1.0 1 1

Aggregate (cost=8,389.28..8,389.29 rows=1 width=8) (actual time=215.906..215.907 rows=1 loops=1)

2. 12.629 208.520 ↑ 1.0 10,000 1

Limit (cost=80.72..8,264.28 rows=10,000 width=8) (actual time=30.814..208.520 rows=10,000 loops=1)

3.          

Initplan (forLimit)

4. 0.067 0.067 ↑ 1.0 1 1

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

5. 14.382 195.824 ↑ 199.0 10,000 1

Merge Semi Join (cost=80.71..1,629,011.27 rows=1,990,490 width=8) (actual time=30.811..195.824 rows=10,000 loops=1)

  • Merge Cond: (c.id = c_1.id)
6. 25.869 122.745 ↑ 199.0 10,000 1

Nested Loop Left Join (cost=19.56..1,578,577.29 rows=1,990,490 width=16) (actual time=9.909..122.745 rows=10,000 loops=1)

7. 15.593 36.876 ↑ 199.0 10,000 1

Merge Join (cost=19.12..213,785.14 rows=1,990,490 width=24) (actual time=9.892..36.876 rows=10,000 loops=1)

  • Merge Cond: (request.request_type_id = c.id)
8. 21.243 21.243 ↑ 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=32) (actual time=9.875..21.243 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()]))
9. 0.040 0.040 ↑ 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.006..0.040 rows=38 loops=1)

10. 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=16) (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
11. 24.842 58.697 ↑ 3.0 68 1

Nested Loop (cost=61.15..2,436.98 rows=201 width=16) (actual time=3.338..58.697 rows=68 loops=1)

  • Join Filter: (gra.authority_id = c_2.id)
  • Rows Removed by Join Filter: 31820
12. 0.197 1.795 ↑ 4.9 70 1

Merge Join (cost=44.12..60.58 rows=344 width=32) (actual time=1.433..1.795 rows=70 loops=1)

  • Merge Cond: (c_1.id = rta.request_type_id)
13. 0.092 0.092 ↑ 2.4 38 1

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

  • Heap Fetches: 38
14. 0.234 1.506 ↑ 4.9 70 1

Sort (cost=43.97..44.83 rows=344 width=24) (actual time=1.421..1.506 rows=70 loops=1)

  • Sort Key: rta.request_type_id
  • Sort Method: quicksort Memory: 39kB
15. 0.244 1.272 ↑ 1.8 186 1

Hash Join (cost=23.09..29.48 rows=344 width=24) (actual time=0.930..1.272 rows=186 loops=1)

  • Hash Cond: (rta.authority_id = c_2.id)
16. 0.120 0.120 ↑ 1.9 186 1

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

17. 0.445 0.908 ↑ 1.2 609 1

Hash (cost=14.20..14.20 rows=711 width=16) (actual time=0.907..0.908 rows=609 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 37kB
18. 0.463 0.463 ↑ 1.2 609 1

Seq Scan on fdc_authority c_2 (cost=0.00..14.20 rows=711 width=16) (actual time=0.009..0.463 rows=609 loops=1)

  • Filter: (NOT is_archived)
  • Rows Removed by Filter: 8
19. 23.240 32.060 ↓ 1.1 456 70

Materialize (cost=17.03..205.09 rows=421 width=8) (actual time=0.008..0.458 rows=456 loops=70)

20. 4.076 8.820 ↓ 1.1 462 1

Merge Join (cost=17.03..202.99 rows=421 width=8) (actual time=0.512..8.820 rows=462 loops=1)

  • Merge Cond: (gra.group_id = grm.group_id)
21. 4.385 4.385 ↑ 6.4 4,874 1

Index Scan using fdc_group_authority_gr_i on fdc_group_authority gra (cost=0.29..1,264.27 rows=31,394 width=16) (actual time=0.012..4.385 rows=4,874 loops=1)

22. 0.272 0.359 ↓ 54.2 434 1

Sort (cost=16.74..16.76 rows=8 width=8) (actual time=0.095..0.359 rows=434 loops=1)

  • Sort Key: grm.group_id
  • Sort Method: quicksort Memory: 25kB
23. 0.087 0.087 ↑ 4.0 2 1

Index Scan using fdc_group_member_u_i on fdc_group_member grm (cost=0.28..16.62 rows=8 width=8) (actual time=0.082..0.087 rows=2 loops=1)

  • Index Cond: (user_id = $0)
Planning time : 14.951 ms
Execution time : 216.183 ms