explain.depesz.com

PostgreSQL's explain analyze made readable

Result: KK21

Settings
# exclusive inclusive rows x rows loops node
1. 57.269 192,836.520 ↑ 19.0 5,256 1

GroupAggregate (cost=11,003,910.51..11,012,410.51 rows=100,000 width=401) (actual time=192,744.278..192,836.520 rows=5,256 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=335014 read=316212, temp read=3870 written=3870
2.          

CTE t

3. 0.093 0.579 ↑ 2.5 166 1

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

  • Buffers: shared hit=362
4. 0.041 0.041 ↑ 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.037..0.041 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.034..0.089 rows=33 loops=5)

  • Output: children.id, parent.parent_id
  • Buffers: shared hit=359
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.001..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=359
8. 352.021 192,779.251 ↑ 1.0 100,000 1

Sort (cost=11,003,675.81..11,003,925.81 rows=100,000 width=345) (actual time=192,744.241..192,779.251 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 sort Disk: 30960kB
  • Buffers: shared hit=335014 read=316212, temp read=3870 written=3870
9. 76.679 192,427.230 ↑ 1.0 100,000 1

Hash Join (cost=10,975,290.81..10,995,370.99 rows=100,000 width=345) (actual time=191,849.874..192,427.230 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=335008 read=316212
10. 64.076 192,350.007 ↑ 1.0 100,000 1

Hash Join (cost=10,975,279.71..10,995,093.29 rows=100,000 width=236) (actual time=191,849.312..192,350.007 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=335003 read=316212
11. 62.662 192,284.309 ↑ 1.0 100,000 1

Hash Join (cost=10,975,237.50..10,994,787.55 rows=100,000 width=165) (actual time=191,847.672..192,284.309 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=334985 read=316212
12. 152.729 192,220.818 ↑ 1.0 100,000 1

Merge Join (cost=10,975,216.82..10,994,502.54 rows=100,000 width=105) (actual time=191,846.826..192,220.818 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=334978 read=316212
13. 196.796 196.796 ↑ 1.5 394,543 1

Index Scan using brand_pkey on public.brand (cost=0.42..16,396.16 rows=587,249 width=65) (actual time=0.018..196.796 rows=394,543 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=334145
14. 67.065 191,871.293 ↑ 1.0 100,000 1

Sort (cost=10,975,216.38..10,975,466.38 rows=100,000 width=44) (actual time=191,846.764..191,871.293 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: 10885kB
  • Buffers: shared hit=833 read=316212
15. 54.131 191,804.228 ↑ 1.0 100,000 1

Subquery Scan on anon_1 (cost=10,965,661.56..10,966,911.56 rows=100,000 width=44) (actual time=191,687.159..191,804.228 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=826 read=316212
16. 40.776 191,750.097 ↑ 1.0 100,000 1

Limit (cost=10,965,661.56..10,965,911.56 rows=100,000 width=56) (actual time=191,687.156..191,750.097 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=826 read=316212
17. 4,662.796 191,709.321 ↑ 52.2 100,000 1

Sort (cost=10,965,661.56..10,978,705.66 rows=5,217,643 width=56) (actual time=191,687.154..191,709.321 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: 20207kB
  • Buffers: shared hit=826 read=316212
18. 8,974.635 187,046.525 ↓ 2.4 12,267,315 1

Hash Join (cost=255,826.10..10,506,257.47 rows=5,217,643 width=56) (actual time=2,060.695..187,046.525 rows=12,267,315 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=823 read=316212
19. 176,079.413 178,040.656 ↓ 1.7 16,190,435 1

Bitmap Heap Scan on public.organization_placement_rollup_day organization_placement_rollup_day_1 (cost=254,879.68..10,480,132.04 rows=9,583,467 width=52) (actual time=2,022.473..178,040.656 rows=16,190,435 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-03-31'::date) AND (organization_placement_rollup_day_1.rollup_date <= '2019-05-31'::date))
  • 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=272231
  • Buffers: shared hit=259 read=316212
20. 1,961.243 1,961.243 ↑ 1.0 16,190,435 1

Bitmap Index Scan on idx_organization_placement_rollup_day_organization_id_and_rollu (cost=0.00..252,483.81 rows=16,565,059 width=0) (actual time=1,961.242..1,961.243 rows=16,190,435 loops=1)

  • Index Cond: ((organization_placement_rollup_day_1.organization_id = 32) AND (organization_placement_rollup_day_1.rollup_date >= '2019-03-31'::date) AND (organization_placement_rollup_day_1.rollup_date <= '2019-05-31'::date))
  • Buffers: shared hit=1 read=44239
21. 0.386 31.234 ↑ 1.9 1,332 1

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

  • Output: anon_2.search_term_id
  • Buckets: 4096 Batches: 1 Memory Usage: 85kB
  • Buffers: shared hit=564
22. 0.563 30.848 ↑ 1.9 1,332 1

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

  • Output: anon_2.search_term_id
  • Buffers: shared hit=564
23. 1.404 30.285 ↑ 1.9 1,332 1

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

  • Output: NULL::bigint, organization_search_terms.search_term_id, NULL::bigint
  • Buffers: shared hit=564
24. 2.940 28.881 ↓ 1.9 4,913 1

Sort (cost=875.17..881.65 rows=2,591 width=24) (actual time=27.927..28.881 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=564
25. 1.426 25.941 ↓ 1.9 4,913 1

Hash Join (cost=535.01..728.27 rows=2,591 width=24) (actual time=23.960..25.941 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=564
26. 0.676 0.676 ↑ 2.5 166 1

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

  • Output: t.id, t.parent_id
  • Buffers: shared hit=362
27. 4.472 23.839 ↓ 2.3 12,875 1

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

  • Output: product_groups_organization_search_terms.product_group_id, organization_search_terms.search_term_id
  • Buckets: 16384 (originally 8192) Batches: 1 (originally 1) Memory Usage: 732kB
  • Buffers: shared hit=202
28. 9.209 19.367 ↓ 2.3 12,875 1

Hash Join (cost=261.98..466.33 rows=5,495 width=16) (actual time=6.063..19.367 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=202
29. 4.147 4.147 ↓ 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.006..4.147 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
30. 2.691 6.011 ↓ 1.0 6,669 1

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

  • Output: organization_search_terms.search_term_id, organization_search_terms.id
  • Buckets: 8192 Batches: 1 Memory Usage: 377kB
  • Buffers: shared hit=94
31. 3.320 3.320 ↓ 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.006..3.320 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: 2189
  • Buffers: shared hit=94
32. 0.431 0.829 ↑ 1.0 608 1

Hash (cost=13.08..13.08 rows=608 width=64) (actual time=0.828..0.829 rows=608 loops=1)

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

Seq Scan on public.channel (cost=0.00..13.08 rows=608 width=64) (actual time=0.005..0.398 rows=608 loops=1)

  • Output: channel.id, channel.name, channel.icon_url, channel.domain, channel.country, channel.gazaro_id
  • Buffers: shared hit=7
34. 0.901 1.622 ↑ 1.0 1,076 1

Hash (cost=28.76..28.76 rows=1,076 width=75) (actual time=1.622..1.622 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: 2048 Batches: 1 Memory Usage: 122kB
  • Buffers: shared hit=18
35. 0.721 0.721 ↑ 1.0 1,076 1

Seq Scan on public.product_group (cost=0.00..28.76 rows=1,076 width=75) (actual time=0.005..0.721 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=18
36. 0.283 0.544 ↑ 1.0 271 1

Hash (cost=7.71..7.71 rows=271 width=113) (actual time=0.544..0.544 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: 1024 Batches: 1 Memory Usage: 42kB
  • Buffers: shared hit=5
37. 0.261 0.261 ↑ 1.0 271 1

Seq Scan on public.organization_placement_config config (cost=0.00..7.71 rows=271 width=113) (actual time=0.006..0.261 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 : 56.513 ms
Execution time : 192,846.074 ms