explain.depesz.com

PostgreSQL's explain analyze made readable

Result: vAi

Settings
# exclusive inclusive rows x rows loops node
1. 209.472 17,315.569 ↑ 16,022.4 32,479 1

Merge Right Join (cost=390,366.94..16,051,055.96 rows=520,390,014 width=1,467) (actual time=17,055.644..17,315.569 rows=32,479 loops=1)

  • Output: pp.product_id, pp.is_alcohol, fa.type, fa.name, fa.name_trigram, fa.category_site_name, fa.category_site_names, fa.brand_name, fa.manufacturer_name, regexp_replace(regexp_replace((ppi.description)::text, '<.*?>'::text, ''::text, 'g'::text), '[nr]+'::text, ' '::text, 'g'::text), fa.feature_field, fa.feature_json, product_popularity.data, fa.price_json, (date_part('epoch'::text, timezone('utc'::text, now())))::integer
  • Merge Cond: (fa.product_id = pp.product_id)
  • Buffers: shared hit=2700828
2.          

CTE product_popularity

3. 37.153 58.954 ↑ 1.0 20,229 1

GroupAggregate (cost=0.29..1,950.57 rows=20,229 width=12) (actual time=0.054..58.954 rows=20,229 loops=1)

  • Output: pp_1.product_id, json_object_agg(pp_1.region_id, pp_1.popularity)
  • Group Key: pp_1.product_id
  • Buffers: shared hit=29806
4. 21.801 21.801 ↑ 1.0 29,878 1

Index Scan using product_popularity_pkey on product.product_popularity pp_1 (cost=0.29..1,548.32 rows=29,878 width=12) (actual time=0.029..21.801 rows=29,878 loops=1)

  • Output: pp_1.region_id, pp_1.product_id, pp_1.popularity, pp_1.updated_at
  • Buffers: shared hit=29806
5.          

CTE products

6. 36.574 41.307 ↓ 1.0 32,479 1

Bitmap Heap Scan on product.product pp_2 (cost=597.88..10,347.95 rows=32,078 width=55) (actual time=6.798..41.307 rows=32,479 loops=1)

  • Output: pp_2.product_id, pp_2.is_alcohol, pp_2.name
  • Recheck Cond: (pp_2.product_status_id = ANY ('{4,5,6}'::integer[]))
  • Heap Blocks: exact=7763
  • Buffers: shared hit=7861
7. 4.733 4.733 ↓ 1.0 32,479 1

Bitmap Index Scan on fki_product_product_status_id_fkey (cost=0.00..589.86 rows=32,078 width=0) (actual time=4.733..4.733 rows=32,479 loops=1)

  • Index Cond: (pp_2.product_status_id = ANY ('{4,5,6}'::integer[]))
  • Buffers: shared hit=98
8.          

CTE products_activity

9. 211.890 339.255 ↓ 162.4 32,479 1

GroupAggregate (cost=10,839.43..12,680.77 rows=200 width=12) (actual time=116.768..339.255 rows=32,479 loops=1)

  • Output: pp_3.product_id, array_agg(DISTINCT ppa.shop_id), array_agg(DISTINCT ('s'::text || (ppa.shop_id)::text)), array_agg(DISTINCT ((('s'::text || (ppa.shop_id)::text) || 'mdd'::text) || (ppa.min_delivery_date)::text))
  • Group Key: pp_3.product_id
  • Buffers: shared hit=1331
10. 30.013 127.365 ↓ 1.2 56,676 1

Sort (cost=10,839.43..10,961.95 rows=49,009 width=12) (actual time=116.672..127.365 rows=56,676 loops=1)

  • Output: pp_3.product_id, ppa.shop_id, ppa.min_delivery_date
  • Sort Key: pp_3.product_id
  • Sort Method: quicksort Memory: 4193kB
  • Buffers: shared hit=1331
11. 23.892 97.352 ↓ 1.2 56,676 1

Hash Left Join (cost=3,975.99..7,021.45 rows=49,009 width=12) (actual time=46.356..97.352 rows=56,676 loops=1)

  • Output: pp_3.product_id, ppa.shop_id, ppa.min_delivery_date
  • Hash Cond: ((ps.shop_id = ppa.shop_id) AND (pp_3.product_id = ppa.product_id))
  • Buffers: shared hit=1331
12. 18.384 53.542 ↓ 1.2 56,676 1

Hash Left Join (cost=2,199.88..3,973.09 rows=49,009 width=8) (actual time=26.315..53.542 rows=56,676 loops=1)

  • Output: pp_3.product_id, ps.shop_id
  • Hash Cond: (pp_3.product_id = ps.product_id)
  • Buffers: shared hit=759
13. 9.016 9.016 ↓ 1.0 32,479 1

CTE Scan on products pp_3 (cost=0.00..641.56 rows=32,078 width=4) (actual time=0.014..9.016 rows=32,479 loops=1)

  • Output: pp_3.product_id, pp_3.is_alcohol, pp_3.name
14. 7.848 26.142 ↑ 1.0 46,189 1

Hash (cost=1,620.97..1,620.97 rows=46,312 width=8) (actual time=26.142..26.142 rows=46,189 loops=1)

  • Output: ps.product_id, ps.shop_id
  • Buckets: 65536 Batches: 1 Memory Usage: 2317kB
  • Buffers: shared hit=759
15. 18.294 18.294 ↑ 1.0 46,189 1

Seq Scan on product.shipment ps (cost=0.00..1,620.97 rows=46,312 width=8) (actual time=0.023..18.294 rows=46,189 loops=1)

  • Output: ps.product_id, ps.shop_id
  • Filter: (ps.amount > '0'::numeric)
  • Rows Removed by Filter: 22769
  • Buffers: shared hit=759
16. 8.415 19.918 ↑ 1.0 41,772 1

Hash (cost=1,146.23..1,146.23 rows=41,992 width=12) (actual time=19.918..19.918 rows=41,772 loops=1)

  • Output: ppa.shop_id, ppa.min_delivery_date, ppa.product_id
  • Buckets: 65536 Batches: 1 Memory Usage: 2171kB
  • Buffers: shared hit=572
17. 11.503 11.503 ↑ 1.0 41,772 1

Seq Scan on product.product_activity ppa (cost=0.00..1,146.23 rows=41,992 width=12) (actual time=0.009..11.503 rows=41,772 loops=1)

  • Output: ppa.shop_id, ppa.min_delivery_date, ppa.product_id
  • Filter: ppa.is_active
  • Rows Removed by Filter: 15249
  • Buffers: shared hit=572
18.          

CTE prices

19. 232.159 391.798 ↓ 162.4 32,479 1

GroupAggregate (cost=15,987.01..23,865.66 rows=200 width=18) (actual time=100.110..391.798 rows=32,479 loops=1)

  • Output: pp_4.product_id, array_agg(DISTINCT ((('"r'::text || (p_price.region_id)::text) || '":'::text) || (COALESCE(CASE WHEN (p_price.rule_price > '0'::numeric) THEN p_price.rule_price ELSE p_price.price END, '0'::numeric))::text))
  • Group Key: pp_4.product_id
  • Buffers: shared hit=1309
20. 37.715 159.639 ↑ 1.7 111,329 1

Merge Left Join (cost=15,987.01..19,040.81 rows=192,894 width=18) (actual time=100.039..159.639 rows=111,329 loops=1)

  • Output: pp_4.product_id, p_price.region_id, p_price.rule_price, p_price.price
  • Merge Cond: (pp_4.product_id = p_price.product_id)
  • Buffers: shared hit=1309
21. 16.796 23.047 ↓ 1.0 32,479 1

Sort (cost=3,042.49..3,122.68 rows=32,078 width=4) (actual time=18.371..23.047 rows=32,479 loops=1)

  • Output: pp_4.product_id
  • Sort Key: pp_4.product_id
  • Sort Method: quicksort Memory: 2291kB
22. 6.251 6.251 ↓ 1.0 32,479 1

CTE Scan on products pp_4 (cost=0.00..641.56 rows=32,078 width=4) (actual time=0.002..6.251 rows=32,479 loops=1)

  • Output: pp_4.product_id
23. 68.274 98.877 ↑ 1.0 123,068 1

Sort (cost=12,944.52..13,252.19 rows=123,068 width=18) (actual time=81.655..98.877 rows=123,068 loops=1)

  • Output: p_price.region_id, p_price.rule_price, p_price.price, p_price.product_id
  • Sort Key: p_price.product_id
  • Sort Method: quicksort Memory: 10837kB
  • Buffers: shared hit=1309
24. 30.603 30.603 ↑ 1.0 123,068 1

Seq Scan on product.price p_price (cost=0.00..2,539.68 rows=123,068 width=18) (actual time=0.020..30.603 rows=123,068 loops=1)

  • Output: p_price.region_id, p_price.rule_price, p_price.price, p_price.product_id
  • Buffers: shared hit=1309
25.          

CTE categories

26. 490.776 873.006 ↓ 162.4 32,478 1

GroupAggregate (cost=20,974.90..22,201.54 rows=200 width=556) (actual time=366.876..873.006 rows=32,478 loops=1)

  • Output: pp_5.product_id, (array_agg(DISTINCT ppcs.category_site_id) FILTER (WHERE pptcs.is_primary))[1], (array_agg(DISTINCT ppcs.name) FILTER (WHERE pptcs.is_primary))[1], array_agg(DISTINCT ppcs.category_site_id), array_agg(DISTINCT ('c'::text || (ppcs.category_site_id)::text)), array_agg(DISTINCT ppcs.name) FILTER (WHERE (NOT ppcs.is_hidden)), array_remove(array_agg(DISTINCT pcss.value), NULL::character varying)
  • Group Key: pp_5.product_id
  • Buffers: shared hit=12694
27. 45.141 382.230 ↓ 1.3 56,028 1

Sort (cost=20,974.90..21,085.91 rows=44,405 width=556) (actual time=366.744..382.230 rows=56,028 loops=1)

  • Output: pp_5.product_id, ppcs.category_site_id, pptcs.is_primary, ppcs.name, ppcs.is_hidden, pcss.value
  • Sort Key: pp_5.product_id
  • Sort Method: quicksort Memory: 6881kB
  • Buffers: shared hit=12694
28. 16.206 337.089 ↓ 1.3 56,028 1

Hash Left Join (cost=13,910.60..17,547.18 rows=44,405 width=556) (actual time=209.311..337.089 rows=56,028 loops=1)

  • Output: pp_5.product_id, ppcs.category_site_id, pptcs.is_primary, ppcs.name, ppcs.is_hidden, pcss.value
  • Hash Cond: (pptcs.category_site_id = pcss.category_site_id)
  • Buffers: shared hit=12694
29. 23.296 320.866 ↓ 1.2 54,915 1

Hash Join (cost=13,909.20..17,363.24 rows=44,405 width=44) (actual time=209.273..320.866 rows=54,915 loops=1)

  • Output: pp_5.product_id, pptcs.is_primary, pptcs.category_site_id, ppcs.category_site_id, ppcs.name, ppcs.is_hidden
  • Hash Cond: (pptcs.category_site_id = ppcs.category_site_id)
  • Buffers: shared hit=12693
30. 36.617 296.963 ↓ 1.2 59,500 1

Hash Join (cost=13,832.91..16,656.26 rows=49,770 width=9) (actual time=208.654..296.963 rows=59,500 loops=1)

  • Output: pp_5.product_id, pptcs.is_primary, pptcs.category_site_id
  • Hash Cond: (pp_5.product_id = pptcs.product_id)
  • Buffers: shared hit=12633
31. 59.793 59.793 ↓ 1.0 32,479 1

CTE Scan on products pp_5 (cost=0.00..641.56 rows=32,078 width=4) (actual time=6.804..59.793 rows=32,479 loops=1)

  • Output: pp_5.product_id, pp_5.is_alcohol, pp_5.name
  • Buffers: shared hit=7861
32. 113.865 200.553 ↑ 1.0 402,707 1

Hash (cost=8,799.07..8,799.07 rows=402,707 width=9) (actual time=200.553..200.553 rows=402,707 loops=1)

  • Output: pptcs.is_primary, pptcs.product_id, pptcs.category_site_id
  • Buckets: 524288 Batches: 1 Memory Usage: 21400kB
  • Buffers: shared hit=4772
33. 86.688 86.688 ↑ 1.0 402,707 1

Seq Scan on product.product_to_category_site pptcs (cost=0.00..8,799.07 rows=402,707 width=9) (actual time=0.009..86.688 rows=402,707 loops=1)

  • Output: pptcs.is_primary, pptcs.product_id, pptcs.category_site_id
  • Buffers: shared hit=4772
34. 0.189 0.607 ↑ 1.0 687 1

Hash (cost=67.70..67.70 rows=687 width=35) (actual time=0.607..0.607 rows=687 loops=1)

  • Output: ppcs.category_site_id, ppcs.name, ppcs.is_hidden
  • Buckets: 1024 Batches: 1 Memory Usage: 54kB
  • Buffers: shared hit=60
35. 0.418 0.418 ↑ 1.0 687 1

Seq Scan on product.category_site ppcs (cost=0.00..67.70 rows=687 width=35) (actual time=0.030..0.418 rows=687 loops=1)

  • Output: ppcs.category_site_id, ppcs.name, ppcs.is_hidden
  • Filter: (ppcs.deleted_at IS NULL)
  • Rows Removed by Filter: 83
  • Buffers: shared hit=60
36. 0.007 0.017 ↑ 1.0 18 1

Hash (cost=1.18..1.18 rows=18 width=520) (actual time=0.017..0.017 rows=18 loops=1)

  • Output: pcss.value, pcss.category_site_id
  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
  • Buffers: shared hit=1
37. 0.010 0.010 ↑ 1.0 18 1

Seq Scan on product.category_site_synonyms pcss (cost=0.00..1.18 rows=18 width=520) (actual time=0.007..0.010 rows=18 loops=1)

  • Output: pcss.value, pcss.category_site_id
  • Buffers: shared hit=1
38.          

CTE product_synonyms

39. 88.950 123.304 ↓ 162.4 32,479 1

GroupAggregate (cost=3,043.79..3,393.53 rows=200 width=520) (actual time=18.366..123.304 rows=32,479 loops=1)

  • Output: pp_6.product_id, array_agg(DISTINCT pps.value)
  • Group Key: pp_6.product_id
  • Buffers: shared hit=1
40. 10.323 34.354 ↓ 1.0 32,479 1

Merge Left Join (cost=3,043.79..3,230.64 rows=32,078 width=520) (actual time=18.343..34.354 rows=32,479 loops=1)

  • Output: pp_6.product_id, pps.value
  • Merge Cond: (pp_6.product_id = pps.product_id)
  • Buffers: shared hit=1
41. 17.852 23.985 ↓ 1.0 32,479 1

Sort (cost=3,042.49..3,122.68 rows=32,078 width=4) (actual time=18.291..23.985 rows=32,479 loops=1)

  • Output: pp_6.product_id
  • Sort Key: pp_6.product_id
  • Sort Method: quicksort Memory: 2291kB
42. 6.133 6.133 ↓ 1.0 32,479 1

CTE Scan on products pp_6 (cost=0.00..641.56 rows=32,078 width=4) (actual time=0.002..6.133 rows=32,479 loops=1)

  • Output: pp_6.product_id
43. 0.021 0.046 ↑ 1.0 11 1

Sort (cost=1.30..1.33 rows=11 width=520) (actual time=0.043..0.046 rows=11 loops=1)

  • Output: pps.value, pps.product_id
  • Sort Key: pps.product_id
  • Sort Method: quicksort Memory: 25kB
  • Buffers: shared hit=1
44. 0.025 0.025 ↑ 1.0 11 1

Seq Scan on product.product_synonyms pps (cost=0.00..1.11 rows=11 width=520) (actual time=0.023..0.025 rows=11 loops=1)

  • Output: pps.value, pps.product_id
  • Buffers: shared hit=1
45.          

CTE properties

46. 352.494 1,970.280 ↓ 162.4 32,479 1

GroupAggregate (cost=111,000.58..115,300.77 rows=200 width=181) (actual time=1,535.180..1,970.280 rows=32,479 loops=1)

  • Output: pp_7.product_id, array_cat(array_agg(DISTINCT pv_type.data), array_agg(DISTINCT pvs_type.value))
  • Group Key: pp_7.product_id
  • Buffers: shared hit=15340
47. 400.032 1,617.786 ↓ 1.7 711,592 1

Sort (cost=111,000.58..112,074.75 rows=429,669 width=181) (actual time=1,535.052..1,617.786 rows=711,592 loops=1)

  • Output: pp_7.product_id, pv_type.data, pvs_type.value
  • Sort Key: pp_7.product_id
  • Sort Method: quicksort Memory: 59558kB
  • Buffers: shared hit=15340
48. 197.457 1,217.754 ↓ 1.7 711,592 1

Hash Left Join (cost=45,354.52..70,798.89 rows=429,669 width=181) (actual time=607.238..1,217.754 rows=711,592 loops=1)

  • Output: pp_7.product_id, pv_type.data, pvs_type.value
  • Hash Cond: ((pptv_type.value_id = pv_type.value_id) AND (ppitop.property_id = pv_type.property_id))
  • Buffers: shared hit=15340
49. 136.680 969.850 ↓ 1.7 710,423 1

Hash Left Join (cost=40,876.84..63,092.43 rows=429,669 width=12) (actual time=556.530..969.850 rows=710,423 loops=1)

  • Output: pp_7.product_id, pptv_type.value_id, ppitop.property_id
  • Hash Cond: (pptv_type.property_id = ppitop.property_id)
  • Buffers: shared hit=12844
50. 291.427 833.099 ↓ 1.6 708,570 1

Hash Left Join (cost=40,872.53..59,203.35 rows=429,669 width=12) (actual time=556.426..833.099 rows=708,570 loops=1)

  • Output: pp_7.product_id, pptv_type.property_id, pptv_type.value_id
  • Hash Cond: (pp_7.product_id = pptv_type.product_id)
  • Buffers: shared hit=12843
51. 12.190 12.190 ↓ 1.0 32,479 1

CTE Scan on products pp_7 (cost=0.00..641.56 rows=32,078 width=4) (actual time=0.001..12.190 rows=32,479 loops=1)

  • Output: pp_7.product_id, pp_7.is_alcohol, pp_7.name
52. 296.811 529.482 ↑ 1.0 1,245,757 1

Hash (cost=25,300.57..25,300.57 rows=1,245,757 width=12) (actual time=529.482..529.482 rows=1,245,757 loops=1)

  • Output: pptv_type.product_id, pptv_type.property_id, pptv_type.value_id
  • Buckets: 2097152 Batches: 1 Memory Usage: 69913kB
  • Buffers: shared hit=12843
53. 232.671 232.671 ↑ 1.0 1,245,757 1

Seq Scan on product.product_to_value pptv_type (cost=0.00..25,300.57 rows=1,245,757 width=12) (actual time=0.030..232.671 rows=1,245,757 loops=1)

  • Output: pptv_type.product_id, pptv_type.property_id, pptv_type.value_id
  • Buffers: shared hit=12843
54. 0.029 0.071 ↑ 1.0 147 1

Hash (cost=2.47..2.47 rows=147 width=4) (actual time=0.071..0.071 rows=147 loops=1)

  • Output: ppitop.property_id
  • Buckets: 1024 Batches: 1 Memory Usage: 14kB
  • Buffers: shared hit=1
55. 0.042 0.042 ↑ 1.0 147 1

Seq Scan on product.property_is_type_of_product ppitop (cost=0.00..2.47 rows=147 width=4) (actual time=0.022..0.042 rows=147 loops=1)

  • Output: ppitop.property_id
  • Buffers: shared hit=1
56. 23.233 50.447 ↓ 1.0 58,634 1

Hash (cost=3,598.75..3,598.75 rows=58,595 width=185) (actual time=50.447..50.447 rows=58,634 loops=1)

  • Output: pv_type.data, pv_type.value_id, pv_type.property_id, pvs_type.value
  • Buckets: 65536 Batches: 1 Memory Usage: 12272kB
  • Buffers: shared hit=2496
57. 16.822 27.214 ↓ 1.0 58,634 1

Hash Left Join (cost=4.83..3,598.75 rows=58,595 width=185) (actual time=0.086..27.214 rows=58,634 loops=1)

  • Output: pv_type.data, pv_type.value_id, pv_type.property_id, pvs_type.value
  • Hash Cond: (pv_type.value_id = pvs_type.value_id)
  • Buffers: shared hit=2496
58. 10.332 10.332 ↑ 1.0 58,595 1

Seq Scan on product.value pv_type (cost=0.00..3,079.95 rows=58,595 width=171) (actual time=0.008..10.332 rows=58,595 loops=1)

  • Output: pv_type.value_id, pv_type.created_at, pv_type.data, pv_type.property_id, pv_type.translit, pv_type.is_translit_manual, pv_type.is_checked, pv_type.icon, pv_type.updated_at, pv_type.value_sort
  • Buffers: shared hit=2494
59. 0.031 0.060 ↑ 1.0 126 1

Hash (cost=3.26..3.26 rows=126 width=18) (actual time=0.060..0.060 rows=126 loops=1)

  • Output: pvs_type.value, pvs_type.value_id
  • Buckets: 1024 Batches: 1 Memory Usage: 15kB
  • Buffers: shared hit=2
60. 0.029 0.029 ↑ 1.0 126 1

Seq Scan on product.value_synonyms pvs_type (cost=0.00..3.26 rows=126 width=18) (actual time=0.007..0.029 rows=126 loops=1)

  • Output: pvs_type.value, pvs_type.value_id
  • Buffers: shared hit=2
61.          

CTE p

62. 2,692.954 7,029.586 ↓ 162.4 32,479 1

GroupAggregate (cost=105,300.99..130,015.46 rows=200 width=284) (actual time=4,134.410..7,029.586 rows=32,479 loops=1)

  • Output: pp_8.product_id, array_agg(DISTINCT (((((((((ppr.name)::text || ' '::text) || (pv.data)::text) || ' '::text) || (COALESCE(pvs.value, ''::character varying))::text) || ' '::text) || (COALESCE(pm.name, ''::character varying))::text) || ' '::text) || (COALESCE(pm.short, ''::character varying))::text)), array_remove(array_cat(array_agg(DISTINCT CASE WHEN (pv.property_id = 147) THEN pv.data ELSE NULL::character varying END), array_agg(DISTINCT CASE WHEN (pv.property_id = 147) THEN pvs.value ELSE NULL::character varying END)), NULL::character varying), array_remove(array_cat(array_agg(DISTINCT CASE WHEN (pv.property_id = 148) THEN pv.data ELSE NULL::character varying END), array_agg(DISTINCT CASE WHEN (pv.property_id = 148) THEN pvs.value ELSE NULL::character varying END)), NULL::character varying), (array_remove(array_agg(DISTINCT CASE WHEN (pv.property_id = 147) THEN pv.value_id ELSE NULL::integer END), NULL::integer))[1], (array_remove(array_agg(DISTINCT CASE WHEN (pv.property_id = 148) THEN pv.value_id ELSE NULL::integer END), NULL::integer))[1]
  • Group Key: pp_8.product_id
  • Buffers: shared hit=2510085
63. 680.729 4,336.632 ↓ 1.7 710,646 1

Sort (cost=105,300.99..106,375.16 rows=429,669 width=284) (actual time=4,134.178..4,336.632 rows=710,646 loops=1)

  • Output: pp_8.product_id, ppr.name, pv.data, pvs.value, pm.name, pm.short, pv.property_id, pv.value_id
  • Sort Key: pp_8.product_id
  • Sort Method: quicksort Memory: 123092kB
  • Buffers: shared hit=2509827
64. 366.908 3,655.903 ↓ 1.7 710,646 1

Hash Left Join (cost=4,337.97..65,099.30 rows=429,669 width=284) (actual time=50.592..3,655.903 rows=710,646 loops=1)

  • Output: pp_8.product_id, ppr.name, pv.data, pvs.value, pm.name, pm.short, pv.property_id, pv.value_id
  • Hash Cond: (pptv.value_id = pv.value_id)
  • Buffers: shared hit=2509827
65. 171.094 3,238.770 ↓ 1.6 708,570 1

Nested Loop Left Join (cost=6.78..54,860.16 rows=429,669 width=103) (actual time=0.217..3,238.770 rows=708,570 loops=1)

  • Output: pp_8.product_id, pptv.value_id, ppr.name, pm.name, pm.short
  • Buffers: shared hit=2507331
66. 14.650 14.650 ↓ 1.0 32,479 1

CTE Scan on products pp_8 (cost=0.00..641.56 rows=32,078 width=4) (actual time=0.001..14.650 rows=32,479 loops=1)

  • Output: pp_8.product_id, pp_8.is_alcohol, pp_8.name
67. 487.197 3,053.026 ↓ 1.7 22 32,479

Hash Right Join (cost=6.78..8.34 rows=13 width=103) (actual time=0.080..0.094 rows=22 loops=32,479)

  • Output: pptv.product_id, pptv.value_id, ppr.name, pm.name, pm.short
  • Hash Cond: (pm.measure_id = ppr.measure_id)
  • Buffers: shared hit=2507331
68. 129.904 129.904 ↑ 1.0 36 32,476

Seq Scan on product.measure pm (cost=0.00..1.36 rows=36 width=68) (actual time=0.001..0.004 rows=36 loops=32,476)

  • Output: pm.measure_id, pm.created_at, pm.name, pm.short
  • Buffers: shared hit=32476
69. 227.353 2,435.925 ↓ 1.7 22 32,479

Hash (cost=6.62..6.62 rows=13 width=43) (actual time=0.075..0.075 rows=22 loops=32,479)

  • Output: pptv.product_id, pptv.value_id, ppr.name, ppr.measure_id
  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
  • Buffers: shared hit=2474855
70. 206.816 2,208.572 ↓ 1.7 22 32,479

Nested Loop Left Join (cost=0.70..6.62 rows=13 width=43) (actual time=0.009..0.068 rows=22 loops=32,479)

  • Output: pptv.product_id, pptv.value_id, ppr.name, ppr.measure_id
  • Buffers: shared hit=2474855
71. 584.622 584.622 ↓ 1.7 22 32,479

Index Scan using fki_product_to_value_product_id_fkey on product.product_to_value pptv (cost=0.43..2.68 rows=13 width=12) (actual time=0.006..0.018 rows=22 loops=32,479)

  • Output: pptv.product_to_value_id, pptv.created_at, pptv.product_id, pptv.value_id, pptv.start_at, pptv.end_at, pptv.is_active, pptv.property_id, pptv.updated_at
  • Index Cond: (pp_8.product_id = pptv.product_id)
  • Buffers: shared hit=347866
72. 1,417.134 1,417.134 ↑ 1.0 1 708,567

Index Scan using property_pkey on product.property ppr (cost=0.28..0.29 rows=1 width=39) (actual time=0.001..0.002 rows=1 loops=708,567)

  • Output: ppr.property_id, ppr.created_at, ppr.name, ppr.property_type_id, ppr.measure_id, ppr.translit, ppr.is_translit_manual, ppr.is_translit_locked, ppr.updated_at, ppr.icon, ppr.is_need_check, ppr.is_multiple, ppr.min_search_length
  • Index Cond: (pptv.property_id = ppr.property_id)
  • Buffers: shared hit=2126989
73. 21.135 50.225 ↓ 1.0 58,634 1

Hash (cost=3,598.75..3,598.75 rows=58,595 width=185) (actual time=50.225..50.225 rows=58,634 loops=1)

  • Output: pv.data, pv.property_id, pv.value_id, pvs.value
  • Buckets: 65536 Batches: 1 Memory Usage: 12272kB
  • Buffers: shared hit=2496
74. 17.686 29.090 ↓ 1.0 58,634 1

Hash Left Join (cost=4.83..3,598.75 rows=58,595 width=185) (actual time=0.075..29.090 rows=58,634 loops=1)

  • Output: pv.data, pv.property_id, pv.value_id, pvs.value
  • Hash Cond: (pv.value_id = pvs.value_id)
  • Buffers: shared hit=2496
75. 11.348 11.348 ↑ 1.0 58,595 1

Seq Scan on product.value pv (cost=0.00..3,079.95 rows=58,595 width=171) (actual time=0.008..11.348 rows=58,595 loops=1)

  • Output: pv.value_id, pv.created_at, pv.data, pv.property_id, pv.translit, pv.is_translit_manual, pv.is_checked, pv.icon, pv.updated_at, pv.value_sort
  • Buffers: shared hit=2494
76. 0.032 0.056 ↑ 1.0 126 1

Hash (cost=3.26..3.26 rows=126 width=18) (actual time=0.056..0.056 rows=126 loops=1)

  • Output: pvs.value, pvs.value_id
  • Buckets: 1024 Batches: 1 Memory Usage: 15kB
  • Buffers: shared hit=2
77. 0.024 0.024 ↑ 1.0 126 1

Seq Scan on product.value_synonyms pvs (cost=0.00..3.26 rows=126 width=18) (actual time=0.010..0.024 rows=126 loops=1)

  • Output: pvs.value, pvs.value_id
  • Buffers: shared hit=2
78.          

CTE final_aggregation

79. 4,850.778 16,373.610 ↓ 1.0 32,478 1

Merge Left Join (cost=3,112.35..16,746.50 rows=32,078 width=496) (actual time=11,231.454..16,373.610 rows=32,478 loops=1)

  • Output: p.product_id, array_to_string(properties.type_array, ' '::text), (((pp_9.name)::text || ' '::text) || array_to_string(ps_1.name_synonyms_array, ' '::text)), get_trigram(((((pp_9.name)::text || ' '::text) || array_to_string(ps_1.name_synonyms_array, ' '::text)))::character varying), (((c.category_site_name)::text || ' '::text) || array_to_string(c.category_site_name_synonyms, ' '::text)), array_to_string(c.category_site_names, ' '::text), array_to_string(p.brand_name_array, ' '::text), array_to_string(p.manufacturer_name_array, ' '::text), array_to_string(p.values_array, ', '::text), array_to_string(array_remove(array_cat(array_cat(array_cat(ARRAY[('b'::text || (p.brand_id)::text), ('m'::text || (p.manufacturer_id)::text)], c.category_site_string_ids), pa.shop_activity_string_ids), pa.min_delivery_dates), NULL::text), ','::text), json_build_object('category_site_ids', c.category_site_ids, 'category_site_id', c.category_site_id, 'brand_id', p.brand_id, 'manufacturer_id', p.manufacturer_id, 'shop_ids', pa.shop_activity_ids), (('{'::text || array_to_string(prices.price, ','::text)) || '}'::text)
  • Merge Cond: (pp_9.product_id = properties.product_id)
  • Buffers: shared hit=2540760
80. 24.195 9,498.404 ↓ 1.0 32,478 1

Merge Left Join (cost=3,100.70..5,828.33 rows=32,078 width=468) (actual time=9,219.078..9,498.404 rows=32,478 loops=1)

  • Output: pp_9.name, pp_9.product_id, c.category_site_name, c.category_site_name_synonyms, c.category_site_names, c.category_site_string_ids, c.category_site_ids, c.category_site_id, ps_1.name_synonyms_array, prices.price, p.product_id, p.brand_name_array, p.manufacturer_name_array, p.values_array, p.brand_id, p.manufacturer_id, pa.shop_activity_string_ids, pa.min_delivery_dates, pa.shop_activity_ids
  • Merge Cond: (pp_9.product_id = pa.product_id)
  • Buffers: shared hit=2525420
81. 39.449 9,083.844 ↓ 1.0 32,478 1

Merge Left Join (cost=3,089.06..5,255.33 rows=32,078 width=372) (actual time=8,843.815..9,083.844 rows=32,478 loops=1)

  • Output: pp_9.name, pp_9.product_id, c.category_site_name, c.category_site_name_synonyms, c.category_site_names, c.category_site_string_ids, c.category_site_ids, c.category_site_id, ps_1.name_synonyms_array, prices.price, p.product_id, p.brand_name_array, p.manufacturer_name_array, p.values_array, p.brand_id, p.manufacturer_id
  • Merge Cond: (pp_9.product_id = p.product_id)
  • Buffers: shared hit=2524089
82. 21.028 1,656.795 ↓ 1.0 32,478 1

Merge Left Join (cost=3,077.42..4,682.32 rows=32,078 width=264) (actual time=1,528.623..1,656.795 rows=32,478 loops=1)

  • Output: pp_9.name, pp_9.product_id, c.category_site_name, c.category_site_name_synonyms, c.category_site_names, c.category_site_string_ids, c.category_site_ids, c.category_site_id, ps_1.name_synonyms_array, prices.price
  • Merge Cond: (pp_9.product_id = prices.product_id)
  • Buffers: shared hit=14004
83. 20.573 1,204.164 ↓ 1.0 32,478 1

Merge Left Join (cost=3,065.77..4,109.31 rows=32,078 width=232) (actual time=1,108.584..1,204.164 rows=32,478 loops=1)

  • Output: pp_9.name, pp_9.product_id, c.category_site_name, c.category_site_name_synonyms, c.category_site_names, c.category_site_string_ids, c.category_site_ids, c.category_site_id, ps_1.name_synonyms_array
  • Merge Cond: (pp_9.product_id = ps_1.product_id)
  • Buffers: shared hit=12695
84. 29.386 1,022.388 ↓ 1.0 32,478 1

Merge Join (cost=3,054.13..3,536.30 rows=32,078 width=200) (actual time=956.105..1,022.388 rows=32,478 loops=1)

  • Output: pp_9.name, pp_9.product_id, c.category_site_name, c.category_site_name_synonyms, c.category_site_names, c.category_site_string_ids, c.category_site_ids, c.category_site_id
  • Merge Cond: (c.product_id = pp_9.product_id)
  • Buffers: shared hit=12694
85. 36.245 948.240 ↓ 162.4 32,478 1

Sort (cost=11.64..12.14 rows=200 width=168) (actual time=932.797..948.240 rows=32,478 loops=1)

  • Output: c.category_site_name, c.category_site_name_synonyms, c.category_site_names, c.category_site_string_ids, c.category_site_ids, c.category_site_id, c.product_id
  • Sort Key: c.product_id
  • Sort Method: quicksort Memory: 10366kB
  • Buffers: shared hit=12694
86. 911.995 911.995 ↓ 162.4 32,478 1

CTE Scan on categories c (cost=0.00..4.00 rows=200 width=168) (actual time=366.880..911.995 rows=32,478 loops=1)

  • Output: c.category_site_name, c.category_site_name_synonyms, c.category_site_names, c.category_site_string_ids, c.category_site_ids, c.category_site_id, c.product_id
  • Buffers: shared hit=12694
87. 36.790 44.762 ↓ 1.0 32,479 1

Sort (cost=3,042.49..3,122.68 rows=32,078 width=36) (actual time=23.297..44.762 rows=32,479 loops=1)

  • Output: pp_9.name, pp_9.product_id
  • Sort Key: pp_9.product_id
  • Sort Method: quicksort Memory: 5426kB
88. 7.972 7.972 ↓ 1.0 32,479 1

CTE Scan on products pp_9 (cost=0.00..641.56 rows=32,078 width=36) (actual time=0.006..7.972 rows=32,479 loops=1)

  • Output: pp_9.name, pp_9.product_id
89. 18.874 161.203 ↓ 162.4 32,479 1

Sort (cost=11.64..12.14 rows=200 width=36) (actual time=152.473..161.203 rows=32,479 loops=1)

  • Output: ps_1.name_synonyms_array, ps_1.product_id
  • Sort Key: ps_1.product_id
  • Sort Method: quicksort Memory: 3306kB
  • Buffers: shared hit=1
90. 142.329 142.329 ↓ 162.4 32,479 1

CTE Scan on product_synonyms ps_1 (cost=0.00..4.00 rows=200 width=36) (actual time=18.370..142.329 rows=32,479 loops=1)

  • Output: ps_1.name_synonyms_array, ps_1.product_id
  • Buffers: shared hit=1
91. 21.876 431.603 ↓ 162.4 32,479 1

Sort (cost=11.64..12.14 rows=200 width=36) (actual time=420.031..431.603 rows=32,479 loops=1)

  • Output: prices.price, prices.product_id
  • Sort Key: prices.product_id
  • Sort Method: quicksort Memory: 5782kB
  • Buffers: shared hit=1309
92. 409.727 409.727 ↓ 162.4 32,479 1

CTE Scan on prices (cost=0.00..4.00 rows=200 width=36) (actual time=100.116..409.727 rows=32,479 loops=1)

  • Output: prices.price, prices.product_id
  • Buffers: shared hit=1309
93. 197.719 7,387.600 ↓ 162.4 32,479 1

Sort (cost=11.64..12.14 rows=200 width=108) (actual time=7,315.177..7,387.600 rows=32,479 loops=1)

  • Output: p.product_id, p.brand_name_array, p.manufacturer_name_array, p.values_array, p.brand_id, p.manufacturer_id
  • Sort Key: p.product_id
  • Sort Method: quicksort Memory: 78399kB
  • Buffers: shared hit=2510085
94. 7,189.881 7,189.881 ↓ 162.4 32,479 1

CTE Scan on p (cost=0.00..4.00 rows=200 width=108) (actual time=4,134.417..7,189.881 rows=32,479 loops=1)

  • Output: p.product_id, p.brand_name_array, p.manufacturer_name_array, p.values_array, p.brand_id, p.manufacturer_id
  • Buffers: shared hit=2510085
95. 28.699 390.365 ↓ 162.4 32,479 1

Sort (cost=11.64..12.14 rows=200 width=100) (actual time=375.253..390.365 rows=32,479 loops=1)

  • Output: pa.shop_activity_string_ids, pa.min_delivery_dates, pa.shop_activity_ids, pa.product_id
  • Sort Key: pa.product_id
  • Sort Method: quicksort Memory: 6639kB
  • Buffers: shared hit=1331
96. 361.666 361.666 ↓ 162.4 32,479 1

CTE Scan on products_activity pa (cost=0.00..4.00 rows=200 width=100) (actual time=116.774..361.666 rows=32,479 loops=1)

  • Output: pa.shop_activity_string_ids, pa.min_delivery_dates, pa.shop_activity_ids, pa.product_id
  • Buffers: shared hit=1331
97. 24.643 2,024.428 ↓ 162.4 32,479 1

Sort (cost=11.64..12.14 rows=200 width=36) (actual time=2,011.993..2,024.428 rows=32,479 loops=1)

  • Output: properties.type_array, properties.product_id
  • Sort Key: properties.product_id
  • Sort Method: quicksort Memory: 5522kB
  • Buffers: shared hit=15340
98. 1,999.785 1,999.785 ↓ 162.4 32,479 1

CTE Scan on properties (cost=0.00..4.00 rows=200 width=36) (actual time=1,535.186..1,999.785 rows=32,479 loops=1)

  • Output: properties.type_array, properties.product_id
  • Buffers: shared hit=15340
99. 102.983 16,719.607 ↓ 1.0 32,478 1

Sort (cost=3,042.49..3,122.68 rows=32,078 width=324) (actual time=16,704.889..16,719.607 rows=32,478 loops=1)

  • Output: fa.type, fa.name, fa.name_trigram, fa.category_site_name, fa.category_site_names, fa.brand_name, fa.manufacturer_name, fa.feature_field, fa.feature_json, fa.price_json, fa.product_id
  • Sort Key: fa.product_id
  • Sort Method: quicksort Memory: 34521kB
  • Buffers: shared hit=2540760
100. 16,616.624 16,616.624 ↓ 1.0 32,478 1

CTE Scan on final_aggregation fa (cost=0.00..641.56 rows=32,078 width=324) (actual time=11,231.463..16,616.624 rows=32,478 loops=1)

  • Output: fa.type, fa.name, fa.name_trigram, fa.category_site_name, fa.category_site_names, fa.brand_name, fa.manufacturer_name, fa.feature_field, fa.feature_json, fa.price_json, fa.product_id
  • Buffers: shared hit=2540760
101. 9.468 386.490 ↑ 99.9 32,479 1

Materialize (cost=50,821.72..107,761.36 rows=3,244,529 width=1,147) (actual time=350.653..386.490 rows=32,479 loops=1)

  • Output: pp.product_id, pp.is_alcohol, product_popularity.data, ppi.description
  • Buffers: shared hit=160068
102. 15.195 377.022 ↑ 99.9 32,479 1

Merge Left Join (cost=50,821.72..99,650.04 rows=3,244,529 width=1,147) (actual time=350.648..377.022 rows=32,479 loops=1)

  • Output: pp.product_id, pp.is_alcohol, product_popularity.data, ppi.description
  • Merge Cond: (pp.product_id = product_popularity.product_id)
  • Buffers: shared hit=160068
103. 37.107 283.433 ↓ 1.0 32,479 1

Sort (cost=48,970.35..49,050.54 rows=32,078 width=1,115) (actual time=275.191..283.433 rows=32,479 loops=1)

  • Output: pp.product_id, pp.is_alcohol, ppi.description
  • Sort Key: pp.product_id
  • Sort Method: quicksort Memory: 17229kB
  • Buffers: shared hit=130262
104. 56.968 246.326 ↓ 1.0 32,479 1

Merge Right Join (cost=3,042.91..46,569.42 rows=32,078 width=1,115) (actual time=21.449..246.326 rows=32,479 loops=1)

  • Output: pp.product_id, pp.is_alcohol, ppi.description
  • Merge Cond: (ppi.product_id = pp.product_id)
  • Buffers: shared hit=130262
105. 162.045 162.045 ↑ 1.0 375,518 1

Index Scan using product_info_pkey on product.product_info ppi (cost=0.42..42,105.67 rows=376,038 width=1,114) (actual time=0.069..162.045 rows=375,518 loops=1)

  • Output: ppi.product_id, ppi.description, ppi.sap_name, ppi.conditions, ppi.composition, ppi.sap_short_name, ppi.is_foreign_description, ppi.is_foreign_properties, ppi.is_replacement_blocked, ppi.is_need_to_order, ppi.last_modified, ppi.is_lower_description
  • Buffers: shared hit=130262
106. 17.045 27.313 ↓ 1.0 32,479 1

Sort (cost=3,042.49..3,122.68 rows=32,078 width=5) (actual time=21.361..27.313 rows=32,479 loops=1)

  • Output: pp.product_id, pp.is_alcohol
  • Sort Key: pp.product_id
  • Sort Method: quicksort Memory: 2291kB
107. 10.268 10.268 ↓ 1.0 32,479 1

CTE Scan on products pp (cost=0.00..641.56 rows=32,078 width=5) (actual time=0.031..10.268 rows=32,479 loops=1)

  • Output: pp.product_id, pp.is_alcohol
108. 9.409 78.394 ↑ 1.0 20,229 1

Sort (cost=1,851.37..1,901.94 rows=20,229 width=36) (actual time=75.448..78.394 rows=20,229 loops=1)

  • Output: product_popularity.data, product_popularity.product_id
  • Sort Key: product_popularity.product_id
  • Sort Method: quicksort Memory: 2185kB
  • Buffers: shared hit=29806
109. 68.985 68.985 ↑ 1.0 20,229 1

CTE Scan on product_popularity (cost=0.00..404.58 rows=20,229 width=36) (actual time=0.058..68.985 rows=20,229 loops=1)

  • Output: product_popularity.data, product_popularity.product_id
  • Buffers: shared hit=29806