PostgreSQL Index Isn't Used on Query

Question

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

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

Do you know how to solve this? Thanks!

Sample data is better presented as formatted text. See here for some tips on how to create nice looking tables. This is also true for the execution plan. Formatted plain text is way easier to read. — user330315
– user330315, Commented Nov 30, 2020 at 7:33
You are returning over 90% of the table. Using the index would probably be slower. — jjanes
– jjanes, Commented Nov 30, 2020 at 7:45

Tim Biegeleisen · Accepted Answer · 2020-11-30 07:56:19Z

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.

Collectives™ on Stack Overflow

PostgreSQL Index Isn't Used on Query

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related