explain-analyze.info | query-plan-1119

Time	Node
Node (ms)	Percent
22501.478	100.00%	¶	`HashAggregate (cost=71290.4..71368.12 rows=3886 width=25) (actual time=22501.478..22501.478 rows=0 loops=1) Filter: (sum(CASE WHEN (sd.band_id = 352433) THEN 0 ELSE 1 END) = count(sd.song_id))`
15412.030	68.49%	¶	`-> Hash Join (cost=42706.73..69049.94 rows=179237 width=25) (actual time=10312.224..15412.030 rows=8074752 loops=1) Hash Cond: (pt.playlist_id = pd.playlist_id)`
695.092	3.09%	¶	`-> Hash Join (cost=6623.68..32081.61 rows=37045 width=12) (actual time=47.078..695.092 rows=144638 loops=1) Hash Cond: (pt.song_id = sd.song_id)`
272.082	1.21%	¶	`-> Seq Scan on playlist_tracks pt (cost=0.0..16855.24 rows=823224 width=8) (actual time=0.019..272.082 rows=824349 loops=1)`
16.280	0.07%	¶	`-> Hash (cost=6468.18..6468.18 rows=12440 width=8) (actual time=16.280..16.280 rows=11650 loops=1)`
10.870	0.05%	¶	`-> Bitmap Heap Scan on song_data sd (cost=208.68..6468.18 rows=12440 width=8) (actual time=1.823..10.870 rows=11650 loops=1) Recheck Cond: (band_id = 352433)`
1.617	0.01%	¶	`-> Bitmap Index Scan on song_data_band_id (cost=0.0..205.57 rows=12440 width=0) (actual time=1.617..1.617 rows=11651 loops=1) Index Cond: (band_id = 352433)`
10259.370	45.59%	¶	`-> Hash (cost=36011.5..36011.5 rows=5723 width=25) (actual time=10259.370..10259.370 rows=16013 loops=1)`
10250.476	45.55%	¶	`-> Merge Join (cost=34531.92..36011.5 rows=5723 width=25) (actual time=10210.144..10250.476 rows=16013 loops=1) Merge Cond: (s.store_id = a.store_id)`
18.490	0.08%	¶	`-> Index Scan using store_pro_type_pkey on store_pro_type s (cost=0.0..1364.53 rows=9051 width=8) (actual time=0.028..18.490 rows=8544 loops=1) Filter: ((end_date IS NULL) AND (pro_type = ANY ('{2,6}'::integer[])))`
10215.578	45.40%	¶	`-> Sort (cost=34531.16..34549.52 rows=7344 width=21) (actual time=10210.108..10215.578 rows=21769 loops=1) Sort Key: a.store_id Sort Method: quicksort Memory: 2067kB`
10198.013	45.32%	¶	`-> Hash Join (cost=26949.57..34059.58 rows=7344 width=21) (actual time=10168.732..10198.013 rows=22102 loops=1) Hash Cond: (a.mbox = z.machine_serial)`
0.000	0.00%	¶	`-> Index Scan using archive_billing_data_billing_month on archive_billing_data a (never executed) Index Cond: (billing_month = '2008-07-01 00:00:00'::timestamp without time zone)`
10168.693	45.19%	¶	`-> Hash (cost=26857.14..26857.14 rows=7394 width=17) (actual time=10168.693..10168.693 rows=26790 loops=1)`
10154.256	45.13%	¶	`-> Hash Join (cost=25436.46..26857.14 rows=7394 width=17) (actual time=10086.336..10154.256 rows=26790 loops=1) Hash Cond: (COALESCE(ma.msched_id, g.msched_id) = e.sched_id)`
53.058	0.24%	¶	`-> Hash Left Join (cost=189.18..1358.22 rows=23696 width=17) (actual time=7.442..53.058 rows=23684 loops=1) Hash Cond: (z.group_id = g.group_id)`
34.361	0.15%	¶	`-> Hash Left Join (cost=154.82..998.04 rows=23696 width=17) (actual time=6.415..34.361 rows=23684 loops=1) Hash Cond: (z.zone_id = ma.zone_id)`
7.978	0.04%	¶	`-> Seq Scan on retail_zones z (cost=0.0..579.96 rows=23696 width=17) (actual time=0.007..7.978 rows=23684 loops=1)`
6.396	0.03%	¶	`-> Hash (cost=84.92..84.92 rows=5592 width=8) (actual time=6.396..6.396 rows=5591 loops=1)`
0.000	0.00%	¶	`-> Seq Scan on msched_assignments ma (never executed)`
1.011	0.00%	¶	`-> Hash (cost=20.27..20.27 rows=1127 width=8) (actual time=1.011..1.011 rows=1128 loops=1)`
0.487	0.00%	¶	`-> Seq Scan on retail_groups g (cost=0.0..20.27 rows=1127 width=8) (actual time=0.007..0.487 rows=1128 loops=1)`
10078.873	44.79%	¶	`-> Hash (cost=25232.59..25232.59 rows=1176 width=12) (actual time=10078.873..10078.873 rows=2172 loops=1)`
10077.534	44.79%	¶	`-> Nested Loop (cost=21113.03..25232.59 rows=1176 width=12) (actual time=7801.996..10077.534 rows=2172 loops=1)`
9997.874	44.43%	¶	`-> Hash Join (cost=21113.03..22768.32 rows=5698 width=16) (actual time=7799.107..9997.874 rows=18862 loops=1) Hash Cond: (e.sched_id = sc.sched_id)`
9973.604	44.32%	¶	`-> Nested Loop (cost=20859.16..22366.54 rows=10392 width=16) (actual time=7793.413..9973.604 rows=18876 loops=1)`
0.000	0.00%	¶	`-> Merge Join (never executed) Merge Cond: ((pd.playlist_name = p.playlist_name) AND (ppn.value = p.cust_id))`
0.000	0.00%	¶	`-> Sort (never executed) Sort Key: pd.playlist_name, ppn.value Sort Method: quicksort Memory: 8075kB`
0.000	0.00%	¶	`-> Hash Join (never executed) Hash Cond: (ppn.playlist_id = pd.playlist_id)`
0.000	0.00%	¶	`-> Seq Scan on playlist_properties_num ppn (never executed) Filter: (prop_no = 10)`
0.000	0.00%	¶	`-> Hash (never executed)`
0.000	0.00%	¶	`-> Seq Scan on playlist_data pd (never executed)`
0.000	0.00%	¶	`-> Sort (never executed) Sort Key: p.playlist_name, p.cust_id Sort Method: quicksort Memory: 7421kB`
0.000	0.00%	¶	`-> Seq Scan on playlist_data p (never executed)`
0.000	0.00%	¶	`-> Index Scan using events_playlist_idx on events e (never executed) Index Cond: (e.playlist_id = (p.playlist_id)::text)`
5.681	0.03%	¶	`-> Hash (cost=189.11..189.11 rows=5181 width=4) (actual time=5.681..5.681 rows=5166 loops=1)`
0.000	0.00%	¶	`-> Seq Scan on schedules sc (never executed) Filter: (schedule_type = 'm'::text)`
0.000	0.00%	¶	`-> Index Scan using event_date_config_pkey on event_date_config d (never executed) Index Cond: (d.date_config_id = e.date_config_id) Filter: (((d.end_year > 2008) AND (d.start_year < 2008)) OR ((d.end_year = 2008) AND (d.end_month >= 7)) OR ((d.sta rt_year = 2008) AND (d.start_month <= 7)) OR ((COALESCE(d.start_year, 0) = 0) AND ((d.start_month <= 7) OR (d.end_month >= 7))))`

22501.478

100.00%

 HashAggregate (cost=71290.4..71368.12 rows=3886 width=25) (actual time=22501.478..22501.478 rows=0 loops=1)
   Filter: (sum(CASE WHEN (sd.band_id = 352433) THEN 0 ELSE 1 END) = count(sd.song_id))

15412.030

68.49%

¶

   -> Hash Join (cost=42706.73..69049.94 rows=179237 width=25) (actual time=10312.224..15412.030 rows=8074752 loops=1)
         Hash Cond: (pt.playlist_id = pd.playlist_id)

695.092

3.09%

¶

         -> Hash Join (cost=6623.68..32081.61 rows=37045 width=12) (actual time=47.078..695.092 rows=144638 loops=1)
               Hash Cond: (pt.song_id = sd.song_id)

272.082

1.21%

¶

               -> Seq Scan on playlist_tracks pt (cost=0.0..16855.24 rows=823224 width=8) (actual time=0.019..272.082 rows=824349 loops=1)

16.280

0.07%

¶

               -> Hash (cost=6468.18..6468.18 rows=12440 width=8) (actual time=16.280..16.280 rows=11650 loops=1)

10.870

0.05%

¶

                     -> Bitmap Heap Scan on song_data sd (cost=208.68..6468.18 rows=12440 width=8) (actual time=1.823..10.870 rows=11650 loops=1)
                           Recheck Cond: (band_id = 352433)

1.617

0.01%

¶

                           -> Bitmap Index Scan on song_data_band_id (cost=0.0..205.57 rows=12440 width=0) (actual time=1.617..1.617 rows=11651 loops=1)
                                 Index Cond: (band_id = 352433)

10259.370

45.59%

¶

         -> Hash (cost=36011.5..36011.5 rows=5723 width=25) (actual time=10259.370..10259.370 rows=16013 loops=1)

10250.476

45.55%

¶

               -> Merge Join (cost=34531.92..36011.5 rows=5723 width=25) (actual time=10210.144..10250.476 rows=16013 loops=1)
                     Merge Cond: (s.store_id = a.store_id)

18.490

0.08%

¶

                     -> Index Scan using store_pro_type_pkey on store_pro_type s (cost=0.0..1364.53 rows=9051 width=8) (actual time=0.028..18.490 rows=8544 loops=1)
                           Filter: ((end_date IS NULL) AND (pro_type = ANY ('{2,6}'::integer[])))

10215.578

45.40%

¶

                     -> Sort (cost=34531.16..34549.52 rows=7344 width=21) (actual time=10210.108..10215.578 rows=21769 loops=1)
                           Sort Key: a.store_id
                           Sort Method:  quicksort  Memory: 2067kB

10198.013

45.32%

¶

                           -> Hash Join (cost=26949.57..34059.58 rows=7344 width=21) (actual time=10168.732..10198.013 rows=22102 loops=1)
                                 Hash Cond: (a.mbox = z.machine_serial)

0.000

0.00%

¶

                                 -> Index Scan using archive_billing_data_billing_month on archive_billing_data a (never executed)
                                       Index Cond: (billing_month = '2008-07-01 00:00:00'::timestamp without time zone)

10168.693

45.19%

¶

                                 -> Hash (cost=26857.14..26857.14 rows=7394 width=17) (actual time=10168.693..10168.693 rows=26790 loops=1)

10154.256

45.13%

¶

                                       -> Hash Join (cost=25436.46..26857.14 rows=7394 width=17) (actual time=10086.336..10154.256 rows=26790 loops=1)
                                             Hash Cond: (COALESCE(ma.msched_id, g.msched_id) = e.sched_id)

53.058

0.24%

¶

                                             -> Hash Left Join (cost=189.18..1358.22 rows=23696 width=17) (actual time=7.442..53.058 rows=23684 loops=1)
                                                   Hash Cond: (z.group_id = g.group_id)

34.361

0.15%

¶

                                                   -> Hash Left Join (cost=154.82..998.04 rows=23696 width=17) (actual time=6.415..34.361 rows=23684 loops=1)
                                                         Hash Cond: (z.zone_id = ma.zone_id)

7.978

0.04%

¶

                                                         -> Seq Scan on retail_zones z (cost=0.0..579.96 rows=23696 width=17) (actual time=0.007..7.978 rows=23684 loops=1)

6.396

0.03%

¶

                                                         -> Hash (cost=84.92..84.92 rows=5592 width=8) (actual time=6.396..6.396 rows=5591 loops=1)

0.000

0.00%

¶

                                                               -> Seq Scan on msched_assignments ma (never executed)

1.011

0.00%

¶

                                                   -> Hash (cost=20.27..20.27 rows=1127 width=8) (actual time=1.011..1.011 rows=1128 loops=1)

0.487

0.00%

¶

                                                         -> Seq Scan on retail_groups g (cost=0.0..20.27 rows=1127 width=8) (actual time=0.007..0.487 rows=1128 loops=1)

10078.873

44.79%

¶

                                             -> Hash (cost=25232.59..25232.59 rows=1176 width=12) (actual time=10078.873..10078.873 rows=2172 loops=1)

10077.534

44.79%

¶

                                                   -> Nested Loop (cost=21113.03..25232.59 rows=1176 width=12) (actual time=7801.996..10077.534 rows=2172 loops=1)

9997.874

44.43%

¶

                                                         -> Hash Join (cost=21113.03..22768.32 rows=5698 width=16) (actual time=7799.107..9997.874 rows=18862 loops=1)
                                                               Hash Cond: (e.sched_id = sc.sched_id)

9973.604

44.32%

¶

                                                               -> Nested Loop (cost=20859.16..22366.54 rows=10392 width=16) (actual time=7793.413..9973.604 rows=18876 loops=1)

0.000

0.00%

¶

                                                                     -> Merge Join (never executed)
                                                                           Merge Cond: ((pd.playlist_name = p.playlist_name) AND (ppn.value = p.cust_id))

0.000

0.00%

¶

                                                                           -> Sort (never executed)
                                                                                 Sort Key: pd.playlist_name, ppn.value
                                                                                 Sort Method:  quicksort  Memory: 8075kB

0.000

0.00%

¶

                                                                                 -> Hash Join (never executed)
                                                                                       Hash Cond: (ppn.playlist_id = pd.playlist_id)

0.000

0.00%

¶

                                                                                       -> Seq Scan on playlist_properties_num ppn (never executed)
                                                                                             Filter: (prop_no = 10)

0.000

0.00%

¶

                                                                                       -> Hash (never executed)

0.000

0.00%

¶

                                                                                             -> Seq Scan on playlist_data pd (never executed)

0.000

0.00%

¶

                                                                           -> Sort (never executed)
                                                                                 Sort Key: p.playlist_name, p.cust_id
                                                                                 Sort Method:  quicksort  Memory: 7421kB

0.000

0.00%

¶

                                                                                 -> Seq Scan on playlist_data p (never executed)

0.000

0.00%

¶

                                                                     -> Index Scan using events_playlist_idx on events e (never executed)
                                                                           Index Cond: (e.playlist_id = (p.playlist_id)::text)

5.681

0.03%

¶

                                                               -> Hash (cost=189.11..189.11 rows=5181 width=4) (actual time=5.681..5.681 rows=5166 loops=1)

0.000

0.00%

¶

                                                                     -> Seq Scan on schedules sc (never executed)
                                                                           Filter: (schedule_type = 'm'::text)

0.000

0.00%

¶

                                                         -> Index Scan using event_date_config_pkey on event_date_config d (never executed)
                                                               Index Cond: (d.date_config_id = e.date_config_id)
                                                               Filter: (((d.end_year > 2008) AND (d.start_year < 2008)) OR ((d.end_year = 2008) AND (d.end_month >= 7)) OR ((d.sta rt_year = 2008) AND (d.start_month <= 7)) OR ((COALESCE(d.start_year, 0) = 0) AND ((d.start_month <= 7) OR (d.end_month >= 7))))