[PERFORM] Query plan excluding index on view
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 Table: ItemB id<- primary key name description 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!
Re: [PERFORM] 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 <[EMAIL PROTECTED]> wrote: > "Matt Klinker" <[EMAIL PROTECTED]> 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 >
Re: [PERFORM] Query plan excluding index on view
.304.90 rows=16240 width=0) Index Cond: (node_fid = 173204537) -> Index Scan using pk_company_id on company c (cost=0.00..9.67 rows=1 width=424) Index Cond: (c.id = xref.listing_fid) On Thu, Apr 3, 2008 at 11:49 PM, Tom Lane <[EMAIL PROTECTED]> wrote: > "Matt Klinker" <[EMAIL PROTECTED]> writes: > > 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. > ... > > -> Subquery Scan *SELECT* 1 (cost=0.00..1285922.80 > rows=18384890 > > width=251) > > -> Seq Scan on company (cost=0.00..1102073.90 > rows=18384890 > > The presence of a Subquery Scan node tells me that either this is a much > older PG version than you stated, or there are some interesting details > to the query that you omitted. Please drop the fan-dance routine and > show us a complete reproducible test case. > >regards, tom lane >
Re: [SOLVED] [PERFORM] Query plan excluding index on view
Removing the constants definitely did take care of the issue on 8.3 (still same query plan on 8.1). Thanks for your help in getting this resolved, and sorry again for not including all relevant information on my initial request On Fri, Apr 4, 2008 at 10:20 PM, Tom Lane <[EMAIL PROTECTED]> wrote: > "Matt Klinker" <[EMAIL PROTECTED]> writes: > > --Joined View: > > CREATE OR REPLACE VIEW directory_listing AS > > SELECT school.id, school.name, school.description, 119075291 AS > > listing_type_fid > >FROM school > > UNION ALL > > SELECT company.id, company.name, company.description, 119074833 AS > > listing_type_fid > >FROM company; > > Ah, there's the problem :-(. Can you get rid of the constants here? > The planner's currently not smart about UNION ALL subqueries unless > their SELECT lists contain just simple column references. > > (Yes, fixing that is on the todo list, but don't hold your breath... > it'll be 8.4 material at the earliest.) > >regards, tom lane >
