Postgresql, Using Index with Between

Question

I have index on a column, let's say ID (bigint). If I have a query with something like this:

SELECT * 
  FROM table 
 WHERE id = 12345

...it will use index. But when I'm using query like...

SELECT * 
  FROM table 
 WHERE id >= 12345 
   AND id <= 12366

It use sequential scan, which is very slow. Can I force using the ID index?

possible duplicate of How do I force Postgres to use a particular index? — OMG Ponies
– OMG Ponies, Commented Jul 14, 2011 at 16:07
Hi Alexius, You are correct. I used hash instead of BTree. Once I change into BTree, it is now using the index. Thanks. — Dino
– Dino, Commented Jul 14, 2011 at 16:29

Gedrox · Accepted Answer · 2011-07-14 18:50:08Z

2

It should use the index if the index type is btree and ~~select doesn't fetch more then 30% of all record count (is it true in postgresql as well?)~~ @scott-marlowe says that "..for PostgreSQL the switchover point comes much earlier, somewhere in the 1 to 10% range where it's cheaper to do a sequential scan..".

Try calling REINDEX action maybe?

edited Jul 14, 2011 at 18:50

answered Jul 14, 2011 at 16:09

Gedrox

3,6121 gold badge23 silver badges30 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Scott Marlowe Over a year ago

No, in PostgreSQL indexes are not "covering" which means that you always have to go to the table whether you use the index or not. So, for PostgreSQL the switchover point comes much earlier, somewhere in the 1 to 10% range where it's cheaper to do a sequential scan.

Alexis Wilke Over a year ago

@ScottMarlowe Is the covering still true with the INCLUDE() keyword? Or am I mistaken on the meaning of covering here?

Community · Accepted Answer · 2017-05-23 11:55:32Z

0

I don't use postgresql, but what you need to do is.

Look at the query plan to confirm it's not using any index.
In sybase, you can force a query to use a certain index using an "index hint"

Looks like this question gives you exactly what you want. How do I force Postgres to use a particular index?

edited May 23, 2017 at 11:55

CommunityBot

11 silver badge

answered Jul 14, 2011 at 16:06

user606723

5,1952 gold badges31 silver badges37 bronze badges

Collectives™ on Stack Overflow

Postgresql, Using Index with Between

2 Answers 2

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related