From: | Timasmith <timasmith(at)hotmail(dot)com> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | how to enforce index usage with +0 |
Date: | 2007-06-10 12:32:55 |
Message-ID: | 1181478775.030881.325650@c77g2000hse.googlegroups.com |
Views: | Whole Thread | Raw Message | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Martijn van Oosterhout | 2007-06-10 13:01:49 | Re: gist index on cube column |
Previous Message | A. Kretschmer | 2007-06-10 12:21:35 | Re: How to count pairs? |