PostgreSQL Index Isn't Used on Query

user_12726150 0
Viewed 51

so I have PostgreSQL database with table that track the movement and fuel level of equipment with millions of rows

Record

and to make query faster when I create index on time column with this command:

create index gpsapi__index__time on gpsapi (time);

When I tried to run simple command with "EXPLAIN ANALYZE" like this

EXPLAIN ANALYZE
SELECT *
FROM gpsapi g
WHERE g.time >= NOW() - '1 months'::interval;

it doesn't show that the query uses the index I created output

Do you know how to solve this? Thanks!

postgresql sql
1 Answers

If you read the execution plan closely, you'll see that Postgres is telling you that out of about 6 million records, 5.5 million matched (> 90%). Based on statistics, it is likely that Postgres realized that it would be returning a large percentage of total records in the table, and that it would be faster to just forgo using the index and scan the entire table.

The concept to understand here is that, while the index you defined does potentially let Postgres throw away non matching records very quickly, it actually increases the time needed to lookup the values in SELECT *. The reason for this is that, upon hitting the leaf node in the index, Postgres must then do a seek back to the clustered index/table to find the column values. Assuming your query would return most of the table, it would be faster to just scan the table directly.

This being said, there is nothing at all inherently wrong with your index. If your query used a more narrow range, or searched for a specific timestamp, such that the expected result set were sufficiently small, then Postgres likely would have used the index.

user_18632290
Related