From: Matt Klinker <mklinker@gmail.com>
To: pgsql-performance@postgresql.org
Subject: Re: Query plan excluding index on view
Date: Thu, 3 Apr 2008 22:58:10 -0600
Message-ID: <3bda20f60804032158u4f6765e3t8b0debad3e7f5ceb@mail.gmail.com> (raw)
In-Reply-To: <29828.1207271948@sss.pgh.pa.us>
References: <3bda20f60804031631j177b42e2u82b5033fd23c2293@mail.gmail.com>
<28177.1207266621@sss.pgh.pa.us>
<3bda20f60804031654q7991de08m1254964511632acf@mail.gmail.com>
<29828.1207271948@sss.pgh.pa.us>
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
>
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: mklinker@gmail.com
Subject: Re: Query plan excluding index on view
In-Reply-To: <3bda20f60804032158u4f6765e3t8b0debad3e7f5ceb@mail.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