SEO

#1 (**permalink**) 04-09-2008, 05:48 PM

Hi,

In Oracle there are instances when as a developer I know how the data
is spread in the tables and I want to be sure the database uses the
right index. Does the following hold true in Postgresql for a query
like this:

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.

The +0 and date_trunc is used purely to ensure the valid_unit_dt field
(which is indexed) is not used.

Is this efficient?

Does date_trunc render the index invalid or can I do something else
(+0) doesnt work.

thanks

Tim

#2 (**permalink**) 04-09-2008, 05:52 PM

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@crankycanuck.ca
This work was visionary and imaginative, and goes to show that visionary
and imaginative work need not end up well.
--Dennis Ritchie

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

SEO

how to enforce index usage with +0