Received: from localhost (unknown [200.46.204.183]) by developer.postgresql.org (Postfix) with ESMTP id 6AD642E002D for ; Thu, 17 Apr 2008 20:48:51 -0300 (ADT) Received: from developer.postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 41453-01 for ; Thu, 17 Apr 2008 20:48:45 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.5 Received: from 27.mail-out.ovh.net (27.mail-out.ovh.net [91.121.30.210]) by developer.postgresql.org (Postfix) with SMTP id 813D92E002C for ; Thu, 17 Apr 2008 20:48:45 -0300 (ADT) Received: (qmail 11919 invoked by uid 503); 18 Apr 2008 01:31:08 -0000 Received: from gw2.ovh.net (HELO mail174.ha.ovh.net) (213.251.189.202) by 27.mail-out.ovh.net with SMTP; 18 Apr 2008 01:31:08 -0000 Received: from b0.ovh.net (HELO queue-out) (213.186.33.50) by b0.ovh.net with SMTP; 17 Apr 2008 23:48:44 -0000 Received: from par69-8-88-161-102-87.fbx.proxad.net (HELO apollo13.peufeu.com) (88.161.102.87) by ns0.ovh.net with SMTP; 17 Apr 2008 23:48:42 -0000 Date: Fri, 18 Apr 2008 01:53:19 +0200 To: "kevin kempter" , pgsql-performance@postgresql.org Subject: Re: seq scan issue... From: PFC Content-Type: text/plain; format=flowed; delsp=yes; charset=utf-8 MIME-Version: 1.0 References: Content-Transfer-Encoding: Quoted-Printable Message-ID: In-Reply-To: User-Agent: Opera Mail/9.24 (Linux) X-Ovh-Remote: 88.161.102.87 (par69-8-88-161-102-87.fbx.proxad.net) X-Ovh-Local: 213.186.33.20 (ns0.ovh.net) X-Spam-Check: DONE|H 0.5/N X-Ovh-Tracer-Id: 18004265410707260976 X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.756 tagged_above=0 required=5 tests=AWL=0.756 X-Spam-Level: X-Archive-Number: 200804/254 X-Sequence-Number: 29747 > - 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 t= he = 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 distinc= t = in a subquery on tmp1. > > Thanks in advance. > > > > > > > =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D > Explain PLan > =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D > > 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 =3D pf.playback_device_id ; > > > Unique (cost=3D2966361.56..3194555.91 rows=3D10104496 width=3D97) > -> Sort (cost=3D2966361.56..2998960.76 rows=3D13039677 width=3D9= 7) > Sort Key: tmp1.affiliate_id, tmp1.name, tmp1.description, = > tmp1.create_dt, > tmp1.playback_device_id, pf.segment_id > -> Hash Join (cost=3D23925.45..814071.14 rows=3D13039677 = > width=3D97) > Hash Cond: (pf.playback_device_id =3D = > tmp1.playback_device_id) > -> Seq Scan on playback_fragment pf = > (cost=3D0.00..464153.77 rows=3D130 > 39677 width=3D16) > -> Hash (cost=3D16031.31..16031.31 rows=3D631531 wid= th=3D89) > -> Seq Scan on aff_id_tmp1 tmp1 = > (cost=3D0.00..16031.31 rows=3D63 > 1531 width=3D89) > (1068 rows) > >