explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Y2p4 : Optimization for: Optimization for: plan #zyIa; plan #2hm

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 2,420.326 22,341.644 ↑ 1.0 1 1

Aggregate (cost=454,644.79..454,644.80 rows=1 width=8) (actual time=22,341.643..22,341.644 rows=1 loops=1)

2. 2,913.882 19,921.318 ↓ 1.2 2,349,055 1

Hash Join (cost=227,431.04..449,668.75 rows=1,990,415 width=8) (actual time=4,345.178..19,921.318 rows=2,349,055 loops=1)

  • Hash Cond: (request.request_type_id = c_3.id)
3. 4,683.769 17,007.230 ↓ 1.2 2,349,055 1

Hash Right Join (cost=227,427.95..444,202.72 rows=1,990,415 width=16) (actual time=4,344.949..17,007.230 rows=2,349,055 loops=1)

  • Hash Cond: (rsh.request_id = request.id)
4. 2,869.548 7,979.738 ↑ 1.0 2,349,226 1

Hash Join (cost=3.90..178,394.24 rows=2,373,875 width=8) (actual time=0.235..7,979.738 rows=2,349,226 loops=1)

  • Hash Cond: (rsh.status_id = c.id)
5. 2,693.377 5,110.152 ↑ 1.0 2,349,226 1

Hash Left Join (cost=2.66..169,760.72 rows=2,373,875 width=16) (actual time=0.165..5,110.152 rows=2,349,226 loops=1)

  • Hash Cond: (rsh.reason_id = c_1.id)
6. 2,416.638 2,416.638 ↑ 1.0 2,349,226 1

Seq Scan on fdc_request_status_history rsh (cost=0.00..160,856.02 rows=2,373,875 width=24) (actual time=0.011..2,416.638 rows=2,349,226 loops=1)

  • Filter: ((statement_timestamp() >= date_from) AND (statement_timestamp() <= date_to))
  • Rows Removed by Filter: 2,535,821
7. 0.024 0.137 ↓ 1.1 25 1

Hash (cost=2.37..2.37 rows=23 width=8) (actual time=0.136..0.137 rows=25 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
8. 0.056 0.113 ↓ 1.1 25 1

Hash Join (cost=1.07..2.37 rows=23 width=8) (actual time=0.069..0.113 rows=25 loops=1)

  • Hash Cond: (c_1.object_type_id = c_2.id)
9. 0.021 0.021 ↓ 1.1 25 1

Seq Scan on fdc_change_status_reason c_1 (cost=0.00..1.23 rows=23 width=16) (actual time=0.007..0.021 rows=25 loops=1)

10. 0.009 0.036 ↑ 1.0 3 1

Hash (cost=1.03..1.03 rows=3 width=8) (actual time=0.035..0.036 rows=3 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
11. 0.027 0.027 ↑ 1.0 3 1

Seq Scan on fdc_object_type c_2 (cost=0.00..1.03 rows=3 width=8) (actual time=0.024..0.027 rows=3 loops=1)

12. 0.017 0.038 ↑ 1.0 11 1

Hash (cost=1.11..1.11 rows=11 width=8) (actual time=0.037..0.038 rows=11 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
13. 0.021 0.021 ↑ 1.0 11 1

Seq Scan on fdc_request_status c (cost=0.00..1.11 rows=11 width=8) (actual time=0.013..0.021 rows=11 loops=1)

14. 1,948.819 4,343.723 ↓ 1.2 2,349,055 1

Hash (cost=188,936.86..188,936.86 rows=1,990,415 width=32) (actual time=4,343.723..4,343.723 rows=2,349,055 loops=1)

  • Buckets: 524,288 Batches: 8 Memory Usage: 20,116kB
15. 2,394.904 2,394.904 ↓ 1.2 2,349,055 1

Index Scan using fdc_request_rtype_i on fdc_request request (cost=16.43..188,936.86 rows=1,990,415 width=32) (actual time=13.379..2,394.904 rows=2,349,055 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_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_140_id(), nsi.c_request_type_900_id(), nsi.c_request_type_901_id(), nsi.c_request_type_074_id()]))
16. 0.102 0.206 ↓ 1.0 95 1

Hash (cost=1.93..1.93 rows=93 width=12) (actual time=0.206..0.206 rows=95 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 13kB
17. 0.104 0.104 ↓ 1.0 95 1

Seq Scan on fdc_request_type c_3 (cost=0.00..1.93 rows=93 width=12) (actual time=0.016..0.104 rows=95 loops=1)

Planning time : 17.972 ms
Execution time : 22,342.035 ms