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!
