From: | Andrew Sullivan <ajs(at)crankycanuck(dot)ca> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: how to enforce index usage with +0 |
Date: | 2007-06-13 20:56:33 |
Message-ID: | 20070613205633.GB1505@phlogiston.dyndns.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
On Sun, Jun 10, 2007 at 05:32:55AM -0700, Timasmith wrote:
> select s.order_id
> from small_orders_table s, orders o
> where s.find_these_id in
> (select some_id from some_table where some_indexed_field = 'ABC')
> and s.order_id+0 = o.order_id
> and date_trunc('microseconds', o.valid_until_dt) < now()
>
> This should essentially use the s.some_indexed_field as the primary
> index and hit the orders table on the order id.
It will do this automatically if the selectivity of your
some_indexed_field values leans that way. I think you're probably
trying to outsmart the planner/optimiser here, and that's _usually_
not a good idea. IT shouldn't make any difference whether you add
that +0 or not, assuming the database is tuned correctly.
I'd be rather more worried about the date_trunc stuff. You probably
want a functional index on there.
A
--
Andrew Sullivan | ajs(at)crankycanuck(dot)ca
This work was visionary and imaginative, and goes to show that visionary
and imaginative work need not end up well.
--Dennis Ritchie
From | Date | Subject | |
---|---|---|---|
Next Message | Ron Johnson | 2007-06-13 21:00:19 | Re: pointer to feature comparisons, please |
Previous Message | Johannes Konert | 2007-06-13 20:22:01 | Re: pg_xlog - files are guaranteed to be sequentialy named? |