Range contains element filter not using index of the element column

From: Lauri Kajan <lauri(dot)kajan(at)gmail(dot)com>
To: pgsql-general(at)lists(dot)postgresql(dot)org
Subject: Range contains element filter not using index of the element column
Date: 2019-11-27 09:32:10
Message-ID: CAKWoFMJTND5-qt9bKkj7c=j10xUGhsYTSbtK57sSDxBcc=Hu=A@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Hi all,
I'm wondering if there are anything to do to utilize a index when doing a
range contains element query. I have tested this with 9.6 and 12.0.

I have a table with a timestamp column that has a btree index.
I would like to do a query:
SELECT * FROM table WHERE ts <@ tsrange($1, $2, '(]');
The index is not used and a seq scan is done instead.

To use the index correctly I have to do the query like this:
SELECT * FROM table WHERE ($1 IS null OR $1 < ts) AND ($2 IS null OR ts <=
$2);
I like the <@ syntax more. Is there something I can do differently? Maybe a
different type of index instead?

Thanks,

-Lauri

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Dmytro Zhluktenko 2019-11-27 09:54:13 RE: Re: Postgres Full Text Search Jsonb Array column does not search for first row
Previous Message Andrei Zhidenkov 2019-11-27 08:42:10 Re: Weird seqscan node plan