explain.depesz.com

PostgreSQL's explain analyze made readable

Result: gxZM

Settings

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 0.019 39,761.868 ↑ 1.0 40 1

Limit (cost=79,305.01..79,315.91 rows=40 width=4,215) (actual time=39,761.740..39,761.868 rows=40 loops=1)

2. 0.097 39,761.849 ↑ 103.8 40 1

Unique (cost=79,305.01..80,436.15 rows=4,151 width=4,215) (actual time=39,761.739..39,761.849 rows=40 loops=1)

3. 5.937 39,761.752 ↑ 103.8 40 1

Sort (cost=79,305.01..79,315.38 rows=4,151 width=4,215) (actual time=39,761.738..39,761.752 rows=40 loops=1)

  • Sort Key: (CASE WHEN (i.default_image IS NULL) THEN 0 ELSE 1 END) DESC, (CASE WHEN (it.stock > 0) THEN 1 ELSE 0 END) DESC, (CASE WHEN (it.bti IS TRUE) THEN it.current_price ELSE '999999999999'::double precision END), i.complete, d.facts, i.id, i.almcod, i.type, i.name, i.productarea, i.requires_assembly, i.default_image, i.designers, i.stylegroup, i.price_range, i.efficiency, d.product_area_name, d.custom_benefit, d.good_to_know, d.customer_material, d.environment, d.care_instructions, d.package_number, d.length_metric, d.length_imperial, d.width_metric, d.width_imperial, d.height_metric, d.height_imperial, d.diameter_metric, d.diameter_imperial, d.depth_metric, d.depth_imperial, d.energy_tag, d.color, d.size, d.resume_measure_metric, d.resume_measure_imperial, d.custom_benefit_resume, d.long_benefit, d.search_value, d.search_value_unaccent, d.extra_facts, d.valid_design, d.pia_item_description, dx.facts, dx.product_area_name, dx.custom_benefit, dx.good_to_know, dx.customer_material, dx.environment, dx.care_instructions, dx.package_number, dx.length_metric, dx.length_imperial, dx.width_metric, dx.width_imperial, dx.height_metric, dx.height_imperial, dx.diameter_metric, dx.diameter_imperial, dx.depth_metric, dx.depth_imperial, dx.energy_tag, dx.color, dx.size, dx.resume_measure_metric, dx.resume_measure_imperial, dx.custom_benefit_resume, dx.long_benefit, dx.search_value, dx.search_value_unaccent, dx.extra_facts, dx.valid_design, dx.pia_item_description, it.current_price, it.hbp_price, it.original_price, it.offer_price, it.family_price, it.bti, it.is_new, it.top_seller, it.sold, it.show_in_web, it.buy_in_web, it.start_sale_date, it.end_sale_date, it.start_sale_date_showable, it.end_sale_date_showable, it.location, it.salesmethod, it.fixed_assembly_cost, it.buy_in_nav, it.show_in_list, it.activitystartdate, it.activityenddate, it.business_price, itu.product_url, (CASE WHEN (ip.priority IS NULL) THEN 0 ELSE ip.priority END), p.value, p.imperial, p.metric, p.unit, (sum(CASE WHEN ((oi.price * (oi.quantity)::double precision) > '0'::double precision) THEN (oi.price * (oi.quantity)::double precision) ELSE '0'::double precision END)), (sum(CASE WHEN ((o.created_at >= (now() - '14 days'::interval))AND (o.created_at <= now())) THEN (oi.price * (oi.quantity)::double precision) ELSE '0'::double precision END)), (sum(CASE WHEN (oi.quantity > 0) THEN oi.quantity ELSE 0 END)), (count('z') OVER (?))
  • Sort Method: quicksort Memory: 886kB
4. 2.411 39,755.815 ↑ 21.6 192 1

WindowAgg (cost=70,278.37..71,534.05 rows=4,151 width=4,215) (actual time=39,755.459..39,755.815 rows=192 loops=1)

5. 10,520.306 39,753.404 ↑ 21.6 192 1

GroupAggregate (cost=70,278.37..71,471.78 rows=4,151 width=4,205) (actual time=22,550.915..39,753.404 rows=192 loops=1)

  • Group Key: i.id, (CASE WHEN (i.default_image IS NULL) THEN 0 ELSE 1 END), d.facts, d.product_area_name, d.custom_benefit, d.good_to_know, d.customer_material, d.environment, d.care_instructions, d.package_number, d.length_metric, d.length_imperial, d.width_metric, d.width_imperial, d.height_metric, d.height_imperial, d.diameter_metric, d.diameter_imperial, d.depth_metric, d.depth_imperial, d.energy_tag, d.color, d.size, d.resume_measure_metric, d.resume_measure_imperial, d.custom_benefit_resume, d.long_benefit, d.search_value, d.search_value_unaccent, d.extra_facts, d.valid_design, d.pia_item_description, dx.facts, dx.product_area_name, dx.custom_benefit, dx.good_to_know, dx.customer_material, dx.environment, dx.care_instructions, dx.package_number, dx.length_metric, dx.length_imperial, dx.width_metric, dx.width_imperial, dx.height_metric, dx.height_imperial, dx.diameter_metric, dx.diameter_imperial, dx.depth_metric, dx.depth_imperial, dx.energy_tag, dx.color, dx.size, dx.resume_measure_metric, dx.resume_measure_imperial, dx.custom_benefit_resume, dx.long_benefit, dx.search_value, dx.search_value_unaccent, dx.extra_facts, dx.valid_design, dx.pia_item_description, it.current_price, it.hbp_price, it.original_price, it.offer_price, it.family_price, (CASE WHEN (it.stock > 0) THEN 1 ELSE 0 END), it.bti, it.is_new, it.top_seller, it.sold, it.show_in_web, it.buy_in_web, it.start_sale_date, it.end_sale_date, it.start_sale_date_showable, it.end_sale_date_showable, it.location, it.salesmethod, it.fixed_assembly_cost, it.buy_in_nav, it.show_in_list, it.activitystartdate, it.activityenddate, it.business_price, itu.product_url, (CASE WHEN (ip.priority IS NULL) THEN 0 ELSE ip.priority END), p.value, p.imperial, p.metric, p.unit, (CASE WHEN (it.bti IS TRUE) THEN it.current_price ELSE '999999999999'::double precision END)
6. 27,533.621 29,233.098 ↓ 4.1 16,848 1

Sort (cost=70,278.37..70,288.75 rows=4,151 width=4,201) (actual time=22,549.528..29,233.098 rows=16,848 loops=1)

  • Sort Key: i.id, (CASE WHEN (i.default_image IS NULL) THEN 0 ELSE 1 END) DESC, d.facts, d.product_area_name, d.custom_benefit, d.good_to_know, d.customer_material, d.environment, d.care_instructions, d.package_number, d.length_metric, d.length_imperial, d.width_metric, d.width_imperial, d.height_metric, d.height_imperial, d.diameter_metric, d.diameter_imperial, d.depth_metric, d.depth_imperial, d.energy_tag, d.color, d.size, d.resume_measure_metric, d.resume_measure_imperial, d.custom_benefit_resume, d.long_benefit, d.search_value, d.search_value_unaccent, d.extra_facts, d.valid_design, d.pia_item_description, dx.facts, dx.product_area_name, dx.custom_benefit, dx.good_to_know, dx.customer_material, dx.environment, dx.care_instructions, dx.package_number, dx.length_metric, dx.length_imperial, dx.width_metric, dx.width_imperial, dx.height_metric, dx.height_imperial, dx.diameter_metric, dx.diameter_imperial, dx.depth_metric, dx.depth_imperial, dx.energy_tag, dx.color, dx.size, dx.resume_measure_metric, dx.resume_measure_imperial, dx.custom_benefit_resume, dx.long_benefit, dx.search_value, dx.search_value_unaccent, dx.extra_facts, dx.valid_design, dx.pia_item_description, it.current_price, it.hbp_price, it.original_price, it.offer_price, it.family_price, (CASE WHEN (it.stock > 0) THEN 1 ELSE 0 END) DESC, it.bti, it.is_new, it.top_seller, it.sold, it.show_in_web, it.buy_in_web,it.start_sale_date, it.end_sale_date, it.start_sale_date_showable, it.end_sale_date_showable, it.location, it.salesmethod, it.fixed_assembly_cost, it.buy_in_nav, it.show_in_list, it.activitystartdate, it.activityenddate, it.business_price, itu.product_url, (CASE WHEN (ip.priority IS NULL) THEN 0 ELSE ip.priority END), p.value, p.imperial, p.metric, p.unit, (CASE WHEN (it.bti IS TRUE) THEN it.current_price ELSE '999999999999'::double precision END)
  • Sort Method: external merge Disk: 57592kB
7. 17.611 1,699.477 ↓ 4.1 16,848 1

Hash Left Join (cost=34,200.62..62,521.41 rows=4,151 width=4,201) (actual time=327.961..1,699.477 rows=16,848 loops=1)

  • Hash Cond: (i.id = ip.id_item)
8. 13.487 1,681.864 ↓ 4.1 16,848 1

Nested Loop (cost=34,139.77..62,432.00 rows=4,151 width=4,185) (actual time=327.921..1,681.864 rows=16,848 loops=1)

9. 182.272 1,617.833 ↓ 3.7 16,848 1

Hash Left Join (cost=34,139.48..60,639.59 rows=4,604 width=4,129) (actual time=327.908..1,617.833 rows=16,848 loops=1)

  • Hash Cond: (i.id = apa.id_item)
  • Filter: ((i.productarea = ANY ('{911,912,914,915,917,919,1071,50489,50490}'::integer[])) OR (apa.code = ANY ('{911,912,914,915,917,919,1071,50489,50490}'::integer[])))
  • Rows Removed by Filter: 411139
10. 219.639 1,433.048 ↓ 3.3 369,504 1

Hash Left Join (cost=33,835.27..58,391.04 rows=111,464 width=4,129) (actual time=320.605..1,433.048 rows=369,504 loops=1)

  • Hash Cond: (oi.order_id = o.id)
11. 794.209 1,193.702 ↓ 3.3 369,504 1

Hash Right Join (cost=31,108.85..54,132.00 rows=111,464 width=4,129) (actual time=300.537..1,193.702 rows=369,504 loops=1)

  • Hash Cond: (oi.item_id = i.id)
12. 99.085 99.085 ↓ 1.0 376,052 1

Seq Scan on orderitem oi (cost=0.00..10,003.37 rows=376,037 width=28) (actual time=0.006..99.085 rows=376,052 loops=1)

13. 104.560 300.408 ↓ 1.6 16,499 1

Hash (cost=25,623.33..25,623.33 rows=10,602 width=4,109) (actual time=300.408..300.408 rows=16,499 loops=1)

  • Buckets: 1024 Batches: 16 Memory Usage: 3540kB
14. 23.671 195.848 ↓ 1.6 16,499 1

Nested Loop (cost=4,104.31..25,623.33 rows=10,602 width=4,109) (actual time=24.574..195.848 rows=16,499 loops=1)

15. 78.809 139.179 ↓ 2.5 16,499 1

Nested Loop (cost=4,103.89..15,154.70 rows=6,628 width=2,473) (actual time=24.567..139.179 rows=16,499 loops=1)

  • -> Index Scan using itemdetail_pkey on itemdetail d (cost=0.41..2.25 rows=1 width=1636) (actual time=0.003..0.003 rows=1loops=16499)
16. 7.154 60.370 ↓ 4.0 16,499 1

Hash Left Join (cost=4,103.48..5,786.25 rows=4,143 width=837) (actual time=24.554..60.370 rows=16,499 loops=1)

  • Hash Cond: (i.id = p.id_item)
  • Index Cond: (id = i.id)
17. 22.201 50.261 ↓ 4.0 16,499 1

Hash Join (cost=3,863.04..5,530.26 rows=4,143 width=814) (actual time=21.591..50.261 rows=16,499 loops=1)

  • Hash Cond: (i.id = it.id)
18. 6.539 6.539 ↑ 1.0 35,712 1

Seq Scan on item i (cost=0.00..1,491.67 rows=35,767 width=123) (actual time=0.005..6.539 rows=35,712 loops=1)

19. 7.238 21.521 ↓ 4.0 16,499 1

Hash (cost=3,811.25..3,811.25 rows=4,143 width=691) (actual time=21.521..21.521 rows=16,499 loops=1)

  • Buckets: 32768 (originally 8192) Batches: 1 (originally 1) Memory Usage: 2168kB
20. 14.283 14.283 ↓ 4.0 16,499 1

Seq Scan on itemstore it (cost=0.00..3,811.25 rows=4,143 width=691) (actual time=2.119..14.283 rows=16,499 loops=1)

  • Filter: (((show_in_web IS TRUE) OR (show_in_web IS NULL)) AND (show_in_list IS TRUE) AND CASE WHEN(start_sale_date_showable IS NOT NULL) THEN (start_sale_date_showable <= now()) WHEN (start_sale_date IS NOT NULL) THEN (start_sale_date <= now()) ELSE (start_sale_date_showable IS NULL) END AND CASE WHEN (end_sale_date_showable IS NOT NULL) THEN (end_sale_date_showable >= now()) WHEN (end_sale_date IS NOT NULL) THEN (end_sale_date >= now()) ELSE (end_sale_date_showable IS NULL) END)
  • Rows Removed by Filter: 10876
21. 0.307 2.955 ↓ 181.6 1,271 1

Hash (cost=240.36..240.36 rows=7 width=27) (actual time=2.955..2.955 rows=1,271 loops=1)

  • Buckets: 2048 (originally 1024) Batches: 1 (originally 1) Memory Usage: 92kB
22. 0.664 2.648 ↓ 181.6 1,271 1

Hash Join (cost=198.78..240.36 rows=7 width=27) (actual time=1.740..2.648 rows=1,271 loops=1)

  • Hash Cond: ((CASE WHEN ((min((CASE WHEN ((priceunit.type_number)::text = '00061'::text) THEN '00'::character varying ELSE priceunit.type_number END)::text)) = '00'::text) THEN '00061'::text ELSE (min((CASE WHEN ((priceunit.type_number)::text = '00061'::text) THEN '00'::character varying ELSE priceunit.type_number END)::text)) END = (p.type_number)::text) AND (priceunit.id_item = p.id_item))
23. 0.737 1.289 ↑ 1.0 1,271 1

HashAggregate (cost=100.31..113.08 rows=1,277 width=36) (actual time=1.029..1.289 rows=1,271 loops=1)

  • Group Key: priceunit.id_item
24. 0.552 0.552 ↓ 1.0 1,311 1

Seq Scan on priceunit (cost=0.00..90.58 rows=1,297 width=10) (actual time=0.006..0.552 rows=1,311 loops=1)

  • Filter: ((type_number)::text <> ALL ('{00007,00125,00381,00041,00044,00047,00008,00005}'::text[]))
  • Rows Removed by Filter: 270
25. 0.375 0.695 ↓ 1.0 1,581 1

Hash (cost=74.79..74.79 rows=1,579 width=33) (actual time=0.695..0.695 rows=1,581 loops=1)

  • Buckets: 2048 Batches: 1 Memory Usage: 120kB
26. 0.320 0.320 ↓ 1.0 1,581 1

Seq Scan on priceunit p (cost=0.00..74.79 rows=1,579 width=33) (actual time=0.005..0.320 rows=1,581 loops=1)

27. 32.998 32.998 ↑ 1.0 1 16,499

Index Scan using itemdetail_pkey on itemdetail dx (cost=0.41..1.57 rows=1 width=1,636) (actual time=0.002..0.002 rows=1 loops=16,499)

  • Index Cond: (id = d.id)
28. 7.875 19.707 ↑ 1.0 39,636 1

Hash (cost=2,221.74..2,221.74 rows=40,374 width=12) (actual time=19.707..19.707 rows=39,636 loops=1)

  • Buckets: 65536 Batches: 1 Memory Usage: 2216kB
29. 11.832 11.832 ↑ 1.0 39,636 1

Seq Scan on "order" o (cost=0.00..2,221.74 rows=40,374 width=12) (actual time=0.007..11.832 rows=39,636 loops=1)

30. 1.537 2.513 ↓ 1.0 10,949 1

Hash (cost=167.43..167.43 rows=10,943 width=8) (actual time=2.513..2.513 rows=10,949 loops=1)

  • Buckets: 16384 Batches: 1 Memory Usage: 556kB
31. 0.976 0.976 ↓ 1.0 10,949 1

Seq Scan on alternative_pa apa (cost=0.00..167.43 rows=10,943 width=8) (actual time=0.009..0.976 rows=10,949 loops=1)

32. 50.544 50.544 ↑ 1.0 1 16,848

Index Scan using item_url_pkey on item_url itu (cost=0.29..0.38 rows=1 width=72) (actual time=0.002..0.003 rows=1 loops=16,848)

  • Index Cond: (id = d.id)
33. 0.000 0.002 ↓ 0.0 0 1

Hash (cost=32.60..32.60 rows=2,260 width=8) (actual time=0.002..0.002 rows=0 loops=1)

  • Buckets: 4096 Batches: 1 Memory Usage: 32kB
34. 0.002 0.002 ↓ 0.0 0 1

Seq Scan on item_priority ip (cost=0.00..32.60 rows=2,260 width=8) (actual time=0.002..0.002 rows=0 loops=1)