Checking if a postgres index is up to date?

Question

Given a table with around 5 million records added per month, this simple query has vastly different run times from the first execution to the 2nd:

select count(*) from my_table where recorded_at > '2018-01-24 23:59:59'

There is an index on the field recorded_at.

CREATE INDEX my_table__recorded_at ON my_table(recorded_at);

Does this difference in execution time indicate that the index is not being updated correctly?

Its the same for other dates:

select count(*) from my_table where recorded_at > '2018-02-07 23:59:59'

This is running on AWS Aurora postgres with an r4.xlarge instance

You can check whether the index is being used with EXPLAIN SELECT count(*) FROM my_table WHERE recorded_at > '2018-02-07 23:59:59'; That said, I strongly suspect what you're seeing is the effect of caching the table and/or index. First time it runs, it reads everything in and caches things. Second time, it just has to go to the cache. Finally, unless Aurora is wildly different from postgres proper, indexes always get updated. — jmelesky
– jmelesky, Commented Mar 16, 2018 at 23:04
This is a rather typical situation that the first execution of a query is much slower than the next ones. This is due to Postgres and/or file system cache. It is unlikely on this basis to believe that the data is damaged or obsolete in any way. — klin
– klin, Commented Mar 16, 2018 at 23:05

Sebastián Palma · Accepted Answer · 2021-02-26 21:17:18Z

2

Indexes are up to date just after every DML.

You can use:

EXPLAIN (analyze, verbose, buffers)
SELECT COUNT(*)
FROM my_table
WHERE recorded_at > '2018-01-24 23:59:59'

to get more information about cache hits, I/O timing.

Also there is the REINDEX command if you think, that your index is corrupted.

Maybe the pg_prewarm module could be interesting.

Sebastián Palma

33.6k6 gold badges45 silver badges65 bronze badges

answered Mar 17, 2018 at 3:46

Kirill Petrov

713 bronze badges

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

1 Comment

Hmmm.... the indexes can be correct, but they can be unbalanced after bulk inserts on many DBMSes.