explain.depesz.com

PostgreSQL's explain analyze made readable

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

Settings

Optimization path:

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 16,064.176 16,064.176 ↓ 155.8 31,168 1

CTE Scan on groupped_by_code_category (cost=62,338.05..62,342.05 rows=200 width=654) (actual time=10,891.172..16,064.176 rows=31,168 loops=1)

2.          

CTE inspections

3. 10.613 440.593 ↓ 79,800.0 79,800 1

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

  • One-Time Filter: (('{252437,252437}'::text[])[2] = '252437'::text)
4. 429.980 429.980 ↓ 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.098..429.980 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. 135.323 1,396.031 ↓ 17,685.5 336,024 1

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

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

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

8. 798.000 798.000 ↑ 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.007..0.010 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. 100.827 481.769 ↓ 888.5 168,810 1

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

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

CTE Scan on founds (cost=0.00..0.38 rows=19 width=8) (actual time=0.354..44.918 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. 558.088 5,761.238 ↓ 8,443.4 1,764,680 1

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

  • Hash Cond: (founds_1.defect_category_id = drdca.id)
15. 763.979 5,202.472 ↓ 8,443.4 1,764,680 1

Nested Loop Left Join (cost=0.91..1,219.67 rows=209 width=631) (actual time=1,684.016..5,202.472 rows=1,764,680 loops=1)

16. 143.813 2,673.813 ↓ 8,443.4 1,764,680 1

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

17. 336.273 2,506.963 ↓ 83,993.2 1,595,870 1

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

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

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

19. 90.580 1,630.288 ↓ 17,685.5 336,024 1

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

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

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

21. 23.037 23.037 ↓ 888.5 168,810 1

CTE Scan on predicteds (cost=0.00..3.80 rows=190 width=568) (actual time=0.003..23.037 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.182 0.678 ↑ 1.0 1,055 1

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

  • Buckets: 2048 Batches: 1 Memory Usage: 80kB
24. 0.496 0.496 ↑ 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.041..0.496 rows=1,055 loops=1)

25.          

CTE groupped_by_code_category

26. 1,418.239 16,043.403 ↓ 155.8 31,168 1

GroupAggregate (cost=12.23..28.73 rows=200 width=654) (actual time=10,891.167..16,043.403 rows=31,168 loops=1)

  • Group Key: d_2.defect_code, d_2.defect_category_code, d_2.booking_id
27. 7,458.534 14,625.164 ↓ 8,443.4 1,764,680 1

Sort (cost=12.23..12.76 rows=209 width=1,172) (actual time=10,890.797..14,625.164 rows=1,764,680 loops=1)

  • Sort Key: d_2.defect_code, d_2.defect_category_code, d_2.booking_id
  • Sort Method: external merge Disk: 339480kB
28. 7,166.630 7,166.630 ↓ 8,443.4 1,764,680 1

CTE Scan on all_defects d_2 (cost=0.00..4.18 rows=209 width=1,172) (actual time=1,684.754..7,166.630 rows=1,764,680 loops=1)

Planning time : 5.864 ms
Execution time : 16,220.359 ms