CODEWITHSUNDEEP
sqlpostgresqlindexing
Dino
Dino

Reputation: 821

Postgresql, Using Index with Between

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?

Upvotes: 2

Views: 5548

Answers (2)

Gedrox
Gedrox

Reputation: 3612

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?

Upvotes: 2

user606723
user606723

Reputation: 5145

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?

Upvotes: 0

Related Questions