CODEWITHSUNDEEP
postgresqlperformanceindexing
JK.
JK.

Reputation: 21808

Checking if a postgres index is up to date?

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

Upvotes: 1

Views: 5132

Answers (1)

Kirill Petrov
Kirill Petrov

Reputation: 71

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.

Upvotes: 2

Related Questions