pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedIndex called with Union but not with OR clause
2+ messages / 2 participants
[nested] [flat]
* Index called with Union but not with OR clause
@ 2004-02-20 06:56 V Chitra <vchitra@techbooks.com>
0 siblings, 1 reply; 2+ messages in thread
From: V Chitra @ 2004-02-20 06:56 UTC (permalink / raw)
To: pgsql-admin@postgresql.org
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.
Thanks in advance
Chitra
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: Index called with Union but not with OR clause
@ 2004-02-20 20:11 Bruno Wolff III <bruno@wolff.to>
parent: V Chitra <vchitra@techbooks.com>
0 siblings, 0 replies; 2+ messages in thread
From: Bruno Wolff III @ 2004-02-20 20:11 UTC (permalink / raw)
To: V Chitra <vchitra@techbooks.com>; +Cc: pgsql-admin@postgresql.org; pgsql-performance
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 <vchitra@techbooks.com> 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.
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2004-02-20 20:11 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2004-02-20 06:56 Index called with Union but not with OR clause V Chitra <vchitra@techbooks.com>
2004-02-20 20:11 ` Bruno Wolff III <bruno@wolff.to>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox