0

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
  • Add the whole query please and the table definition. – ypercubeᵀᴹ Jun 8 at 7:01
  • PG has expression [indexes]( postgresql.org/docs/11/indexes-expressional.html). The problem is that they won't work with an expression like NOW() - INTERVAL '30 days' (not IMMUTABLE). 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:43
  • @Vérace I did some tests, the index does not get used if I have check_timestamp IS NULL, but it does get used when I have check_timestamp < NOW() - INTERVAL '30 days' – Arya Jun 8 at 14:56
  • For this type of query it's better to not have NULL values in the column (precisely because of non-efficient plans). To indicate "no end" in a timestamp column you can alternatively store the value '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:55
  • @a_horse_with_no_name I did more testing and I found another major slow down was caused by ORDER 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
2

In general PostgreSQL is capable of using an index in this situation. It can scan the index two times and combine them with a BitmapOr.

The reason it cannot or has chosen not to do this in your particular case would depend on the particulars of your case, which you are not sharing with us. Perhaps it just thinks the alternative plan will be cheaper.

  • I did some tests, the index does not get used if I have check_timestamp IS NULL, but it does get used when I have check_timestamp < NOW() - INTERVAL '30 days' the index is not being used for checking NULL – Arya Jun 8 at 14:57
  • I've also done some tests. The index does get used to test NULLs in general. If you want me to test your particulars, you will have to share them. – jjanes Jun 8 at 14:58
  • I have added the database schema – Arya Jun 8 at 15:12
  • I think what is causing the performance issue is ORDER BY check_timestamp NULLS FIRST right? – Arya Jun 8 at 19:52
  • creating the index with putting NULLS first fixed the performance issue – Arya Jun 8 at 20:01

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.