explain.depesz.com

PostgreSQL's explain analyze made readable

Result: CLjN

Settings
# exclusive inclusive rows x rows loops node
1. 0.062 26.662 ↑ 1.9 34 1

Unique (cost=4,439.51..4,457.71 rows=65 width=4,267) (actual time=26.582..26.662 rows=34 loops=1)

2. 0.805 26.600 ↑ 1.9 34 1

Sort (cost=4,439.51..4,439.67 rows=65 width=4,267) (actual time=26.580..26.600 rows=34 loops=1)

  • Sort Key: (ts_rank(d_1.search_value, '''linnmon'':*'::tsquery)) DESC, (CASE WHEN (i_1.default_image IS NULL) THEN 0 ELSE 1 END) DESC, (CASE WHEN (it_1.stock > 0) THEN 1 ELSE 0 END) DESC, 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.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, d.depth_metric, d.depth_imperial, 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.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, dx.depth_metric, dx.depth_imperial, it.current_price, it.hbp_price, it.original_price, it.offer_price, it.family_price, it.stock, 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, it.product_url, itu.product_url, p.value, p.imperial, p.metric, p.unit, (CASE WHEN (it_1.bti IS TRUE) THEN it_1.current_price ELSE '999999999999'::double precision END), (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 (?)), (CASE WHEN (ip.priority IS NULL) THEN 0 ELSE ip.priority END)
  • Sort Method: quicksort Memory: 185kB
3. 4.338 25.795 ↑ 1.9 34 1

Nested Loop Left Join (cost=4,104.37..4,437.55 rows=65 width=4,267) (actual time=19.312..25.795 rows=34 loops=1)

  • Join Filter: (p.id_item = i_1.id)
  • Rows Removed by Join Filter: 43213
4. 0.045 16.765 ↑ 1.9 34 1

Nested Loop (cost=3,905.59..4,190.35 rows=65 width=4,246) (actual time=16.449..16.765 rows=34 loops=1)

  • Join Filter: (i_1.id = i.id)
5. 0.061 16.618 ↑ 1.5 34 1

Nested Loop (cost=3,905.30..4,170.78 rows=50 width=4,127) (actual time=16.435..16.618 rows=34 loops=1)

  • Join Filter: (i_1.id = dx.id)
6. 0.849 16.455 ↑ 1.5 34 1

Hash Right Join (cost=3,904.88..4,113.85 rows=50 width=2,491) (actual time=16.423..16.455 rows=34 loops=1)

  • Hash Cond: (apa.id_item = i_1.id)
7. 0.746 0.746 ↓ 1.0 10,949 1

Seq Scan on alternative_pa apa (cost=0.00..167.43 rows=10,943 width=4) (actual time=0.004..0.746 rows=10,949 loops=1)

8. 0.087 14.860 ↑ 1.2 34 1

Hash (cost=3,904.38..3,904.38 rows=40 width=2,491) (actual time=14.860..14.860 rows=34 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 65kB
9. 0.021 14.773 ↑ 1.2 34 1

Nested Loop (cost=3,518.60..3,904.38 rows=40 width=2,491) (actual time=14.425..14.773 rows=34 loops=1)

  • Join Filter: (i_1.id = itu.id)
10. 0.041 14.650 ↑ 1.2 34 1

Nested Loop (cost=3,518.31..3,889.87 rows=40 width=2,419) (actual time=14.413..14.650 rows=34 loops=1)

  • Join Filter: (i_1.id = it.id)
11. 0.034 14.541 ↑ 1.2 34 1

Nested Loop (cost=3,517.89..3,855.68 rows=40 width=1,696) (actual time=14.403..14.541 rows=34 loops=1)

12. 0.003 14.405 ↑ 1.2 34 1

Limit (cost=3,517.48..3,517.58 rows=40 width=238) (actual time=14.395..14.405 rows=34 loops=1)

13. 0.070 14.402 ↑ 22.2 34 1

Sort (cost=3,517.48..3,519.36 rows=754 width=238) (actual time=14.395..14.402 rows=34 loops=1)

  • Sort Key: (ts_rank(d_1.search_value, '''linnmon'':*'::tsquery)) DESC, (CASE WHEN (i_1.default_image IS NULL) THEN 0 ELSE 1 END) DESC, (CASE WHEN (it_1.stock > 0) THEN 1 ELSE 0 END) DESC, i_1.complete, d_1.facts
  • Sort Method: quicksort Memory: 35kB
14. 0.192 14.332 ↑ 22.2 34 1

WindowAgg (cost=3,425.78..3,493.64 rows=754 width=238) (actual time=14.178..14.332 rows=34 loops=1)

15. 4.144 14.140 ↑ 22.2 34 1

GroupAggregate (cost=3,425.78..3,480.45 rows=754 width=206) (actual time=10.040..14.140 rows=34 loops=1)

  • Group Key: i_1.id, it_1.bti, it_1.stock, it_1.current_price, it_1.buy_in_web, d_1.search_value, d_1.facts, ip.priority
16. 5.789 9.996 ↑ 1.4 557 1

Sort (cost=3,425.78..3,427.67 rows=754 width=202) (actual time=9.899..9.996 rows=557 loops=1)

  • Sort Key: i_1.id, it_1.bti, it_1.stock, it_1.current_price, it_1.buy_in_web, d_1.search_value, d_1.facts, ip.priority
  • Sort Method: quicksort Memory: 128kB
17. 0.191 4.207 ↑ 1.4 557 1

Nested Loop Left Join (cost=2,490.03..3,389.75 rows=754 width=202) (actual time=2.095..4.207 rows=557 loops=1)

18. 0.128 2.902 ↑ 1.4 557 1

Nested Loop Left Join (cost=2,489.74..3,134.64 rows=754 width=202) (actual time=2.085..2.902 rows=557 loops=1)

19. 0.002 2.366 ↑ 2.4 34 1

Nested Loop Left Join (cost=2,489.32..2,900.12 rows=81 width=182) (actual time=2.065..2.366 rows=34 loops=1)

20. 0.058 2.330 ↑ 2.4 34 1

Nested Loop (cost=2,489.16..2,885.25 rows=81 width=178) (actual time=2.060..2.330 rows=34 loops=1)

21. 0.875 2.068 ↑ 3.4 68 1

Hash Right Join (cost=2,488.74..2,697.82 rows=230 width=168) (actual time=2.041..2.068 rows=68 loops=1)

  • Hash Cond: (apa_1.id_item = i_1.id)
22. 0.758 0.758 ↓ 1.0 10,949 1

Seq Scan on alternative_pa apa_1 (cost=0.00..167.43 rows=10,943 width=4) (actual time=0.005..0.758 rows=10,949 loops=1)

23. 0.026 0.435 ↑ 3.4 68 1

Hash (cost=2,485.87..2,485.87 rows=230 width=168) (actual time=0.435..0.435 rows=68 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 20kB
24. 0.045 0.409 ↑ 3.4 68 1

Nested Loop (cost=74.13..2,485.87 rows=230 width=168) (actual time=0.106..0.409 rows=68 loops=1)

25. 0.071 0.160 ↑ 3.4 68 1

Bitmap Heap Scan on itemdetail d_1 (cost=73.84..912.84 rows=230 width=132) (actual time=0.097..0.160 rows=68 loops=1)

  • Recheck Cond: ((search_value @@ '''linnmon'':*'::tsquery) OR (search_value_unaccent @@ '''linnmon'':*'::tsquery))
  • Heap Blocks: exact=68
26. 0.000 0.089 ↓ 0.0 0 1

BitmapOr (cost=73.84..73.84 rows=231 width=0) (actual time=0.089..0.089 rows=0 loops=1)

27. 0.055 0.055 ↑ 1.7 68 1

Bitmap Index Scan on itemdetail_search_value_idx (cost=0.00..36.86 rows=115 width=0) (actual time=0.055..0.055 rows=68 loops=1)

  • Index Cond: (search_value @@ '''linnmon'':*'::tsquery)
28. 0.034 0.034 ↑ 1.7 68 1

Bitmap Index Scan on itemdetail_search_value_unaccent_idx (cost=0.00..36.86 rows=115 width=0) (actual time=0.034..0.034 rows=68 loops=1)

  • Index Cond: (search_value_unaccent @@ '''linnmon'':*'::tsquery)
29. 0.204 0.204 ↑ 1.0 1 68

Index Scan using item_pkey on item i_1 (cost=0.29..6.83 rows=1 width=36) (actual time=0.002..0.003 rows=1 loops=68)

  • Index Cond: (id = d_1.id)
30. 0.204 0.204 ↓ 0.0 0 68

Index Scan using itemstore_pkey on itemstore it_1 (cost=0.42..0.80 rows=1 width=18) (actual time=0.003..0.003 rows=0 loops=68)

  • Index Cond: (id = i_1.id)
  • 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: 0
31. 0.034 0.034 ↓ 0.0 0 34

Index Scan using item_priority_id_item_key on item_priority ip (cost=0.15..0.17 rows=1 width=8) (actual time=0.001..0.001 rows=0 loops=34)

  • Index Cond: (id_item = i_1.id)
32. 0.408 0.408 ↑ 3.9 16 34

Index Scan using orderitem_item_id_idx on orderitem oi (cost=0.42..2.28 rows=62 width=28) (actual time=0.006..0.012 rows=16 loops=34)

  • Index Cond: (i_1.id = item_id)
33. 1.114 1.114 ↑ 1.0 1 557

Index Scan using order_pkey on "order" o (cost=0.29..0.33 rows=1 width=12) (actual time=0.002..0.002 rows=1 loops=557)

  • Index Cond: (oi.order_id = id)
34. 0.102 0.102 ↑ 1.0 1 34

Index Scan using itemdetail_pkey on itemdetail d (cost=0.41..8.43 rows=1 width=1,636) (actual time=0.003..0.003 rows=1 loops=34)

  • Index Cond: (id = i_1.id)
35. 0.068 0.068 ↑ 1.0 1 34

Index Scan using itemstore_pkey on itemstore it (cost=0.42..0.84 rows=1 width=723) (actual time=0.002..0.002 rows=1 loops=34)

  • Index Cond: (id = d.id)
36. 0.102 0.102 ↑ 1.0 1 34

Index Scan using item_url_pkey on item_url itu (cost=0.29..0.35 rows=1 width=72) (actual time=0.002..0.003 rows=1 loops=34)

  • Index Cond: (id = it.id)
37. 0.102 0.102 ↑ 1.0 1 34

Index Scan using itemdetail_pkey on itemdetail dx (cost=0.41..1.13 rows=1 width=1,636) (actual time=0.003..0.003 rows=1 loops=34)

  • Index Cond: (id = it.id)
38. 0.102 0.102 ↑ 1.0 1 34

Index Scan using item_pkey on item i (cost=0.29..0.38 rows=1 width=123) (actual time=0.003..0.003 rows=1 loops=34)

  • Index Cond: (id = it.id)
39. 2.186 4.692 ↓ 181.6 1,271 34

Materialize (cost=198.78..240.39 rows=7 width=27) (actual time=0.052..0.138 rows=1,271 loops=34)

40. 0.515 2.506 ↓ 181.6 1,271 1

Hash Join (cost=198.78..240.36 rows=7 width=27) (actual time=1.781..2.506 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))
41. 0.697 1.261 ↑ 1.0 1,271 1

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

  • Group Key: priceunit.id_item
42. 0.564 0.564 ↓ 1.0 1,311 1

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

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

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

  • Buckets: 2048 Batches: 1 Memory Usage: 120kB
44. 0.360 0.360 ↓ 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.360 rows=1,581 loops=1)

Planning time : 22.179 ms
Execution time : 27.577 ms