From vchitra@techbooks.com Fri Feb 20 07:02:20 2004 X-Original-To: pgsql-admin-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.2]) by svr1.postgresql.org (Postfix) with ESMTP id B80ADD1DF7F for ; Fri, 20 Feb 2004 07:02:20 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024) with ESMTP id 64619-04 for ; Fri, 20 Feb 2004 03:01:52 -0400 (AST) Received: from delhimail.tbdelhi.com (unknown [208.179.178.248]) by svr1.postgresql.org (Postfix) with ESMTP id EC994D1DD5D for ; Fri, 20 Feb 2004 03:01:43 -0400 (AST) Received: from VICHITRA (delhiwg1-opt.techbooks.com [208.179.178.190] (may be forged)) by delhimail.tbdelhi.com (8.9.2/8.9.2) with SMTP id MAA02790 for ; Fri, 20 Feb 2004 12:29:02 +0530 (DST) Message-ID: <008401c3f77e$a78f1d20$18dda8c0@VICHITRA> From: "V Chitra" To: Subject: Index called with Union but not with OR clause Date: Fri, 20 Feb 2004 12:26:22 +0530 MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_0081_01C3F7AC.C0117130" X-Priority: 3 X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook Express 6.00.2800.1106 X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1106 X-Virus-Scanned: by amavisd-new at postgresql.org X-Spam-Status: No, hits=0.1 tagged_above=0.0 required=5.0 tests=BAYES_44, HTML_MESSAGE X-Spam-Level: X-Archive-Number: 200402/302 X-Sequence-Number: 12476 This is a multi-part message in MIME format. ------=_NextPart_000_0081_01C3F7AC.C0117130 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Hi All, I have a select statement=20 select * from v_func_actual_costs where parent_project=3D'10478' or proj_pk =3D '10478' both the fields parent_project and proj_pk have indexes based on them, but = when I ran explain plan on this statement I found that none of the indexes = are being called. But, if I make two separate statement and combine them wi= th Union ALL, the indexes are being called. The select statement in this ca= se is select * from ct_admin.v_func_actual_costs where parent_project=3D'10478' union all select * from ct_admin.v_func_actual_costs where proj_pk =3D '10478'=20 Can anybody help me to find a reason for the same. This is just a part of t= he query so I cannot use the Union ALL clause. Thanks in advance Chitra ------=_NextPart_000_0081_01C3F7AC.C0117130 Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable
Hi All,
 
I have a select statement 
 
select * from v_func_actual_costs
where= =20 parent_project=3D'10478' or proj_pk =3D '10478'
 
both the fields parent_project and proj_pk= have=20 indexes based on them, but when I ran explain plan on this statement I= =20 found that none of the indexes are being called. But, if I make two separat= e=20 statement and combine them with Union ALL, the indexes are being called. Th= e=20 select statement in this case is
 
select * from ct_admin.v_func_actual_costs=
where=20 parent_project=3D'10478'
union all
select * from=20 ct_admin.v_func_actual_costs
where proj_pk =3D '10478'
 
Can anybody help me to find a reason for t= he same.=20 This is just a part of the query so I cannot use the Union ALL=20 clause.
 
Thanks in advance

Chitra
------=_NextPart_000_0081_01C3F7AC.C0117130-- From bruno@wolff.to Fri Feb 20 20:09:41 2004 X-Original-To: pgsql-admin-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.2]) by svr1.postgresql.org (Postfix) with ESMTP id 2B037D1DD04 for ; Fri, 20 Feb 2004 20:09:41 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024) with ESMTP id 29623-01 for ; Fri, 20 Feb 2004 16:09:33 -0400 (AST) Received: from wolff.to (wolff.to [66.93.249.74]) by svr1.postgresql.org (Postfix) with SMTP id 6E054D1D432 for ; Fri, 20 Feb 2004 16:09:31 -0400 (AST) Received: (qmail 29350 invoked by uid 500); 20 Feb 2004 20:11:39 -0000 Date: Fri, 20 Feb 2004 14:11:39 -0600 From: Bruno Wolff III To: V Chitra Cc: pgsql-admin@postgresql.org, pgsql-performance@postgresql.org Subject: Re: Index called with Union but not with OR clause Message-ID: <20040220201139.GA25578@wolff.to> Mail-Followup-To: pgsql-performance@postgresql.org, vchitra@techbooks.com References: <008401c3f77e$a78f1d20$18dda8c0@VICHITRA> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: <008401c3f77e$a78f1d20$18dda8c0@VICHITRA> User-Agent: Mutt/1.5.6i X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200402/312 X-Sequence-Number: 12486 This discussion really belongs on the performance list and I am copying that list with mail-followup-to set. On Fri, Feb 20, 2004 at 12:26:22 +0530, V Chitra wrote: > Hi All, > > I have a select statement > > select * from v_func_actual_costs > where parent_project='10478' or proj_pk = '10478' > > both the fields parent_project and proj_pk have indexes based on them, but when I ran explain plan on this statement I found that none of the indexes are being called. But, if I make two separate statement and combine them with Union ALL, the indexes are being called. The select statement in this case is > > select * from ct_admin.v_func_actual_costs > where parent_project='10478' > union all > select * from ct_admin.v_func_actual_costs > where proj_pk = '10478' > > Can anybody help me to find a reason for the same. This is just a part of the query so I cannot use the Union ALL clause. Have you analyzed the databases recently? Can you supply explain analyze output for the queries? It isn't necessarily faster to use two index scans instead of one sequential scan depending on the fraction of the table being returned and some other factors. If the planner is making the wrong choice in your case, you need to supply the list with more information to get help figuring out why the wrong choice is being made.