explain.depesz.com

PostgreSQL's explain analyze made readable

Result: JDfN

Settings
# exclusive inclusive rows x rows loops node
1. 93.616 251,665.176 ↑ 3.1 32,461 1

GroupAggregate (cost=11,854,458.13..11,862,958.13 rows=100,000 width=402) (actual time=251,537.651..251,665.176 rows=32,461 loops=1)

  • Output: anon_1.rollup_date, sum(anon_1.product_count), sum(anon_1.product_count_count), count(NULLIF(anon_1.product_count_total, 0)), sum(anon_1.product_count_total), sum(anon_1.promotion_count_count), sum(anon_1.promotion_count), sum(anon_1.promotion_count_total), sum(NULLIF(anon_1.promotion_count_total, 0)), COALESCE(((sum(anon_1.promotion_count))::double precision / (NULLIF((sum(anon_1.promotion_count_total))::numeric, 0.0))::double precision), '0'::double precision), COALESCE(((sum(anon_1.product_count))::double precision / (NULLIF((sum(anon_1.product_count_total))::numeric, 0.0))::double precision), '0'::double precision), brand.created_on, brand.updated_on, brand.deleted_on, brand.id, brand.parent_id, brand.name, brand.normalized_name, channel.id, channel.name, channel.icon_url, channel.domain, channel.country, channel.gazaro_id, product_group.created_on, product_group.updated_on, product_group.deleted_on, product_group.id, product_group.organization_id, product_group.parent_id, product_group.name, product_group.brand_id, config.created_on, config.updated_on, config.deleted_on, config.id, config.name, config.organization_id, config.sort_position, config.min, config.max, config.config_type, config.is_relevant, config.is_leading, config.exclude_3p, config.exclude_sponsored, config.only_sponsored, config.only_headline_ads, config.description, config.badge_type, config.use_matched_brand
  • Group Key: anon_1.rollup_date, channel.id, brand.id, config.id, product_group.id
  • Buffers: shared hit=850,488 read=866,084, temp read=3,851 written=3,852
2.          

CTE t

3. 0.086 0.558 ↑ 2.5 166 1

Recursive Union (cost=0.28..234.70 rows=421 width=16) (actual time=0.027..0.558 rows=166 loops=1)

  • Buffers: shared hit=361 read=1
4. 0.027 0.027 ↑ 1.0 1 1

Index Only Scan using product_group_pkey on public.product_group product_group_1 (cost=0.28..1.30 rows=1 width=16) (actual time=0.025..0.027 rows=1 loops=1)

  • Output: product_group_1.id, product_group_1.id
  • Index Cond: (product_group_1.id = 68)
  • Heap Fetches: 0
  • Buffers: shared hit=3
5. 0.239 0.445 ↑ 1.3 33 5

Nested Loop (cost=0.28..22.50 rows=42 width=16) (actual time=0.037..0.089 rows=33 loops=5)

  • Output: children.id, parent.parent_id
  • Buffers: shared hit=358 read=1
6. 0.040 0.040 ↓ 3.3 33 5

WorkTable Scan on t parent (cost=0.00..0.20 rows=10 width=16) (actual time=0.000..0.008 rows=33 loops=5)

  • Output: parent.id, parent.parent_id
7. 0.166 0.166 ↑ 4.0 1 166

Index Scan using idx_product_group_parent_id on public.product_group children (cost=0.28..2.19 rows=4 width=16) (actual time=0.001..0.001 rows=1 loops=166)

  • Output: children.id, children.organization_id, children.name, children.created_on, children.updated_on, children.deleted_on, children.parent_id, children.brand_id
  • Index Cond: (children.parent_id = parent.id)
  • Buffers: shared hit=358 read=1
8. 341.986 251,571.560 ↑ 1.0 100,000 1

Sort (cost=11,854,223.43..11,854,473.43 rows=100,000 width=346) (actual time=251,537.605..251,571.560 rows=100,000 loops=1)

  • Output: anon_1.rollup_date, brand.id, channel.id, product_group.id, config.id, anon_1.product_count, anon_1.product_count_count, anon_1.product_count_total, anon_1.promotion_count_count, anon_1.promotion_count, anon_1.promotion_count_total, brand.created_on, brand.updated_on, brand.deleted_on, brand.parent_id, brand.name, brand.normalized_name, channel.name, channel.icon_url, channel.domain, channel.country, channel.gazaro_id, product_group.created_on, product_group.updated_on, product_group.deleted_on, product_group.organization_id, product_group.parent_id, product_group.name, product_group.brand_id, config.created_on, config.updated_on, config.deleted_on, config.name, config.organization_id, config.sort_position, config.min, config.max, config.config_type, config.is_relevant, config.is_leading, config.exclude_3p, config.exclude_sponsored, config.only_sponsored, config.only_headline_ads, config.description, config.badge_type, config.use_matched_brand
  • Sort Key: anon_1.rollup_date DESC, channel.id, brand.id, config.id, product_group.id
  • Sort Method: external merge Disk: 30,808kB
  • Buffers: shared hit=850,488 read=866,084, temp read=3,851 written=3,852
9. 77.725 251,229.574 ↑ 1.0 100,000 1

Hash Join (cost=11,825,818.23..11,845,918.61 rows=100,000 width=346) (actual time=250,292.093..251,229.574 rows=100,000 loops=1)

  • Output: anon_1.rollup_date, brand.id, channel.id, product_group.id, config.id, anon_1.product_count, anon_1.product_count_count, anon_1.product_count_total, anon_1.promotion_count_count, anon_1.promotion_count, anon_1.promotion_count_total, brand.created_on, brand.updated_on, brand.deleted_on, brand.parent_id, brand.name, brand.normalized_name, channel.name, channel.icon_url, channel.domain, channel.country, channel.gazaro_id, product_group.created_on, product_group.updated_on, product_group.deleted_on, product_group.organization_id, product_group.parent_id, product_group.name, product_group.brand_id, config.created_on, config.updated_on, config.deleted_on, config.name, config.organization_id, config.sort_position, config.min, config.max, config.config_type, config.is_relevant, config.is_leading, config.exclude_3p, config.exclude_sponsored, config.only_sponsored, config.only_headline_ads, config.description, config.badge_type, config.use_matched_brand
  • Inner Unique: true
  • Hash Cond: (anon_1.config_id = config.id)
  • Buffers: shared hit=850,482 read=866,084
10. 65.515 251,151.561 ↑ 1.0 100,000 1

Hash Join (cost=11,825,807.13..11,845,640.90 rows=100,000 width=237) (actual time=250,291.794..251,151.561 rows=100,000 loops=1)

  • Output: anon_1.rollup_date, anon_1.product_count, anon_1.product_count_count, anon_1.product_count_total, anon_1.promotion_count_count, anon_1.promotion_count, anon_1.promotion_count_total, anon_1.config_id, brand.created_on, brand.updated_on, brand.deleted_on, brand.id, brand.parent_id, brand.name, brand.normalized_name, channel.id, channel.name, channel.icon_url, channel.domain, channel.country, channel.gazaro_id, product_group.created_on, product_group.updated_on, product_group.deleted_on, product_group.id, product_group.organization_id, product_group.parent_id, product_group.name, product_group.brand_id
  • Inner Unique: true
  • Hash Cond: (anon_1.product_group_id = product_group.id)
  • Buffers: shared hit=850,477 read=866,084
11. 62.397 251,079.551 ↑ 1.0 100,000 1

Hash Join (cost=11,825,764.92..11,845,335.16 rows=100,000 width=166) (actual time=250,285.253..251,079.551 rows=100,000 loops=1)

  • Output: anon_1.rollup_date, anon_1.product_count, anon_1.product_count_count, anon_1.product_count_total, anon_1.promotion_count_count, anon_1.promotion_count, anon_1.promotion_count_total, anon_1.product_group_id, anon_1.config_id, brand.created_on, brand.updated_on, brand.deleted_on, brand.id, brand.parent_id, brand.name, brand.normalized_name, channel.id, channel.name, channel.icon_url, channel.domain, channel.country, channel.gazaro_id
  • Inner Unique: true
  • Hash Cond: (anon_1.channel_id = channel.id)
  • Buffers: shared hit=850,467 read=866,076
12. 207.535 251,016.668 ↑ 1.0 100,000 1

Merge Join (cost=11,825,744.24..11,845,050.16 rows=100,000 width=105) (actual time=250,284.753..251,016.668 rows=100,000 loops=1)

  • Output: anon_1.rollup_date, anon_1.product_count, anon_1.product_count_count, anon_1.product_count_total, anon_1.promotion_count_count, anon_1.promotion_count, anon_1.promotion_count_total, anon_1.channel_id, anon_1.product_group_id, anon_1.config_id, brand.created_on, brand.updated_on, brand.deleted_on, brand.id, brand.parent_id, brand.name, brand.normalized_name
  • Merge Cond: (brand.id = anon_1.brand_id)
  • Buffers: shared hit=850,460 read=866,076
13. 497.317 497.317 ↑ 1.0 582,337 1

Index Scan using brand_pkey on public.brand (cost=0.42..16,396.16 rows=587,249 width=65) (actual time=0.010..497.317 rows=582,337 loops=1)

  • Output: brand.id, brand.parent_id, brand.name, brand.normalized_name, brand.created_on, brand.updated_on, brand.deleted_on
  • Buffers: shared hit=497,463 read=5,890
14. 70.840 250,311.816 ↑ 1.0 100,000 1

Sort (cost=11,825,743.79..11,825,993.79 rows=100,000 width=44) (actual time=250,284.112..250,311.816 rows=100,000 loops=1)

  • Output: anon_1.rollup_date, anon_1.product_count, anon_1.product_count_count, anon_1.product_count_total, anon_1.promotion_count_count, anon_1.promotion_count, anon_1.promotion_count_total, anon_1.brand_id, anon_1.channel_id, anon_1.product_group_id, anon_1.config_id
  • Sort Key: anon_1.brand_id
  • Sort Method: quicksort Memory: 10,885kB
  • Buffers: shared hit=352,997 read=860,186
15. 56.353 250,240.976 ↑ 1.0 100,000 1

Subquery Scan on anon_1 (cost=11,816,188.97..11,817,438.97 rows=100,000 width=44) (actual time=250,116.517..250,240.976 rows=100,000 loops=1)

  • Output: anon_1.rollup_date, anon_1.product_count, anon_1.product_count_count, anon_1.product_count_total, anon_1.promotion_count_count, anon_1.promotion_count, anon_1.promotion_count_total, anon_1.brand_id, anon_1.channel_id, anon_1.product_group_id, anon_1.config_id
  • Buffers: shared hit=352,990 read=860,186
16. 37.645 250,184.623 ↑ 1.0 100,000 1

Limit (cost=11,816,188.97..11,816,438.97 rows=100,000 width=56) (actual time=250,116.515..250,184.623 rows=100,000 loops=1)

  • Output: organization_placement_rollup_day_1.brand_id, organization_placement_rollup_day_1.channel_id, organization_placement_rollup_day_1.config_id, NULL::integer, organization_placement_rollup_day_1.rollup_date, organization_placement_rollup_day_1.product_count, organization_placement_rollup_day_1.product_count_count, organization_placement_rollup_day_1.product_count_total, organization_placement_rollup_day_1.promotion_count, organization_placement_rollup_day_1.promotion_count_total, organization_placement_rollup_day_1.promotion_count_count, 68, organization_placement_rollup_day_1.randomcolumn
  • Buffers: shared hit=352,990 read=860,186
17. 8,775.394 250,146.978 ↑ 43.8 100,000 1

Sort (cost=11,816,188.97..11,827,133.48 rows=4,377,805 width=56) (actual time=250,116.512..250,146.978 rows=100,000 loops=1)

  • Output: organization_placement_rollup_day_1.brand_id, organization_placement_rollup_day_1.channel_id, organization_placement_rollup_day_1.config_id, NULL::integer, organization_placement_rollup_day_1.rollup_date, organization_placement_rollup_day_1.product_count, organization_placement_rollup_day_1.product_count_count, organization_placement_rollup_day_1.product_count_total, organization_placement_rollup_day_1.promotion_count, organization_placement_rollup_day_1.promotion_count_total, organization_placement_rollup_day_1.promotion_count_count, 68, organization_placement_rollup_day_1.randomcolumn
  • Sort Key: organization_placement_rollup_day_1.randomcolumn
  • Sort Method: top-N heapsort Memory: 20,207kB
  • Buffers: shared hit=352,990 read=860,186
18. 13,974.391 241,371.584 ↓ 4.5 19,486,670 1

Hash Join (cost=2,671,604.28..11,430,731.11 rows=4,377,805 width=56) (actual time=73,082.400..241,371.584 rows=19,486,670 loops=1)

  • Output: organization_placement_rollup_day_1.brand_id, organization_placement_rollup_day_1.channel_id, organization_placement_rollup_day_1.config_id, NULL::integer, organization_placement_rollup_day_1.rollup_date, organization_placement_rollup_day_1.product_count, organization_placement_rollup_day_1.product_count_count, organization_placement_rollup_day_1.product_count_total, organization_placement_rollup_day_1.promotion_count, organization_placement_rollup_day_1.promotion_count_total, organization_placement_rollup_day_1.promotion_count_count, 68, organization_placement_rollup_day_1.randomcolumn
  • Inner Unique: true
  • Hash Cond: (organization_placement_rollup_day_1.search_term_id = anon_2.search_term_id)
  • Buffers: shared hit=352,987 read=860,186
19. 154,443.723 227,364.089 ↓ 3.1 25,232,664 1

Bitmap Heap Scan on public.organization_placement_rollup_day organization_placement_rollup_day_1 (cost=2,670,657.85..11,408,379.10 rows=8,147,346 width=52) (actual time=73,049.275..227,364.089 rows=25,232,664 loops=1)

  • Output: organization_placement_rollup_day_1.id, organization_placement_rollup_day_1.rollup_date, organization_placement_rollup_day_1.organization_id, organization_placement_rollup_day_1.channel_id, organization_placement_rollup_day_1.brand_id, organization_placement_rollup_day_1.config_id, organization_placement_rollup_day_1.search_term_id, organization_placement_rollup_day_1.promotion_count, organization_placement_rollup_day_1.promotion_count_count, organization_placement_rollup_day_1.promotion_count_total, organization_placement_rollup_day_1.promotion_avg, organization_placement_rollup_day_1.product_count, organization_placement_rollup_day_1.product_count_count, organization_placement_rollup_day_1.product_count_total, organization_placement_rollup_day_1.product_avg, organization_placement_rollup_day_1.search_term_result_set_id, organization_placement_rollup_day_1.postal_code_id, organization_placement_rollup_day_1.updated_on, organization_placement_rollup_day_1.randomcolumn
  • Recheck Cond: ((organization_placement_rollup_day_1.organization_id = 32) AND (organization_placement_rollup_day_1.rollup_date >= '2019-10-31'::date) AND (organization_placement_rollup_day_1.rollup_date <= '2019-12-31'::date) AND (organization_placement_rollup_day_1.config_id = ANY ('{3,1,195,8,85,86,92,93}'::integer[])))
  • Filter: (organization_placement_rollup_day_1.channel_id = ANY ('{6,7007,2494,7168,7005,7019,7013,7370,7010,7170,7001,7011,2496,6984,7169,6988,2498,6991}'::integer[]))
  • Heap Blocks: exact=497,676
  • Buffers: shared hit=352,472 read=860,137
20. 51.110 72,920.366 ↓ 0.0 0 1

BitmapAnd (cost=2,670,657.85..2,670,657.85 rows=13,927,405 width=0) (actual time=72,920.366..72,920.366 rows=0 loops=1)

  • Buffers: shared hit=213,648 read=501,285
21. 1,973.980 1,973.980 ↑ 1.6 25,786,078 1

Bitmap Index Scan on organization_placement_rollup_day_idx (cost=0.00..744,953.15 rows=40,991,086 width=0) (actual time=1,973.979..1,973.980 rows=25,786,078 loops=1)

  • Index Cond: ((organization_placement_rollup_day_1.organization_id = 32) AND (organization_placement_rollup_day_1.rollup_date >= '2019-10-31'::date) AND (organization_placement_rollup_day_1.rollup_date <= '2019-12-31'::date))
  • Buffers: shared hit=213,635 read=2
22. 70,895.276 70,895.276 ↑ 1.0 176,115,317 1

Bitmap Index Scan on idx_organization_placement_rollup_day_config_id (cost=0.00..1,921,630.78 rows=181,021,763 width=0) (actual time=70,895.276..70,895.276 rows=176,115,317 loops=1)

  • Index Cond: (organization_placement_rollup_day_1.config_id = ANY ('{3,1,195,8,85,86,92,93}'::integer[]))
  • Buffers: shared hit=13 read=501,283
23. 0.364 33.104 ↑ 1.9 1,332 1

Hash (cost=914.04..914.04 rows=2,591 width=8) (actual time=33.104..33.104 rows=1,332 loops=1)

  • Output: anon_2.search_term_id
  • Buckets: 4,096 Batches: 1 Memory Usage: 85kB
  • Buffers: shared hit=515 read=49
24. 0.556 32.740 ↑ 1.9 1,332 1

Subquery Scan on anon_2 (cost=875.17..914.04 rows=2,591 width=8) (actual time=29.809..32.740 rows=1,332 loops=1)

  • Output: anon_2.search_term_id
  • Buffers: shared hit=515 read=49
25. 1.417 32.184 ↑ 1.9 1,332 1

Unique (cost=875.17..888.13 rows=2,591 width=24) (actual time=29.808..32.184 rows=1,332 loops=1)

  • Output: NULL::bigint, organization_search_terms.search_term_id, NULL::bigint
  • Buffers: shared hit=515 read=49
26. 2.921 30.767 ↓ 1.9 4,913 1

Sort (cost=875.17..881.65 rows=2,591 width=24) (actual time=29.807..30.767 rows=4,913 loops=1)

  • Output: NULL::bigint, organization_search_terms.search_term_id, NULL::bigint
  • Sort Key: organization_search_terms.search_term_id
  • Sort Method: quicksort Memory: 423kB
  • Buffers: shared hit=515 read=49
27. 1.411 27.846 ↓ 1.9 4,913 1

Hash Join (cost=535.01..728.27 rows=2,591 width=24) (actual time=25.883..27.846 rows=4,913 loops=1)

  • Output: NULL::bigint, organization_search_terms.search_term_id, NULL::bigint
  • Hash Cond: (t.id = product_groups_organization_search_terms.product_group_id)
  • Buffers: shared hit=515 read=49
28. 0.653 0.653 ↑ 2.5 166 1

CTE Scan on t (cost=0.00..8.42 rows=421 width=8) (actual time=0.028..0.653 rows=166 loops=1)

  • Output: t.id, t.parent_id
  • Buffers: shared hit=361 read=1
29. 3.995 25.782 ↓ 2.3 12,875 1

Hash (cost=466.33..466.33 rows=5,495 width=16) (actual time=25.782..25.782 rows=12,875 loops=1)

  • Output: product_groups_organization_search_terms.product_group_id, organization_search_terms.search_term_id
  • Buckets: 16,384 (originally 8192) Batches: 1 (originally 1) Memory Usage: 732kB
  • Buffers: shared hit=154 read=48
30. 8.249 21.787 ↓ 2.3 12,875 1

Hash Join (cost=261.98..466.33 rows=5,495 width=16) (actual time=9.980..21.787 rows=12,875 loops=1)

  • Output: product_groups_organization_search_terms.product_group_id, organization_search_terms.search_term_id
  • Inner Unique: true
  • Hash Cond: (product_groups_organization_search_terms.organization_search_terms_id = organization_search_terms.id)
  • Buffers: shared hit=154 read=48
31. 3.625 3.625 ↓ 2.2 16,446 1

Seq Scan on public.product_groups_organization_search_terms (cost=0.00..184.31 rows=7,631 width=16) (actual time=0.005..3.625 rows=16,446 loops=1)

  • Output: product_groups_organization_search_terms.product_group_id, product_groups_organization_search_terms.organization_search_terms_id
  • Buffers: shared hit=108
32. 2.097 9.913 ↓ 1.0 6,669 1

Hash (cost=182.40..182.40 rows=6,366 width=16) (actual time=9.913..9.913 rows=6,669 loops=1)

  • Output: organization_search_terms.search_term_id, organization_search_terms.id
  • Buckets: 8,192 Batches: 1 Memory Usage: 377kB
  • Buffers: shared hit=46 read=48
33. 7.816 7.816 ↓ 1.0 6,669 1

Seq Scan on public.organization_search_terms (cost=0.00..182.40 rows=6,366 width=16) (actual time=0.004..7.816 rows=6,669 loops=1)

  • Output: organization_search_terms.search_term_id, organization_search_terms.id
  • Filter: (organization_search_terms.deleted_on IS NULL)
  • Rows Removed by Filter: 2,189
  • Buffers: shared hit=46 read=48
34. 0.283 0.486 ↑ 1.0 608 1

Hash (cost=13.08..13.08 rows=608 width=65) (actual time=0.486..0.486 rows=608 loops=1)

  • Output: channel.id, channel.name, channel.icon_url, channel.domain, channel.country, channel.gazaro_id
  • Buckets: 1,024 Batches: 1 Memory Usage: 58kB
  • Buffers: shared hit=7
35. 0.203 0.203 ↑ 1.0 608 1

Seq Scan on public.channel (cost=0.00..13.08 rows=608 width=65) (actual time=0.003..0.203 rows=608 loops=1)

  • Output: channel.id, channel.name, channel.icon_url, channel.domain, channel.country, channel.gazaro_id
  • Buffers: shared hit=7
36. 0.477 6.495 ↑ 1.0 1,076 1

Hash (cost=28.76..28.76 rows=1,076 width=75) (actual time=6.495..6.495 rows=1,076 loops=1)

  • Output: product_group.created_on, product_group.updated_on, product_group.deleted_on, product_group.id, product_group.organization_id, product_group.parent_id, product_group.name, product_group.brand_id
  • Buckets: 2,048 Batches: 1 Memory Usage: 122kB
  • Buffers: shared hit=10 read=8
37. 6.018 6.018 ↑ 1.0 1,076 1

Seq Scan on public.product_group (cost=0.00..28.76 rows=1,076 width=75) (actual time=0.004..6.018 rows=1,076 loops=1)

  • Output: product_group.created_on, product_group.updated_on, product_group.deleted_on, product_group.id, product_group.organization_id, product_group.parent_id, product_group.name, product_group.brand_id
  • Buffers: shared hit=10 read=8
38. 0.156 0.288 ↑ 1.0 271 1

Hash (cost=7.71..7.71 rows=271 width=113) (actual time=0.288..0.288 rows=271 loops=1)

  • Output: config.created_on, config.updated_on, config.deleted_on, config.id, config.name, config.organization_id, config.sort_position, config.min, config.max, config.config_type, config.is_relevant, config.is_leading, config.exclude_3p, config.exclude_sponsored, config.only_sponsored, config.only_headline_ads, config.description, config.badge_type, config.use_matched_brand
  • Buckets: 1,024 Batches: 1 Memory Usage: 42kB
  • Buffers: shared hit=5
39. 0.132 0.132 ↑ 1.0 271 1

Seq Scan on public.organization_placement_config config (cost=0.00..7.71 rows=271 width=113) (actual time=0.005..0.132 rows=271 loops=1)

  • Output: config.created_on, config.updated_on, config.deleted_on, config.id, config.name, config.organization_id, config.sort_position, config.min, config.max, config.config_type, config.is_relevant, config.is_leading, config.exclude_3p, config.exclude_sponsored, config.only_sponsored, config.only_headline_ads, config.description, config.badge_type, config.use_matched_brand
  • Buffers: shared hit=5
Planning time : 77.215 ms
Execution time : 251,680.537 ms