2

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?

OMG Ponies
  • 325,700
  • 82
  • 523
  • 502
Dino
  • 781
  • 3
  • 14
  • 32

2 Answers2

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?

Gedrox
  • 3,592
  • 1
  • 21
  • 29
  • 2
    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. – Scott Marlowe Jul 14 '11 at 17:36
  • @ScottMarlowe Is the covering still true with the `INCLUDE()` keyword? Or am I mistaken on the meaning of covering here? – Alexis Wilke Jan 25 '23 at 20:08
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?

Community
  • 1
  • 1
user606723
  • 4,918
  • 2
  • 27
  • 34