I have an index on the check_timestamp
column, the index gets used by the following query
... where check_timestamp < NOW() - INTERVAL '30 days' ORDER BY check_timestamp NULLS FIRST LIMIT 10
But the index does not get used with the following query
... where (check_timestamp < NOW() - INTERVAL '30 days' OR check_timestamp IS NULL) ORDER BY check_timestamp NULLS FIRST LIMIT 10
Why is this happening? any way around it?
Below is the database schema
id uuid [gen_random_uuid()]
street_db text
check_timestamp timestamptz NULL
last_accessed timestamptz NULL
website text
street text
in_use boolean [false]
zipcode_db_id uuid NULL
NOW() - INTERVAL '30 days'
(notIMMUTABLE
). You could perhaps schedule a job to run at 00:01 every night and index a column which just has the date from 30 days ago - a bit of a hack, but it might do the job? Generated columns might help (immutable problem again?), but they won't be available till release 12. – Vérace Jun 8 at 11:43check_timestamp IS NULL
, but it does get used when I havecheck_timestamp < NOW() - INTERVAL '30 days'
– Arya Jun 8 at 14:56'infinity'
instead of NULL.update the_table set check_timestamp = 'infinity' where check_timestamp is null;
– a_horse_with_no_name Jun 8 at 23:55ORDER BY check_timestamp NULLS FIRST
I then recreated the index with nulls first and it fixed the performance issues. Now it uses the index – Arya Jun 9 at 3:38