explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 7fr1 : Optimization for: plan #SCUj

Settings

Optimization path:

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 0.018 10,827.757 ↑ 1.0 3 1

Hash Join (cost=64,125.52..64,125.62 rows=3 width=136) (actual time=10,827.528..10,827.757 rows=3 loops=1)

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

CTE inspections

3. 9.296 376.233 ↓ 67,963.0 67,963 1

Result (cost=0.00..62,391.42 rows=1 width=8) (actual time=0.131..376.233 rows=67,963 loops=1)

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

Seq Scan on fr_inspections_full i (cost=0.00..62,391.42 rows=1 width=8) (actual time=0.125..366.937 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: 106460
5.          

CTE founds

6. 74.449 943.650 ↓ 15,873.3 301,592 1

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

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

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

8. 475.741 475.741 ↑ 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.005..0.007 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. 55.718 573.774 ↓ 584.8 111,120 1

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

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

CTE Scan on founds (cost=0.00..0.38 rows=19 width=8) (actual time=0.156..216.464 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. 395.139 4,181.426 ↓ 5,941.8 1,241,840 1

Hash Left Join (cost=42.09..1,263.73 rows=209 width=1,180) (actual time=1,209.966..4,181.426 rows=1,241,840 loops=1)

  • Hash Cond: (founds_1.defect_category_id = drdca.id)
15. 600.739 3,785.790 ↓ 5,941.8 1,241,840 1

Nested Loop Left Join (cost=0.91..1,219.67 rows=209 width=631) (actual time=1,209.439..3,785.790 rows=1,241,840 loops=1)

16. 114.981 1,943.211 ↓ 5,941.8 1,241,840 1

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

17. 244.799 1,813.398 ↓ 59,511.6 1,130,720 1

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

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

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

19. 68.495 955.075 ↓ 15,873.3 301,592 1

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

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

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

21. 14.832 14.832 ↓ 584.8 111,120 1

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

22. 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: (founds_1.defect_code_id = id)
23. 0.142 0.497 ↑ 1.0 1,055 1

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

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

25.          

CTE groupped_by_code_category

26. 988.506 10,788.183 ↓ 93.7 18,730 1

GroupAggregate (cost=12.23..28.73 rows=200 width=654) (actual time=7,716.504..10,788.183 rows=18,730 loops=1)

  • Group Key: d_2.defect_code, d_2.defect_category_code, d_2.booking_id
27. 4,646.624 9,799.677 ↓ 5,941.8 1,241,840 1

Sort (cost=12.23..12.76 rows=209 width=1,172) (actual time=7,716.429..9,799.677 rows=1,241,840 loops=1)

  • Sort Key: d_2.defect_code, d_2.defect_category_code, d_2.booking_id
  • Sort Method: external merge Disk: 227744kB
28. 5,153.053 5,153.053 ↓ 5,941.8 1,241,840 1

CTE Scan on all_defects d_2 (cost=0.00..4.18 rows=209 width=1,172) (actual time=1,209.968..5,153.053 rows=1,241,840 loops=1)

29.          

CTE groupped_by_code_category_with_multual

30. 11.109 10,826.682 ↑ 1.1 179 1

GroupAggregate (cost=11.64..22.14 rows=200 width=756) (actual time=10,813.518..10,826.682 rows=179 loops=1)

  • Group Key: groupped_by_code_category.defect_code, groupped_by_code_category.defect_category_code
31. 12.137 10,815.573 ↓ 93.7 18,730 1

Sort (cost=11.64..12.14 rows=200 width=654) (actual time=10,813.379..10,815.573 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,803.436 10,803.436 ↓ 93.7 18,730 1

CTE Scan on groupped_by_code_category (cost=0.00..4.00 rows=200 width=654) (actual time=7,716.508..10,803.436 rows=18,730 loops=1)

33.          

CTE unique_defects

34. 0.002 10,827.191 ↑ 1.0 3 1

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

35. 0.123 10,826.966 ↑ 1.0 1 1

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

36. 10,826.843 10,826.843 ↓ 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,813.528..10,826.843 rows=126 loops=1)

  • Filter: (predicted_defect_num > '0'::numeric)
  • Rows Removed by Filter: 53
37. 0.138 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.019 0.019 ↓ 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.005..0.019 rows=165 loops=1)

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

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

40. 0.032 0.032 ↑ 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.032 rows=63 loops=1)

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

CTE defect_details

42. 0.002 0.499 ↑ 1.0 3 1

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

43. 0.068 0.216 ↑ 1.0 1 1

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

44. 0.016 0.148 ↑ 1.0 10 1

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

45. 0.003 0.132 ↑ 1.0 10 1

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

46. 0.060 0.129 ↑ 6.7 10 1

Sort (cost=6.12..6.28 rows=67 width=645) (actual time=0.128..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.069 0.069 ↓ 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.069 rows=126 loops=1)

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

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

49. 0.007 0.130 ↑ 1.0 10 1

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

50. 0.001 0.123 ↑ 1.0 10 1

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

51. 0.064 0.122 ↑ 6.7 10 1

Sort (cost=6.12..6.28 rows=67 width=645) (actual time=0.122..0.122 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.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.005..0.058 rows=165 loops=1)

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

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

54. 0.008 0.064 ↑ 1.0 10 1

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

55. 0.000 0.056 ↑ 1.0 10 1

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

56. 0.020 0.056 ↑ 6.7 10 1

Sort (cost=6.12..6.28 rows=67 width=621) (actual time=0.054..0.056 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 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.004..0.036 rows=63 loops=1)

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

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

59. 0.008 0.540 ↑ 1.0 3 1

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

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

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

Planning time : 2.000 ms
Execution time : 10,954.951 ms