explain.depesz.com

PostgreSQL's explain analyze made readable

Result: zYKx

Settings

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 139.411 117,968.288 ↓ 3.6 30,931 1

Nested Loop Left Join (cost=3,264.18..359,749.83 rows=8,649 width=1,649) (actual time=11,206.651..117,968.288 rows=30,931 loops=1)

  • Join Filter: (f.fc_instance_id = e.fc_instance_id)
  • Rows Removed by Join Filter: 3965
  • Buffers: shared hit=315638 read=10192
  • I/O Timings: read=116294.037
2. 43.393 69,143.483 ↓ 3.6 30,931 1

Nested Loop Left Join (cost=3,263.61..309,049.78 rows=8,649 width=788) (actual time=11,156.138..69,143.483 rows=30,931 loops=1)

  • Buffers: shared hit=162592 read=5653
  • I/O Timings: read=68007.121
3. 46.718 68,450.539 ↓ 4.4 30,931 1

Hash Left Join (cost=3,263.18..246,819.66 rows=7,058 width=773) (actual time=11,137.432..68,450.539 rows=30,931 loops=1)

  • Hash Cond: (COALESCE(c.review_status_id, 1) = d.review_status_id)
  • Buffers: shared hit=404 read=5634
  • I/O Timings: read=67621.531
4. 31.174 68,403.809 ↓ 4.4 30,931 1

Hash Left Join (cost=3,251.16..246,749.41 rows=7,058 width=257) (actual time=11,137.361..68,403.809 rows=30,931 loops=1)

  • Hash Cond: (((a.client_code)::text = (c.client_code)::text) AND (a.raw_record_id = c.raw_record_id) AND (a.parser_id = c.parser_id) AND (a.file_id = c.file_id) AND ((a.consumer_code)::text = (c.consumer_code)::text))
  • Buffers: shared hit=403 read=5634
  • I/O Timings: read=67621.531
5. 32.034 68,354.213 ↓ 4.4 30,931 1

Hash Left Join (cost=3,119.85..246,265.20 rows=7,058 width=211) (actual time=11,118.913..68,354.213 rows=30,931 loops=1)

  • Hash Cond: (a.parser_id = b.parser_id)
  • Buffers: shared hit=311 read=5634
  • I/O Timings: read=67621.531
6. 57,209.098 68,320.318 ↓ 4.4 30,931 1

Bitmap Heap Scan on kickout_records a (cost=3,029.88..245,934.42 rows=7,058 width=179) (actual time=11,117.013..68,320.318 rows=30,931 loops=1)

  • Recheck Cond: (effective_begin_date = '2019-08-12'::date)
  • Filter: (((client_code)::text = 'DESHAW'::text) AND (knowledge_end_date > now()))
  • Rows Removed by Filter: 121101
  • Heap Blocks: exact=4855
  • Buffers: shared hit=269 read=5634
  • I/O Timings: read=67621.531
7. 11,111.220 11,111.220 ↑ 1.0 152,032 1

Bitmap Index Scan on kickout_records_effdt_idx (cost=0.00..3,028.12 rows=156,741 width=0) (actual time=11,111.220..11,111.220 rows=152,032 loops=1)

  • Index Cond: (effective_begin_date = '2019-08-12'::date)
  • Buffers: shared hit=269 read=779
  • I/O Timings: read=10792.760
8. 0.364 1.861 ↑ 1.0 923 1

Hash (cost=78.38..78.38 rows=927 width=40) (actual time=1.861..1.861 rows=923 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 78kB
  • Buffers: shared hit=42
9. 1.497 1.497 ↑ 1.0 923 1

Seq Scan on parser_config b (cost=0.00..78.38 rows=927 width=40) (actual time=0.027..1.497 rows=923 loops=1)

  • Filter: ((effective_end_date > '2019-08-12'::date) AND (effective_begin_date <= '2019-08-12'::date) AND (knowledge_end_date > now()))
  • Rows Removed by Filter: 909
  • Buffers: shared hit=42
10. 0.186 18.422 ↑ 1.0 132 1

Hash (cost=128.31..128.31 rows=133 width=77) (actual time=18.422..18.422 rows=132 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 22kB
  • Buffers: shared hit=92
11. 18.236 18.236 ↑ 1.0 132 1

Seq Scan on kickout_records_manifestation c (cost=0.00..128.31 rows=133 width=77) (actual time=0.064..18.236 rows=132 loops=1)

  • Filter: (((client_code)::text = 'DESHAW'::text) AND ((knowledge_end_date IS NULL) OR (knowledge_end_date > now())))
  • Rows Removed by Filter: 1943
  • Buffers: shared hit=92
12. 0.007 0.012 ↑ 45.0 2 1

Hash (cost=10.90..10.90 rows=90 width=520) (actual time=0.012..0.012 rows=2 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
  • Buffers: shared hit=1
13. 0.005 0.005 ↑ 45.0 2 1

Seq Scan on kickout_records_review_status d (cost=0.00..10.90 rows=90 width=520) (actual time=0.005..0.005 rows=2 loops=1)

  • Buffers: shared hit=1
14. 649.551 649.551 ↑ 1.0 1 30,931

Index Scan using fp_instance_pkey on fp_instance f (cost=0.43..8.81 rows=1 width=23) (actual time=0.021..0.021 rows=1 loops=30,931)

  • Index Cond: (a.fp_inst_id = id)
  • Filter: (tt_end IS NULL)
  • Rows Removed by Filter: 3
  • Buffers: shared hit=162188 read=19
  • I/O Timings: read=385.590
15. 48,685.394 48,685.394 ↑ 2.0 1 30,931

Index Scan using raw_file_records_raw_record_id_idx on raw_file_records e (cost=0.57..5.83 rows=2 width=925) (actual time=1.353..1.574 rows=1 loops=30,931)

  • Index Cond: (a.raw_record_id = raw_record_id)
  • Buffers: shared hit=153046 read=4539
  • I/O Timings: read=48286.916