Re: seq scan issue...

kevin kempter escribió:
> 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)
>
>
Cause you are getting all the rows so pgsql need to scan all the table...