pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedseq scan issue...
4+ messages / 4 participants
[nested] [flat]
* seq scan issue...
@ 2008-04-17 18:24 kevin kempter <kevin@kevinkempterllc.com>
0 siblings, 3 replies; 4+ messages in thread
From: kevin kempter @ 2008-04-17 18:24 UTC (permalink / raw)
To: pgsql-performance
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)
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: seq scan issue...
@ 2008-04-17 18:30 Jeffrey Baker <jwbaker@gmail.com>
parent: kevin kempter <kevin@kevinkempterllc.com>
2 siblings, 0 replies; 4+ messages in thread
From: Jeffrey Baker @ 2008-04-17 18:30 UTC (permalink / raw)
To: kevin kempter <kevin@kevinkempterllc.com>; pgsql-performance
On Thu, Apr 17, 2008 at 11:24 AM, kevin kempter
<kevin@kevinkempterllc.com> wrote:
> Hi List;
>
> I have a large tble (playback_device) with 6million rows in it. The
> aff_id_tmp1 table has 600,000 rows.
> - why am I still getting a seq scan ?
>
You're selecting almost all the rows in the product of aff_id_tmp1 *
playback_fragment. A sequential scan will be far faster than an index
scan. You can prove this to yourself using 'set enable_seqscan to
false' and running the query again. It should be much slower.
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: seq scan issue...
@ 2008-04-17 18:31 Rodrigo Gonzalez <rjgonzale@gmail.com>
parent: kevin kempter <kevin@kevinkempterllc.com>
2 siblings, 0 replies; 4+ messages in thread
From: Rodrigo Gonzalez @ 2008-04-17 18:31 UTC (permalink / raw)
To: kevin kempter <kevin@kevinkempterllc.com>; +Cc: pgsql-performance
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...=
Attachments:
smime.p7s (application/x-pkcs7-signature, 3.2K) download
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: seq scan issue...
@ 2008-04-17 23:53 PFC <lists@peufeu.com>
parent: kevin kempter <kevin@kevinkempterllc.com>
2 siblings, 0 replies; 4+ messages in thread
From: PFC @ 2008-04-17 23:53 UTC (permalink / raw)
To: kevin kempter <kevin@kevinkempterllc.com>; pgsql-performance
> - why am I still getting a seq scan ?
You'll seq scan tmp1 obviously, and also the other table since you fetch a
very large part of it in the process.
It's the only way to do this query since there is no WHERE to restrict the
number of rows and the DISTINCT applies on columns from both tables.
You might want to simplify your query. For instance perhaps you can get
pf.segment_id out of the DISTINCT, in which case you can put the distinct
in a subquery on tmp1.
>
> 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)
>
>
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2008-04-17 23:53 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2008-04-17 18:24 seq scan issue... kevin kempter <kevin@kevinkempterllc.com>
2008-04-17 18:30 ` Jeffrey Baker <jwbaker@gmail.com>
2008-04-17 18:31 ` Rodrigo Gonzalez <rjgonzale@gmail.com>
2008-04-17 23:53 ` PFC <lists@peufeu.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox