Re: Using index name in select Query - Mailing list pgsql-novice
| From | Payal Singh |
|---|---|
| Subject | Re: Using index name in select Query |
| Date | |
| Msg-id | CANUg7LAqcv6-0BeosO_BOb3O1-c0-RCkoBH4q=kb-xCUdaJs+A@mail.gmail.com Whole thread Raw |
| In response to | Re: Using index name in select Query (Payal Singh <payal@omniti.com>) |
| Responses |
Re: Using index name in select Query
(David Johnston <polobo@yahoo.com>)
|
| List | pgsql-novice |
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.
On Sat, Oct 19, 2013 at 11:34 AM, Payal Singh <payal@omniti.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/ .On Sat, Oct 19, 2013 at 10:28 AM, naman.iitb <naman.bbps@gmail.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.htmlSent from the PostgreSQL - novice mailing list archive at Nabble.com.
--
Sent via pgsql-novice mailing list (pgsql-novice@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-novice
pgsql-novice by date: