Re: [PERFORM] seq scan issue...

[Rodrigo Gonzalez]

kevin kempter escribió:

Hi List;



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.



- 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)

tmp1.playback_device_id, pf.segment_id
         ->  Hash Join  (cost=23925.45..814071.14 rows=13039677 width=97)

39677 width=16)
               ->  Hash  (cost=16031.31..16031.31 rows=631531 width=89)

1531 width=89)
(1068 rows)

I have a large tble (playback_device) with 6million rows in it. The aff_id_tmp1 table has 600,000 rows. The only join condition I have is on this key pair (I've posted my explain plan below) Sort Key: tmp1.affiliate_id, tmp1.name, tmp1.description, tmp1.create_dt, Hash Cond: (pf.playback_device_id = tmp1.playback_device_id) -> Seq Scan on playback_fragment pf (cost=0.00..464153.77 rows=130 -> Seq Scan on aff_id_tmp1 tmp1 (cost=0.00..16031.31 rows=63

Cause you are getting all the rows so pgsql need to scan all the table...

smime.p7s
Description: S/MIME Cryptographic Signature