From: Rodrigo Gonzalez <rjgonzale@gmail.com>
To: kevin kempter <kevin@kevinkempterllc.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: seq scan issue...
Date: Thu, 17 Apr 2008 15:31:10 -0300
Message-ID: <4807976E.3070707@gmail.com> (raw)
In-Reply-To: <EAC52C54-0EB7-4823-BAEE-5326C773974D@kevinkempterllc.com>
References: <EAC52C54-0EB7-4823-BAEE-5326C773974D@kevinkempterllc.com>
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
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: rjgonzale@gmail.com, kevin@kevinkempterllc.com
Subject: Re: seq scan issue...
In-Reply-To: <4807976E.3070707@gmail.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