Thread: Using index name in select Query

Naman wrote
> I want to write a query by which i can force the optimizer to use the
> specified index.

No.

Why?

David J.



--
View this message in context:
http://postgresql.1045698.n5.nabble.com/Using-index-name-in-select-Query-tp5775138p5775140.html
Sent from the PostgreSQL - novice mailing list archive at Nabble.com.

Actually as part of project i have to achieve following task :


suppose i have a table t1(a,b,c,d)

I make an index as :
   CREATE INDEX index_abcd on t1(a) WHERE a is NOT NULL and b IS NULL and c
IS NULL and d IS NOT NULL.

There is a high probability but not 100% that optimizer will use the above
index if following query is fired :

    SELECT * FROM t1 WHERE a=2 and b IS NULL and c IS NULL and d IS NOT NULL

Now my requirement is if i know the index name i.e. index_abcd then is there
a way by which i can use it in the select query so that i can be sure that
optimizer will pick the index specified(i.e index_abcd) and no other.

Some other databases like mySql have this provision using USE INDEX clause

I want something similar in postgresql



--
View this message in context:
http://postgresql.1045698.n5.nabble.com/Using-index-name-in-select-Query-tp5775138p5775143.html
Sent from the PostgreSQL - novice mailing list archive at Nabble.com.

Payal Singh-2 wrote
>>Postgres chooses the best, most efficient way to optimize
>> the query, and so if your index actually has the potential to improve the
>> select query at hand, postgres will choose it. If there is a better way
>> to
>> execute your query than using your index, postgres will not use it.

To be fair PostgreSQL is not infallible so if you have a situation where you
believe PostgreSQL should be using an index where it is not then such a
situation should be documented in detail and sent to the list.  You can use
various "enable_" GUC to disable sequential scanning and index scanning so
that supposedly sub-optimal plans are more likely to be chosen (i.e., you
cannot absolutely disable sequential scans since in some cases brute-force
is the only valid method to execute a query).  Costing parameters can also
be altered to better reflect your actual system.

In short the project's policy is to fix underlying problems (via code or
configuration) rather than implement planner hints that simply allow people
to more often shoot themselves in the foot rather than provide a permanent
solution.

David J.




--
View this message in context:
http://postgresql.1045698.n5.nabble.com/Using-index-name-in-select-Query-tp5775138p5775148.html
Sent from the PostgreSQL - novice mailing list archive at Nabble.com.

On Sat, Oct 19, 2013 at 4:28 PM, naman.iitb <naman.bbps@gmail.com> wrote:

> Now my requirement is if i know the index name i.e. index_abcd then is there
> a way by which i can use it in the select query so that i can be sure that
> optimizer will pick the index specified(i.e index_abcd) and no other.
>

What you want is called "query hints" and PostgreSQL, as pointed out
by others, do not support it by policy. The idea is that the planner
knows better than you what to do with your query. What you believe is
the better index today could be the bad index tomorrow, or even a few
seconds after you just told your boss how smart you have  been
designing such index, and that is strictly tied to the workload of
your cluster.
The only way to force PostgreSQL using your own index is to disable
all other access methods (e.g., enabled_seqscan = off) and to make all
other indexes not available (via catalogs). But this will impact all
your cluster.

Any chance this is an homework?

Luca