Received: from localhost (unknown [200.46.204.183])
	by developer.postgresql.org (Postfix) with ESMTP id 6B6762E0049
	for <pgsql-performance-postgresql.org@developer.postgresql.org>;
	Thu,  3 Apr 2008 20:31:50 -0300 (ADT)
Received: from developer.postgresql.org ([200.46.204.71])
	by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024)
	with ESMTP id 19761-08
	for <pgsql-performance-postgresql.org@developer.postgresql.org>;
	Thu,  3 Apr 2008 20:31:47 -0300 (ADT)
X-Greylist: domain auto-whitelisted by SQLgrey-1.7.5
Received: from wx-out-0506.google.com (wx-out-0506.google.com [66.249.82.234])
	by developer.postgresql.org (Postfix) with ESMTP id 00FD12E002D
	for <pgsql-performance@postgresql.org>;
	Thu,  3 Apr 2008 20:31:46 -0300 (ADT)
Received: by wx-out-0506.google.com with SMTP id r21so3346303wxc.28
	for <pgsql-performance@postgresql.org>;
	Thu, 03 Apr 2008 16:31:45 -0700 (PDT)
DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=beta;
	h=domainkey-signature:received:received:message-id:date:from:to:subject:mime-version:content-type;
	bh=nIvudusLXdao9Rkk4KaSFFbmTH0XI89bzvPmdd4Wwaw=;
	b=anxdQL20TTPCCG2jnXQ6eOhlD4AuCxAHOrCeZWfHg0EMwYVvf0K6KrMoUEGSjPIHxjCuMiBbx4VjNfyg+wa7GJLgiVjzhTG4KVa3K+43xIPZABptlWtVmBBvfGMwyviPK8qJvlQ8IKQGXAU4bpRzKDPD1dHYqzN39sXU+u1X73o=
DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=beta;
	h=message-id:date:from:to:subject:mime-version:content-type;
	b=gsA49ncaRCZiu55h/fgYD1vK/k5Rd89NSpO5/xZ65XSdV42dg/CO6zSOXIWsyuwS0n5oJ1EluD3As4Pe6W2hvYXJaRZVN0/xBNyMKs43EGInHK4jJeLzrFLXUz5SJCigu+kShhHCXLRQr/42Axan+mxjN95Ox1HSuZObe0CA4qA=
Received: by 10.100.215.14 with SMTP id n14mr1003237ang.69.1207265505687;
	Thu, 03 Apr 2008 16:31:45 -0700 (PDT)
Received: by 10.100.194.10 with HTTP; Thu, 3 Apr 2008 16:31:45 -0700 (PDT)
Message-ID: <3bda20f60804031631j177b42e2u82b5033fd23c2293@mail.gmail.com>
Date: Thu, 3 Apr 2008 17:31:45 -0600
From: "Matt Klinker" <mklinker@gmail.com>
To: pgsql-performance@postgresql.org
Subject: Query plan excluding index on view
MIME-Version: 1.0
Content-Type: multipart/alternative;
	boundary="----=_Part_4658_15454294.1207265505694"
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=0.001 tagged_above=0 required=5
	tests=HTML_MESSAGE=0.001
X-Spam-Level: 
X-Archive-Number: 200804/38
X-Sequence-Number: 29531

------=_Part_4658_15454294.1207265505694
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
Content-Disposition: inline

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!

------=_Part_4658_15454294.1207265505694
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
Content-Disposition: inline

I&#39;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&#39;d like.&nbsp; I have a couple of semi-related entities that are stored in individual tables, say, A and B.&nbsp; There is then a view created that pulls together the common fields from these 2 tables.&nbsp; These are then related through a m:m relationship to a classification.&nbsp; Quick definitions of all of this follows:<br>
<br>Table: ItemA<br>id&nbsp;&nbsp;&nbsp;&nbsp; &lt;- primary key<br>name<br>description<br>&lt;addtl fields for A&gt;<br><br>Table: ItemB<br>id&nbsp;&nbsp;&nbsp; &lt;- primary key<br>name<br>description<br>&lt;addtl fields for B&gt;<br><br><br>View: Combined<br>
SELECT id, name, description from ItemA<br>UNION ALL<br>SELECT id, name, description from ItemB<br><br><br>Table: xref<br>id&nbsp;&nbsp; &lt;- primary key<br>item_id&nbsp; &lt;- indexed, points to either ItemA.id or ItemB.id<br>classifcation_id&nbsp; &lt;- indexed, points to <a href="http://classification.id">classification.id</a><br>
<br><br>Table: classifcation<br>id&nbsp;&nbsp; &lt;- primiary key<br>name<br><br>I&#39;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.&nbsp; My query is rather simple, baiscally as follows:<br>
<br>SELECT id, name, description<br>FROM combination c<br>&nbsp;&nbsp;&nbsp; INNER JOIN xref on <a href="http://c.id">c.id</a> = xref.item_id<br>WHERE xref.classifcation_id = 1<br><br>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).&nbsp; The explain text shows a disregard for the indexes on ItemA and ItemB and a sequence scan is done on both of them.&nbsp; 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.<br>
<br>I know it&#39;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.&nbsp; If required I can certainly work up a simpler example, or I could include my actual explain (though it doesn&#39;t exactly match everything defined above as I tried to keep things rather generic).<br>
<br>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.&nbsp; If you&#39;ve read this far - thank you much!&nbsp; <br>

------=_Part_4658_15454294.1207265505694--