Re: Using index name in select Query

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
>