explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Zy6I : Optimization for: Optimization for: Optimization for: Optimization for: Optimization for: plan #SCUj; plan #7fr1; plan #Nfsb; plan #OJ9B; plan #WMYG

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 7,406.102 7,406.102 ↓ 155.8 31,168 1

CTE Scan on groupped_by_code_category (cost=62,317.06..62,321.06 rows=200 width=556) (actual time=7,387.652..7,406.102 rows=31,168 loops=1)

2.          

CTE inspections

3. 10.964 398.229 ↓ 79,800.0 79,800 1

Result (cost=0.00..60,652.24 rows=1 width=8) (actual time=0.083..398.229 rows=79,800 loops=1)

  • One-Time Filter: (('{252437,252437}'::text[])[2] = '252437'::text)
4. 387.265 387.265 ↓ 79,800.0 79,800 1

Seq Scan on fr_inspections_full i (cost=0.00..60,652.24 rows=1 width=8) (actual time=0.080..387.265 rows=79,800 loops=1)

  • Filter: (((('{0}'::integer[])[1] = 0) OR (retailer_id = 0)) AND ((('{0}'::integer[])[1] = 0) OR (office_id = 0)) AND ((('{0}'::integer[])[1] = 0) OR (executor_id = 0)) AND ((('{0}'::integer[])[1] = 0) OR (supplier_id = 0)) AND ((('{0}'::integer[])[1] = 0) OR (factory_id = 0)) AND ((('{0}'::integer[])[1] = 0) OR (country_of_factory_id = 0)) AND ((('{0}'::integer[])[1] = 0) OR (inspection_type_id = 0)) AND ((('{0}'::integer[])[1] = 0) OR (po_ids && '{0}'::integer[])) AND ((('{0}'::integer[])[1] = 0) OR (po_type_ids && '{0}'::integer[])) AND (inspection_date >= to_timestamp(((('{0}'::integer[])[1] / 1000))::double precision)) AND ((('{252437,252437}'::text[])[2])::integer = ANY (involved_org_ids)))
  • Rows Removed by Filter: 94623
5.          

CTE founds

6. 64.381 1,040.015 ↓ 17,685.5 336,024 1

Nested Loop (cost=0.42..93.01 rows=19 width=66) (actual time=0.163..1,040.015 rows=336,024 loops=1)

7. 417.034 417.034 ↓ 79,800.0 79,800 1

CTE Scan on inspections (cost=0.00..0.02 rows=1 width=8) (actual time=0.085..417.034 rows=79,800 loops=1)

8. 558.600 558.600 ↑ 4.8 4 79,800

Index Scan using idx_fr_inspection_defects_booking_id on fr_inspection_defects d (cost=0.42..92.80 rows=19 width=34) (actual time=0.004..0.007 rows=4 loops=79,800)

  • Index Cond: (booking_id = inspections.booking_id)
  • Filter: ((type_of_defect)::text = 'inspection_defect_list'::text)
  • Rows Removed by Filter: 1
9.          

CTE predicteds

10. 83.552 462.669 ↓ 888.5 168,810 1

Nested Loop (cost=0.29..300.35 rows=190 width=59) (actual time=1.854..462.669 rows=168,810 loops=1)

11. 43.093 43.093 ↓ 17,685.5 336,024 1

CTE Scan on founds (cost=0.00..0.38 rows=19 width=8) (actual time=0.163..43.093 rows=336,024 loops=1)

12. 336.024 336.024 ↑ 10.0 1 336,024

Index Scan using idx_fr_predicted_defects_booking_id on fr_predicted_defects d_1 (cost=0.29..15.69 rows=10 width=27) (actual time=0.001..0.001 rows=1 loops=336,024)

  • Index Cond: (booking_id = founds.booking_id)
  • Filter: ((type_of_defect)::text = 'inspection_defect_list'::text)
13.          

CTE all_defects

14. 553.828 5,440.533 ↓ 8,443.4 1,764,680 1

Hash Left Join (cost=42.09..1,263.73 rows=209 width=1,180) (actual time=1,305.369..5,440.533 rows=1,764,680 loops=1)

  • Hash Cond: (founds_1.defect_category_id = drdca.id)
15. 831.771 4,886.337 ↓ 8,443.4 1,764,680 1

Nested Loop Left Join (cost=0.91..1,219.67 rows=209 width=631) (actual time=1,304.978..4,886.337 rows=1,764,680 loops=1)

16. 144.194 2,289.886 ↓ 8,443.4 1,764,680 1

Append (cost=0.62..9.31 rows=209 width=568) (actual time=1,304.963..2,289.886 rows=1,764,680 loops=1)

17. 346.384 2,120.536 ↓ 83,993.2 1,595,870 1

Hash Join (cost=0.62..5.32 rows=19 width=568) (actual time=1,304.963..2,120.536 rows=1,595,870 loops=1)

  • Hash Cond: (predicteds_1.booking_id = founds_1.booking_id)
18. 523.282 523.282 ↓ 888.5 168,810 1

CTE Scan on predicteds predicteds_1 (cost=0.00..3.80 rows=190 width=8) (actual time=1.856..523.282 rows=168,810 loops=1)

19. 74.871 1,250.870 ↓ 17,685.5 336,024 1

Hash (cost=0.38..0.38 rows=19 width=568) (actual time=1,250.870..1,250.870 rows=336,024 loops=1)

  • Buckets: 262144 (originally 1024) Batches: 2 (originally 1) Memory Usage: 14337kB
20. 1,175.999 1,175.999 ↓ 17,685.5 336,024 1

CTE Scan on founds founds_1 (cost=0.00..0.38 rows=19 width=568) (actual time=0.000..1,175.999 rows=336,024 loops=1)

21. 25.156 25.156 ↓ 888.5 168,810 1

CTE Scan on predicteds (cost=0.00..3.80 rows=190 width=568) (actual time=0.003..25.156 rows=168,810 loops=1)

22. 1,764.680 1,764.680 ↑ 1.0 1 1,764,680

Index Scan using idx_dr_defect_code_id on dr_defect_code drdco (cost=0.29..5.78 rows=1 width=67) (actual time=0.001..0.001 rows=1 loops=1,764,680)

  • Index Cond: (founds_1.defect_code_id = id)
23. 0.154 0.368 ↑ 1.0 1,055 1

Hash (cost=27.75..27.75 rows=1,075 width=27) (actual time=0.368..0.368 rows=1,055 loops=1)

  • Buckets: 2048 Batches: 1 Memory Usage: 80kB
24. 0.214 0.214 ↑ 1.0 1,055 1

Seq Scan on dr_defect_category drdca (cost=0.00..27.75 rows=1,075 width=27) (actual time=0.022..0.214 rows=1,055 loops=1)

25.          

CTE groupped_by_code_category

26. 691.061 7,397.509 ↓ 155.8 31,168 1

HashAggregate (cost=5.75..7.75 rows=200 width=556) (actual time=7,387.649..7,397.509 rows=31,168 loops=1)

  • Group Key: d_2.defect_code, d_2.defect_category_code, d_2.booking_id
27. 6,706.448 6,706.448 ↓ 8,443.4 1,764,680 1

CTE Scan on all_defects d_2 (cost=0.00..4.18 rows=209 width=556) (actual time=1,305.371..6,706.448 rows=1,764,680 loops=1)

Planning time : 1.097 ms
Execution time : 7,493.070 ms