Hi List;
I have a large tble (playback_device) with 6million rows in it. The
aff_id_tmp1 table has 600,000 rows.
I also have this query:
select distinct
tmp1.affiliate_id,
tmp1.name,
tmp1.description,
tmp1.create_dt,
tmp1.playback_device_id,
pf.segment_id
from
aff_id_tmp1 tmp1,
playback_fragment pf
where
tmp1.playback_device_id = pf.playback_device_id ;
The Primary Key for playback_device is the playback_device_id
there is also an index on playback_device_id on the aff_id_tmp1 table.
The only join condition I have is on this key pair (I've posted my
explain plan below)
- why am I still getting a seq scan ?
Thanks in advance.
============
Explain PLan
============
explain
select distinct
tmp1.affiliate_id,
tmp1.name,
tmp1.description,
tmp1.create_dt,
tmp1.playback_device_id,
pf.segment_id
from
aff_id_tmp1 tmp1,
playback_fragment pf
where
tmp1.playback_device_id = pf.playback_device_id ;
Unique (cost=2966361.56..3194555.91 rows=10104496 width=97)
-> Sort (cost=2966361.56..2998960.76 rows=13039677 width=97)
Sort Key: tmp1.affiliate_id, tmp1.name, tmp1.description,
tmp1.create_dt,
tmp1.playback_device_id, pf.segment_id
-> Hash Join (cost=23925.45..814071.14 rows=13039677
width=97)
Hash Cond: (pf.playback_device_id =
tmp1.playback_device_id)
-> Seq Scan on playback_fragment pf
(cost=0.00..464153.77 rows=130
39677 width=16)
-> Hash (cost=16031.31..16031.31 rows=631531 width=89)
-> Seq Scan on aff_id_tmp1 tmp1
(cost=0.00..16031.31 rows=63
1531 width=89)
(1068 rows)