Received: from localhost (unknown [200.46.204.183]) by developer.postgresql.org (Postfix) with ESMTP id A92652E0053 for ; Fri, 4 Apr 2008 01:58:14 -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 07728-02 for ; Fri, 4 Apr 2008 01:58:11 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.5 Received: from an-out-0708.google.com (an-out-0708.google.com [209.85.132.251]) by developer.postgresql.org (Postfix) with ESMTP id 4BC152E002D for ; Fri, 4 Apr 2008 01:58:11 -0300 (ADT) Received: by an-out-0708.google.com with SMTP id c36so885633ana.33 for ; Thu, 03 Apr 2008 21:58:10 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=beta; h=domainkey-signature:received:received:message-id:date:from:to:subject:in-reply-to:mime-version:content-type:references; bh=fetXGMRapFDv+6ZWe9JUSzh32u9LfLAMv0hhAQJfnMM=; b=kwfOCxKuFdZ8S4lye4X9W65Hu48LtaE8eRgyMGRHHKqdJ7YWVlymxti0KbaFlIXx3XdPaK7GxPJHfXKHW3wulDScjdd6f6LUWmnNIII2ytZKyOMN9PyJ6hI6woebX1y41jmbfIEUE2ERerd59BkP9F8vRQnKNZC2GJWTJG6vVrU= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=beta; h=message-id:date:from:to:subject:in-reply-to:mime-version:content-type:references; b=T5+Sv/7ZteAveLHYPBOgddkU1EOG75J1cGCHYkdniCqBVmdl1K0BLQncwEcpY0XgcEDRCedfAvHkoHPYdNqwc6qoaC8Qgbq1HexaXygWSNcqB9bwMA4/dSH2UDxWGEhqx5A/GDCUtPmRQI1T4YV/VXOA6D3pWmrJEy11sTYjob0= Received: by 10.101.70.15 with SMTP id x15mr1704301ank.109.1207285090565; Thu, 03 Apr 2008 21:58:10 -0700 (PDT) Received: by 10.100.194.10 with HTTP; Thu, 3 Apr 2008 21:58:10 -0700 (PDT) Message-ID: <3bda20f60804032158u4f6765e3t8b0debad3e7f5ceb@mail.gmail.com> Date: Thu, 3 Apr 2008 22:58:10 -0600 From: "Matt Klinker" To: pgsql-performance@postgresql.org Subject: Re: Query plan excluding index on view In-Reply-To: <29828.1207271948@sss.pgh.pa.us> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_5520_32161134.1207285090564" References: <3bda20f60804031631j177b42e2u82b5033fd23c2293@mail.gmail.com> <28177.1207266621@sss.pgh.pa.us> <3bda20f60804031654q7991de08m1254964511632acf@mail.gmail.com> <29828.1207271948@sss.pgh.pa.us> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.001 tagged_above=0 required=5 tests=HTML_MESSAGE=0.001 X-Spam-Level: X-Archive-Number: 200804/40 X-Sequence-Number: 29533 ------=_Part_5520_32161134.1207285090564 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline Sorry for not including this extra bit originally. Below is the explain detail from both the query to the view that takes longer and then the query directly to the single table that performs quickly. Hash Join (cost=49082.96..1940745.80 rows=11412 width=76) Hash Cond: (outer.?column1? = inner.listing_fid) -> Append (cost=0.00..1290709.94 rows=18487347 width=252) -> Subquery Scan *SELECT* 1 (cost=0.00..1285922.80 rows=18384890 width=251) -> Seq Scan on company (cost=0.00..1102073.90 rows=18384890 width=251) -> Subquery Scan *SELECT* 2 (cost=0.00..4787.14 rows=102457 width=252) -> Seq Scan on school (cost=0.00..3762.57 rows=102457 width=252) -> Hash (cost=49042.64..49042.64 rows=16130 width=8) -> Bitmap Heap Scan on listing_node_xref xref (cost=102.45..49042.64 rows=16130 width=8) Recheck Cond: (node_fid = 173204537) -> Bitmap Index Scan on idx_listing_node_xref_node_fid (cost=0.00..102.45 rows=16130 width=0) Index Cond: (node_fid = 173204537) Nested Loop (cost=102.45..98564.97 rows=11349 width=517) -> Bitmap Heap Scan on listing_node_xref xref (cost=102.45..49042.64 rows=16130 width=8) Recheck Cond: (node_fid = 173204537) -> Bitmap Index Scan on idx_listing_node_xref_node_fid (cost=0.00..102.45 rows=16130 width=0) Index Cond: (node_fid = 173204537) -> Index Scan using idx_pki_company_id on company c (cost=0.00..3.06 rows=1 width=517) Index Cond: (c.id = outer.listing_fid) On Thu, Apr 3, 2008 at 7:19 PM, Tom Lane wrote: > "Matt Klinker" writes: > > I new I'd forget something! I've tried this on both 8.2 and 8.3 with > the > > same results. > > Then you're going to have to provide more details ... > > regards, tom lane > ------=_Part_5520_32161134.1207285090564 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline Sorry for not including this extra bit originally.  Below is the explain detail from both the query to the view that takes longer and then the query directly to the single table that performs quickly.

Hash Join  (cost=49082.96..1940745.80 rows=11412 width=76)
  Hash Cond: (outer.?column1? = inner.listing_fid)
  ->  Append  (cost=0.00..1290709.94 rows=18487347 width=252)
        ->  Subquery Scan *SELECT* 1  (cost=0.00..1285922.80 rows=18384890 width=251)
              ->  Seq Scan on company  (cost=0.00..1102073.90 rows=18384890 width=251)
        ->  Subquery Scan *SELECT* 2  (cost=0.00..4787.14 rows=102457 width=252)
              ->  Seq Scan on school  (cost=0.00..3762.57 rows=102457 width=252)
  ->  Hash  (cost=49042.64..49042.64 rows=16130 width=8)
        ->  Bitmap Heap Scan on listing_node_xref xref  (cost=102.45..49042.64 rows=16130 width=8)
              Recheck Cond: (node_fid = 173204537)
              ->  Bitmap Index Scan on idx_listing_node_xref_node_fid  (cost=0.00..102.45 rows=16130 width=0)
                    Index Cond: (node_fid = 173204537)


Nested Loop  (cost=102.45..98564.97 rows=11349 width=517)
  ->  Bitmap Heap Scan on listing_node_xref xref  (cost=102.45..49042.64 rows=16130 width=8)
        Recheck Cond: (node_fid = 173204537)
        ->  Bitmap Index Scan on idx_listing_node_xref_node_fid  (cost=0.00..102.45 rows=16130 width=0)
              Index Cond: (node_fid = 173204537)
  ->  Index Scan using idx_pki_company_id on company c  (cost=0.00..3.06 rows=1 width=517)
        Index Cond: (c.id = outer.listing_fid)


On Thu, Apr 3, 2008 at 7:19 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
"Matt Klinker" <mklinker@gmail.com> writes:
> I new I'd forget something!  I've tried this on both 8.2 and 8.3 with the
> same results.

Then you're going to have to provide more details ...

                       regards, tom lane

------=_Part_5520_32161134.1207285090564--