explain.depesz.com

PostgreSQL's explain analyze made readable

Result: j6r3 : Optimization for: plan #zYKx

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 18.968 6,054.354 ↑ 1.4 15,313 1

Nested Loop Left Join (cost=180,770.68..320,450.26 rows=21,111 width=3,417) (actual time=1,161.067..6,054.354 rows=15,313 loops=1)

  • Join Filter: (f.fc_instance_id = e.fc_instance_id)
  • Rows Removed by Join Filter: 306
2.          

CTE a

3. 26.584 453.745 ↑ 1.1 15,313 1

Bitmap Heap Scan on kickout_records (cost=55,935.89..90,943.90 rows=17,405 width=139) (actual time=427.280..453.745 rows=15,313 loops=1)

  • Recheck Cond: ((effective_begin_date = '2019-08-12'::date) AND ((client_code)::text = 'BALY'::text))
  • Rows Removed by Index Recheck: 1389
  • Filter: (knowledge_end_date > now())
  • Heap Blocks: exact=554
4. 1.611 427.161 ↓ 0.0 0 1

BitmapAnd (cost=55,935.89..55,935.89 rows=18,617 width=0) (actual time=427.161..427.161 rows=0 loops=1)

5. 9.849 9.849 ↑ 1.1 152,032 1

Bitmap Index Scan on kickout_records_effdt_idx (cost=0.00..3,124.61 rows=169,607 width=0) (actual time=9.849..9.849 rows=152,032 loops=1)

  • Index Cond: (effective_begin_date = '2019-08-12'::date)
6. 415.701 415.701 ↑ 1.0 3,558,755 1

Bitmap Index Scan on client_code_idx (cost=0.00..52,802.32 rows=3,580,235 width=0) (actual time=415.701..415.701 rows=3,558,755 loops=1)

  • Index Cond: ((client_code)::text = 'BALY'::text)
7. 9.216 1,303.669 ↑ 1.4 15,313 1

Hash Left Join (cost=89,826.21..106,017.91 rows=21,111 width=2,516) (actual time=1,158.471..1,303.669 rows=15,313 loops=1)

  • Hash Cond: (COALESCE(c.review_status_id, 1) = d.review_status_id)
8. 8.592 1,294.441 ↑ 1.4 15,313 1

Hash Left Join (cost=89,814.18..105,831.72 rows=21,111 width=2,000) (actual time=1,158.441..1,294.441 rows=15,313 loops=1)

  • Hash Cond: (a.parser_id = b.parser_id)
9. 73.265 1,284.229 ↑ 1.4 15,313 1

Hash Left Join (cost=89,724.21..105,022.12 rows=21,111 width=1,968) (actual time=1,156.798..1,284.229 rows=15,313 loops=1)

  • Hash Cond: (a.fp_inst_id = f.id)
10. 10.019 483.635 ↑ 1.1 15,313 1

Hash Left Join (cost=169.63..1,387.99 rows=17,405 width=1,952) (actual time=428.812..483.635 rows=15,313 loops=1)

  • Hash Cond: ((a.raw_record_id = c.raw_record_id) AND (a.parser_id = c.parser_id) AND (a.file_id = c.file_id) AND ((a.client_code)::text = (c.client_code)::text) AND ((a.consumer_code)::text = (c.consumer_code)::t
11. 472.132 472.132 ↑ 1.1 15,313 1

CTE Scan on a (cost=0.00..348.10 rows=17,405 width=1,906) (actual time=427.286..472.132 rows=15,313 loops=1)

12. 0.724 1.484 ↑ 1.0 2,067 1

Hash (cost=123.12..123.12 rows=2,067 width=77) (actual time=1.484..1.484 rows=2,067 loops=1)

  • Buckets: 4096 Batches: 1 Memory Usage: 276kB
13. 0.760 0.760 ↑ 1.0 2,067 1

Seq Scan on kickout_records_manifestation c (cost=0.00..123.12 rows=2,067 width=77) (actual time=0.059..0.760 rows=2,067 loops=1)

  • Filter: ((knowledge_end_date IS NULL) OR (knowledge_end_date > now()))
  • Rows Removed by Filter: 8
14. 110.633 727.329 ↑ 1.0 373,491 1

Hash (cost=82,640.38..82,640.38 rows=376,576 width=24) (actual time=727.329..727.329 rows=373,491 loops=1)

  • Buckets: 65536 Batches: 8 Memory Usage: 3151kB
15. 616.696 616.696 ↑ 1.0 373,491 1

Seq Scan on fp_instance f (cost=0.00..82,640.38 rows=376,576 width=24) (actual time=0.100..616.696 rows=373,491 loops=1)

  • Filter: (tt_end IS NULL)
  • Rows Removed by Filter: 1109496
16. 0.359 1.620 ↑ 1.0 923 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 78kB
17. 1.261 1.261 ↑ 1.0 923 1

Seq Scan on parser_config b (cost=0.00..78.38 rows=927 width=40) (actual time=0.026..1.261 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: 911
18. 0.005 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
19. 0.007 0.007 ↑ 45.0 2 1

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

20. 4,731.717 4,731.717 ↑ 2.0 1 15,313

Index Scan using raw_file_records_raw_record_id_idx on raw_file_records e (cost=0.57..5.82 rows=2 width=925) (actual time=0.296..0.309 rows=1 loops=15,313)

  • Index Cond: (a.raw_record_id = raw_record_id)
Planning time : 0.849 ms
Execution time : 6,058.181 ms