Re: Using index name in select Query

Re-read your second post. Not sure why using a particular index is a
requirement even if it might lead to a less efficient query. Still, if
hints is what you are looking for, then the answer is no, there is no such
feature in postgres. On the other hand, if you do want to force postgres to
use a particular index, you need to understand how postgres is currently
choosing to execute the query, and which index scans are more efficient and
being chosen over the index that you want it to use. I guess then all you
need to do is delete those indexes so postgres has no choice but to use
yours. Although it is definitely not the brightest idea.

Payal Singh,
OmniTi Computer Consulting Inc.
Junior Database Architect,
Phone: 240.646.0770 x 253

On Sat, Oct 19, 2013 at 11:34 AM, Payal Singh <payal(at)omniti(dot)com> wrote:

> I suggest you have a look at EXPLAIN (
> http://www.postgresql.org/docs/9.3/static/sql-explain.html) if you
> haven't already. 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.
>
> You should also have a look at Depesz's series on this topic -
> http://www.depesz.com/tag/unexplainable/ .
>
> Payal Singh,
> OmniTi Computer Consulting Inc.
> Junior Database Architect,
> Phone: 240.646.0770 x 253
>
>
> On Sat, Oct 19, 2013 at 10:28 AM, naman.iitb <naman(dot)bbps(at)gmail(dot)com> wrote:
>
>> 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.
>>
>>
>> --
>> Sent via pgsql-novice mailing list (pgsql-novice(at)postgresql(dot)org)
>> To make changes to your subscription:
>> http://www.postgresql.org/mailpref/pgsql-novice
>>
>
>