explain.depesz.com

PostgreSQL's explain analyze made readable

Result: SCUj

Settings

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 0.020 10,318.638 ↑ 1.0 3 1

Hash Join (cost=64,233.11..64,233.21 rows=3 width=136) (actual time=10,318.411..10,318.638 rows=3 loops=1)

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

CTE inspections

3. 9.413 379.318 ↓ 67,963.0 67,963 1

Result (cost=0.00..62,494.74 rows=1 width=8) (actual time=0.129..379.318 rows=67,963 loops=1)

  • One-Time Filter: (('{252437,252437}'::text[])[2] = '252437'::text)
4. 369.905 369.905 ↓ 67,963.0 67,963 1

Seq Scan on fr_inspections_full i (cost=0.00..62,494.74 rows=1 width=8) (actual time=0.124..369.905 rows=67,963 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,1581638400000}'::bigint[])[1] / 1000))::double precision)) AND (inspection_date <= to_timestamp(((('{0,1581638400000}'::bigint[])[2] / 1000))::double precision)) AND ((('{252437,252437}'::text[])[2])::integer = ANY (involved_org_ids)))
  • Rows Removed by Filter: 106449
5.          

CTE founds

6. 88.358 892.066 ↓ 15,873.3 301,592 1

Nested Loop (cost=0.42..90.82 rows=19 width=66) (actual time=0.157..892.066 rows=301,592 loops=1)

7. 395.930 395.930 ↓ 67,963.0 67,963 1

CTE Scan on inspections (cost=0.00..0.02 rows=1 width=8) (actual time=0.131..395.930 rows=67,963 loops=1)

8. 407.778 407.778 ↑ 4.8 4 67,963

Index Scan using idx_fr_inspection_defects_booking_id on fr_inspection_defects d (cost=0.42..90.61 rows=19 width=34) (actual time=0.004..0.006 rows=4 loops=67,963)

  • 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. 47.599 550.415 ↓ 584.8 111,120 1

Nested Loop (cost=0.29..294.62 rows=190 width=59) (actual time=240.285..550.415 rows=111,120 loops=1)

11. 201.224 201.224 ↓ 15,873.3 301,592 1

CTE Scan on founds (cost=0.00..0.38 rows=19 width=8) (actual time=0.158..201.224 rows=301,592 loops=1)

12. 301.592 301.592 ↓ 0.0 0 301,592

Index Scan using idx_fr_predicted_defects_booking_id on fr_predicted_defects d_1 (cost=0.29..15.39 rows=10 width=27) (actual time=0.001..0.001 rows=0 loops=301,592)

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

CTE all_defects

14. 99.486 1,854.128 ↓ 5,941.8 1,241,840 1

Append (cost=0.62..9.31 rows=209 width=568) (actual time=1,150.745..1,854.128 rows=1,241,840 loops=1)

15. 241.222 1,736.947 ↓ 59,511.6 1,130,720 1

Hash Join (cost=0.62..5.32 rows=19 width=568) (actual time=1,150.745..1,736.947 rows=1,130,720 loops=1)

  • Hash Cond: (predicteds_1.booking_id = founds_1.booking_id)
16. 585.302 585.302 ↓ 584.8 111,120 1

CTE Scan on predicteds predicteds_1 (cost=0.00..3.80 rows=190 width=8) (actual time=240.287..585.302 rows=111,120 loops=1)

17. 66.462 910.423 ↓ 15,873.3 301,592 1

Hash (cost=0.38..0.38 rows=19 width=568) (actual time=910.423..910.423 rows=301,592 loops=1)

  • Buckets: 262144 (originally 1024) Batches: 2 (originally 1) Memory Usage: 14337kB
18. 843.961 843.961 ↓ 15,873.3 301,592 1

CTE Scan on founds founds_1 (cost=0.00..0.38 rows=19 width=568) (actual time=0.001..843.961 rows=301,592 loops=1)

19. 17.695 17.695 ↓ 584.8 111,120 1

CTE Scan on predicteds (cost=0.00..3.80 rows=190 width=568) (actual time=0.003..17.695 rows=111,120 loops=1)

20.          

CTE groupped_by_code_category

21. 1,059.320 10,272.715 ↓ 89.6 18,730 1

GroupAggregate (cost=1,269.86..1,286.58 rows=209 width=654) (actual time=7,134.823..10,272.715 rows=18,730 loops=1)

  • Group Key: (COALESCE(drdco.code, d_2.defect_name)), (COALESCE(drdca.custom_id, '-'::character varying)), d_2.booking_id
22. 4,653.791 9,213.395 ↓ 5,941.8 1,241,840 1

Sort (cost=1,269.86..1,270.39 rows=209 width=1,163) (actual time=7,134.720..9,213.395 rows=1,241,840 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: 227736kB
23. 342.658 4,559.604 ↓ 5,941.8 1,241,840 1

Hash Left Join (cost=44.16..1,261.81 rows=209 width=1,163) (actual time=1,151.461..4,559.604 rows=1,241,840 loops=1)

  • Hash Cond: (d_2.defect_category_id = drdca.id)
24. 660.599 4,216.268 ↓ 5,941.8 1,241,840 1

Nested Loop Left Join (cost=0.29..1,215.06 rows=209 width=627) (actual time=1,150.765..4,216.268 rows=1,241,840 loops=1)

25. 2,313.829 2,313.829 ↓ 5,941.8 1,241,840 1

CTE Scan on all_defects d_2 (cost=0.00..4.18 rows=209 width=568) (actual time=1,150.747..2,313.829 rows=1,241,840 loops=1)

26. 1,241.840 1,241.840 ↑ 1.0 1 1,241,840

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,241,840)

  • Index Cond: (d_2.defect_code_id = id)
  • Filter: (status = 1)
27. 0.165 0.678 ↑ 1.0 1,055 1

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

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

  • Filter: (status = 1)
29.          

CTE groupped_by_code_category_with_multual

30. 10.542 10,317.568 ↑ 1.1 179 1

GroupAggregate (cost=12.23..22.98 rows=200 width=756) (actual time=10,304.858..10,317.568 rows=179 loops=1)

  • Group Key: groupped_by_code_category.defect_code, groupped_by_code_category.defect_category_code
31. 11.176 10,307.026 ↓ 89.6 18,730 1

Sort (cost=12.23..12.76 rows=209 width=654) (actual time=10,304.692..10,307.026 rows=18,730 loops=1)

  • Sort Key: groupped_by_code_category.defect_code, groupped_by_code_category.defect_category_code
  • Sort Method: quicksort Memory: 7937kB
32. 10,295.850 10,295.850 ↓ 89.6 18,730 1

CTE Scan on groupped_by_code_category (cost=0.00..4.18 rows=209 width=654) (actual time=7,134.827..10,295.850 rows=18,730 loops=1)

33.          

CTE unique_defects

34. 0.003 10,318.114 ↑ 1.0 3 1

Append (cost=5.01..15.08 rows=3 width=104) (actual time=10,317.892..10,318.114 rows=3 loops=1)

35. 0.105 10,317.890 ↑ 1.0 1 1

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

36. 10,317.785 10,317.785 ↓ 1.9 126 1

CTE Scan on groupped_by_code_category_with_multual (cost=0.00..4.50 rows=67 width=580) (actual time=10,304.867..10,317.785 rows=126 loops=1)

  • Filter: (predicted_defect_num > '0'::numeric)
  • Rows Removed by Filter: 53
37. 0.111 0.157 ↑ 1.0 1 1

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

38. 0.046 0.046 ↓ 2.5 165 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.003..0.046 rows=165 loops=1)

  • Filter: (found_defect_num > '0'::numeric)
  • Rows Removed by Filter: 14
39. 0.036 0.064 ↑ 1.0 1 1

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

40. 0.028 0.028 ↑ 1.1 63 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.003..0.028 rows=63 loops=1)

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

CTE defect_details

42. 0.003 0.474 ↑ 1.0 3 1

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

43. 0.049 0.198 ↑ 1.0 1 1

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

44. 0.014 0.149 ↑ 1.0 10 1

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

45. 0.006 0.135 ↑ 1.0 10 1

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

46. 0.072 0.129 ↑ 6.7 10 1

Sort (cost=6.12..6.28 rows=67 width=645) (actual time=0.116..0.129 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: 27kB
47. 0.057 0.057 ↓ 1.9 126 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.009..0.057 rows=126 loops=1)

  • Filter: (predicted_defect_num > '0'::numeric)
  • Rows Removed by Filter: 53
48. 0.042 0.174 ↑ 1.0 1 1

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

49. 0.020 0.132 ↑ 1.0 10 1

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

50. 0.000 0.112 ↑ 1.0 10 1

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

51. 0.058 0.112 ↑ 6.7 10 1

Sort (cost=6.12..6.28 rows=67 width=645) (actual time=0.111..0.112 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.054 0.054 ↓ 2.5 165 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.054 rows=165 loops=1)

  • Filter: (found_defect_num > '0'::numeric)
  • Rows Removed by Filter: 14
53. 0.036 0.099 ↑ 1.0 1 1

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

54. 0.007 0.063 ↑ 1.0 10 1

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

55. 0.001 0.056 ↑ 1.0 10 1

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

56. 0.026 0.055 ↑ 6.7 10 1

Sort (cost=6.12..6.28 rows=67 width=621) (actual time=0.055..0.055 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.029 0.029 ↑ 1.1 63 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.005..0.029 rows=63 loops=1)

  • Filter: (matched_defect_num > 0)
  • Rows Removed by Filter: 116
58. 10,318.120 10,318.120 ↑ 1.0 3 1

CTE Scan on unique_defects (cost=0.00..0.06 rows=3 width=104) (actual time=10,317.895..10,318.120 rows=3 loops=1)

59. 0.007 0.498 ↑ 1.0 3 1

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

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

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

Planning time : 1.321 ms
Execution time : 10,394.805 ms