Re: Query plan excluding index on view

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(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> "Matt Klinker" <mklinker(at)gmail(dot)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
>