From: PFC <lists@peufeu.com>
To: kevin kempter <kevin@kevinkempterllc.com>
To: pgsql-performance@postgresql.org
Subject: Re: seq scan issue...
Date: Fri, 18 Apr 2008 01:53:19 +0200
Message-ID: <op.t9r4m5nmcigqcu@apollo13.peufeu.com> (raw)
In-Reply-To: <EAC52C54-0EB7-4823-BAEE-5326C773974D@kevinkempterllc.com>
References: <EAC52C54-0EB7-4823-BAEE-5326C773974D@kevinkempterllc.com>
> - 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)
>
>
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: lists@peufeu.com, kevin@kevinkempterllc.com
Subject: Re: seq scan issue...
In-Reply-To: <op.t9r4m5nmcigqcu@apollo13.peufeu.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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