explain.depesz.com

PostgreSQL's explain analyze made readable

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

Settings

Optimization path:

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 0.019 15,900.875 ↑ 1.0 3 1

Hash Join (cost=62,394.25..62,394.35 rows=3 width=136) (actual time=15,900.587..15,900.875 rows=3 loops=1)

  • Hash Cond: (unique_defects."group" = defect_details."group")
2.          

CTE inspections

3. 12.546 387.145 ↓ 79,800.0 79,800 1

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

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

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

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

CTE Scan on inspections (cost=0.00..0.02 rows=1 width=8) (actual time=0.066..405.933 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. 84.253 464.359 ↓ 888.5 168,810 1

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

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

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

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

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

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

16. 144.027 2,273.842 ↓ 8,443.4 1,764,680 1

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

17. 344.156 2,106.736 ↓ 83,993.2 1,595,870 1

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

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

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

19. 72.712 1,239.025 ↓ 17,685.5 336,024 1

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

  • Buckets: 262144 (originally 1024) Batches: 2 (originally 1) Memory Usage: 14337kB
20. 1,166.313 1,166.313 ↓ 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,166.313 rows=336,024 loops=1)

21. 23.079 23.079 ↓ 888.5 168,810 1

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

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

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

25.          

CTE groupped_by_code_category

26. 1,414.941 15,827.798 ↓ 155.8 31,168 1

GroupAggregate (cost=12.23..28.73 rows=200 width=654) (actual time=10,659.861..15,827.798 rows=31,168 loops=1)

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

Sort (cost=12.23..12.76 rows=209 width=1,172) (actual time=10,659.611..14,412.857 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. 6,903.396 6,903.396 ↓ 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,290.710..6,903.396 rows=1,764,680 loops=1)

29.          

CTE groupped_by_code_category_with_multual

30. 18.018 15,899.560 ↓ 1.0 205 1

GroupAggregate (cost=11.64..22.14 rows=200 width=756) (actual time=15,877.991..15,899.560 rows=205 loops=1)

  • Group Key: groupped_by_code_category.defect_code, groupped_by_code_category.defect_category_code
31. 28.663 15,881.542 ↓ 155.8 31,168 1

Sort (cost=11.64..12.14 rows=200 width=654) (actual time=15,877.963..15,881.542 rows=31,168 loops=1)

  • Sort Key: groupped_by_code_category.defect_code, groupped_by_code_category.defect_category_code
  • Sort Method: quicksort Memory: 12979kB
32. 15,852.879 15,852.879 ↓ 155.8 31,168 1

CTE Scan on groupped_by_code_category (cost=0.00..4.00 rows=200 width=654) (actual time=10,659.865..15,852.879 rows=31,168 loops=1)

33.          

CTE unique_defects

34. 0.002 15,900.250 ↑ 1.0 3 1

Append (cost=5.01..15.08 rows=3 width=104) (actual time=15,899.966..15,900.250 rows=3 loops=1)

35. 0.144 15,899.965 ↑ 1.0 1 1

Aggregate (cost=5.01..5.02 rows=1 width=104) (actual time=15,899.965..15,899.965 rows=1 loops=1)

36. 15,899.821 15,899.821 ↓ 2.1 144 1

CTE Scan on groupped_by_code_category_with_multual (cost=0.00..4.50 rows=67 width=580) (actual time=15,878.241..15,899.821 rows=144 loops=1)

  • Filter: (predicted_defect_num > '0'::numeric)
  • Rows Removed by Filter: 61
37. 0.152 0.201 ↑ 1.0 1 1

Aggregate (cost=5.01..5.02 rows=1 width=104) (actual time=0.201..0.201 rows=1 loops=1)

38. 0.049 0.049 ↓ 2.8 189 1

CTE Scan on groupped_by_code_category_with_multual groupped_by_code_category_with_multual_1 (cost=0.00..4.50 rows=67 width=580) (actual time=0.002..0.049 rows=189 loops=1)

  • Filter: (found_defect_num > '0'::numeric)
  • Rows Removed by Filter: 16
39. 0.046 0.082 ↑ 1.0 1 1

Aggregate (cost=5.01..5.02 rows=1 width=104) (actual time=0.082..0.082 rows=1 loops=1)

40. 0.036 0.036 ↓ 1.1 75 1

CTE Scan on groupped_by_code_category_with_multual groupped_by_code_category_with_multual_2 (cost=0.00..4.50 rows=67 width=532) (actual time=0.004..0.036 rows=75 loops=1)

  • Filter: (matched_defect_num > 0)
  • Rows Removed by Filter: 130
41.          

CTE defect_details

42. 0.002 0.509 ↑ 1.0 3 1

Append (cost=6.27..18.88 rows=3 width=64) (actual time=0.219..0.509 rows=3 loops=1)

43. 0.049 0.218 ↑ 1.0 1 1

Aggregate (cost=6.27..6.28 rows=1 width=64) (actual time=0.218..0.218 rows=1 loops=1)

44. 0.017 0.169 ↑ 1.0 10 1

Subquery Scan on x (cost=6.12..6.24 rows=10 width=668) (actual time=0.164..0.169 rows=10 loops=1)

45. 0.006 0.152 ↑ 1.0 10 1

Limit (cost=6.12..6.14 rows=10 width=645) (actual time=0.148..0.152 rows=10 loops=1)

46. 0.053 0.146 ↑ 6.7 10 1

Sort (cost=6.12..6.28 rows=67 width=645) (actual time=0.146..0.146 rows=10 loops=1)

  • Sort Key: groupped_by_code_category_with_multual_3.predicted_defect_num DESC, groupped_by_code_category_with_multual_3.defect_code, groupped_by_code_category_with_multual_3.defect_category_code
  • Sort Method: top-N heapsort Memory: 28kB
47. 0.093 0.093 ↓ 2.1 144 1

CTE Scan on groupped_by_code_category_with_multual groupped_by_code_category_with_multual_3 (cost=0.00..4.67 rows=67 width=645) (actual time=0.014..0.093 rows=144 loops=1)

  • Filter: (predicted_defect_num > '0'::numeric)
  • Rows Removed by Filter: 61
48. 0.040 0.182 ↑ 1.0 1 1

Aggregate (cost=6.27..6.28 rows=1 width=64) (actual time=0.181..0.182 rows=1 loops=1)

49. 0.006 0.142 ↑ 1.0 10 1

Subquery Scan on x_1 (cost=6.12..6.24 rows=10 width=668) (actual time=0.137..0.142 rows=10 loops=1)

50. 0.001 0.136 ↑ 1.0 10 1

Limit (cost=6.12..6.14 rows=10 width=645) (actual time=0.133..0.136 rows=10 loops=1)

51. 0.077 0.135 ↑ 6.7 10 1

Sort (cost=6.12..6.28 rows=67 width=645) (actual time=0.132..0.135 rows=10 loops=1)

  • Sort Key: groupped_by_code_category_with_multual_4.found_defect_num DESC, groupped_by_code_category_with_multual_4.defect_code, groupped_by_code_category_with_multual_4.defect_category_code
  • Sort Method: top-N heapsort Memory: 28kB
52. 0.058 0.058 ↓ 2.8 189 1

CTE Scan on groupped_by_code_category_with_multual groupped_by_code_category_with_multual_4 (cost=0.00..4.67 rows=67 width=645) (actual time=0.003..0.058 rows=189 loops=1)

  • Filter: (found_defect_num > '0'::numeric)
  • Rows Removed by Filter: 16
53. 0.038 0.107 ↑ 1.0 1 1

Aggregate (cost=6.27..6.28 rows=1 width=64) (actual time=0.107..0.107 rows=1 loops=1)

54. 0.008 0.069 ↑ 1.0 10 1

Subquery Scan on x_2 (cost=6.12..6.24 rows=10 width=644) (actual time=0.065..0.069 rows=10 loops=1)

55. 0.001 0.061 ↑ 1.0 10 1

Limit (cost=6.12..6.14 rows=10 width=621) (actual time=0.060..0.061 rows=10 loops=1)

56. 0.024 0.060 ↑ 6.7 10 1

Sort (cost=6.12..6.28 rows=67 width=621) (actual time=0.060..0.060 rows=10 loops=1)

  • Sort Key: groupped_by_code_category_with_multual_5.matched_defect_num DESC, groupped_by_code_category_with_multual_5.defect_code, groupped_by_code_category_with_multual_5.defect_category_code
  • Sort Method: top-N heapsort Memory: 27kB
57. 0.036 0.036 ↓ 1.1 75 1

CTE Scan on groupped_by_code_category_with_multual groupped_by_code_category_with_multual_5 (cost=0.00..4.67 rows=67 width=621) (actual time=0.007..0.036 rows=75 loops=1)

  • Filter: (matched_defect_num > 0)
  • Rows Removed by Filter: 130
58. 15,900.255 15,900.255 ↑ 1.0 3 1

CTE Scan on unique_defects (cost=0.00..0.06 rows=3 width=104) (actual time=15,899.970..15,900.255 rows=3 loops=1)

59. 0.007 0.601 ↑ 1.0 3 1

Hash (cost=0.06..0.06 rows=3 width=64) (actual time=0.601..0.601 rows=3 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 18kB
60. 0.594 0.594 ↑ 1.0 3 1

CTE Scan on defect_details (cost=0.00..0.06 rows=3 width=64) (actual time=0.238..0.594 rows=3 loops=1)

Planning time : 1.449 ms
Execution time : 16,052.073 ms