explain.depesz.com

PostgreSQL's explain analyze made readable

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

Settings

Optimization path:

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 0.016 15,163.705 ↑ 1.0 3 1

Hash Join (cost=62,398.52..62,398.62 rows=3 width=136) (actual time=15,163.458..15,163.705 rows=3 loops=1)

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

CTE inspections

3. 11.508 387.364 ↓ 79,800.0 79,800 1

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

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

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

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

CTE Scan on inspections (cost=0.00..0.02 rows=1 width=8) (actual time=0.084..406.566 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.728 464.449 ↓ 888.5 168,810 1

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

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

CTE Scan on founds (cost=0.00..0.38 rows=19 width=8) (actual time=0.299..43.697 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. 154.547 2,279.652 ↓ 8,443.4 1,764,680 1

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

15. 345.574 2,102.137 ↓ 83,993.2 1,595,870 1

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

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

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

17. 70.867 1,238.556 ↓ 17,685.5 336,024 1

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

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

19. 22.968 22.968 ↓ 888.5 168,810 1

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

20.          

CTE groupped_by_code_category

21. 1,487.975 15,089.073 ↓ 149.1 31,168 1

GroupAggregate (cost=1,269.86..1,286.58 rows=209 width=654) (actual time=9,803.523..15,089.073 rows=31,168 loops=1)

  • Group Key: (COALESCE(drdco.code, d_2.defect_name)), (COALESCE(drdca.custom_id, '-'::character varying)), d_2.booking_id
22. 7,469.708 13,601.098 ↓ 8,443.4 1,764,680 1

Sort (cost=1,269.86..1,270.39 rows=209 width=1,163) (actual time=9,803.259..13,601.098 rows=1,764,680 loops=1)

  • Sort Key: (COALESCE(drdco.code, d_2.defect_name)), (COALESCE(drdca.custom_id, '-'::character varying)), d_2.booking_id
  • Sort Method: external merge Disk: 339480kB
23. 509.535 6,131.390 ↓ 8,443.4 1,764,680 1

Hash Left Join (cost=44.16..1,261.81 rows=209 width=1,163) (actual time=1,291.703..6,131.390 rows=1,764,680 loops=1)

  • Hash Cond: (d_2.defect_category_id = drdca.id)
24. 935.192 5,621.438 ↓ 8,443.4 1,764,680 1

Nested Loop Left Join (cost=0.29..1,215.06 rows=209 width=627) (actual time=1,291.267..5,621.438 rows=1,764,680 loops=1)

25. 2,921.566 2,921.566 ↓ 8,443.4 1,764,680 1

CTE Scan on all_defects d_2 (cost=0.00..4.18 rows=209 width=568) (actual time=1,291.250..2,921.566 rows=1,764,680 loops=1)

26. 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: (d_2.defect_code_id = id)
  • Filter: (status = 1)
27. 0.186 0.417 ↑ 1.0 1,055 1

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

  • Buckets: 2048 Batches: 1 Memory Usage: 80kB
28. 0.231 0.231 ↑ 1.0 1,055 1

Seq Scan on dr_defect_category drdca (cost=0.00..30.44 rows=1,075 width=27) (actual time=0.013..0.231 rows=1,055 loops=1)

  • Filter: (status = 1)
29.          

CTE groupped_by_code_category_with_multual

30. 17.388 15,162.550 ↓ 1.0 205 1

GroupAggregate (cost=12.23..22.98 rows=200 width=756) (actual time=15,141.820..15,162.550 rows=205 loops=1)

  • Group Key: groupped_by_code_category.defect_code, groupped_by_code_category.defect_category_code
31. 21.297 15,145.162 ↓ 149.1 31,168 1

Sort (cost=12.23..12.76 rows=209 width=654) (actual time=15,141.793..15,145.162 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,123.865 15,123.865 ↓ 149.1 31,168 1

CTE Scan on groupped_by_code_category (cost=0.00..4.18 rows=209 width=654) (actual time=9,803.527..15,123.865 rows=31,168 loops=1)

33.          

CTE unique_defects

34. 0.004 15,163.141 ↑ 1.0 3 1

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

35. 0.135 15,162.895 ↑ 1.0 1 1

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

36. 15,162.760 15,162.760 ↓ 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,142.078..15,162.760 rows=144 loops=1)

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

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

38. 0.041 0.041 ↓ 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.001..0.041 rows=189 loops=1)

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

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

40. 0.030 0.030 ↓ 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.030 rows=75 loops=1)

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

CTE defect_details

42. 0.001 0.507 ↑ 1.0 3 1

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

43. 0.051 0.219 ↑ 1.0 1 1

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

44. 0.030 0.168 ↑ 1.0 10 1

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

45. 0.003 0.138 ↑ 1.0 10 1

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

46. 0.061 0.135 ↑ 6.7 10 1

Sort (cost=6.12..6.28 rows=67 width=645) (actual time=0.133..0.135 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.074 0.074 ↓ 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.013..0.074 rows=144 loops=1)

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

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

49. 0.008 0.146 ↑ 1.0 10 1

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

50. 0.001 0.138 ↑ 1.0 10 1

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

51. 0.087 0.137 ↑ 6.7 10 1

Sort (cost=6.12..6.28 rows=67 width=645) (actual time=0.136..0.137 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.050 0.050 ↓ 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.050 rows=189 loops=1)

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

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

54. 0.005 0.070 ↑ 1.0 10 1

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

55. 0.003 0.065 ↑ 1.0 10 1

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

56. 0.022 0.062 ↑ 6.7 10 1

Sort (cost=6.12..6.28 rows=67 width=621) (actual time=0.061..0.062 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.040 0.040 ↓ 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.008..0.040 rows=75 loops=1)

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

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

59. 0.006 0.544 ↑ 1.0 3 1

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

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

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

Planning time : 1.608 ms
Execution time : 15,269.047 ms