Query plan excluding index on view

From: "Matt Klinker" <mklinker(at)gmail(dot)com>
To: pgsql-performance(at)postgresql(dot)org
Subject: Query plan excluding index on view
Date: 2008-04-03 23:31:45
Message-ID: 3bda20f60804031631j177b42e2u82b5033fd23c2293@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-performance

I'm trying to fine tune this query to return in a reasonable amount of time
and am having difficulties getting the query to run the way I'd like. I
have a couple of semi-related entities that are stored in individual tables,
say, A and B. There is then a view created that pulls together the common
fields from these 2 tables. These are then related through a m:m
relationship to a classification. Quick definitions of all of this follows:

Table: ItemA
id <- primary key
name
description
<addtl fields for A>

Table: ItemB
id <- primary key
name
description
<addtl fields for B>

View: Combined
SELECT id, name, description from ItemA
UNION ALL
SELECT id, name, description from ItemB

Table: xref
id <- primary key
item_id <- indexed, points to either ItemA.id or ItemB.id
classifcation_id <- indexed, points to classification.id

Table: classifcation
id <- primiary key
name

I'm trying to query from the classification, through the xref, and to the
view to get a list of Items (either A or B) that are tied to a specific
classification. My query is rather simple, baiscally as follows:

SELECT id, name, description
FROM combination c
INNER JOIN xref on c.id = xref.item_id
WHERE xref.classifcation_id = 1

This query runs in about 2-3 minutes (I should mention that ItemA has ~18M
records and xref has ~26M records - and both will continue to grow). The
explain text shows a disregard for the indexes on ItemA and ItemB and a
sequence scan is done on both of them. However, if I rewrite this query to
join directly to ItemA rather to the view it runs in ~50ms because it now
uses the proper index.

I know it's generally requested to include the EXPLAIN text when submitting
a specific question, but I thought perhaps this was generic enough that
someone might at least have some suggestions. If required I can certainly
work up a simpler example, or I could include my actual explain (though it
doesn't exactly match everything defined above as I tried to keep things
rather generic).

Any links would be nice as well, from all my searching the past few days,
most of the performance tuning resources I could find where about tuning the
server itself, not really a specific query - at least not one that dealt
with this issue. If you've read this far - thank you much!

Responses

Browse pgsql-performance by date

  From Date Subject
Next Message Tom Lane 2008-04-03 23:50:21 Re: Query plan excluding index on view
Previous Message Justin 2008-04-03 18:21:27 [Fwd: Re: Max shared_buffers]