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.idTable: 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!