Performance on JSONB select

From: PegoraroF10 <marcos(at)f10(dot)com(dot)br>
To: pgsql-general(at)postgresql(dot)org
Subject: Performance on JSONB select
Date: 2019-10-02 14:37:43
Message-ID: 1570027063947-0.post@n3.nabble.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

select * from MyTable where
((JsonBField->>'status'='descartada' and
To_Date(JsonBField->'descartada'->>'data','yyyy-mm-dd') > Current_Date) or
(JsonBField->>'status'='contrato' and
To_Date(JsonBField->'contrato'->>'data','yyyy-mm-dd') > Current_Date-7) or
(JsonBField->>'status'='naoatribuido'));

Considering this table has a million records, I would like to use a proper
index, but how can I use an index when using operator >.

--
Sent from: https://www.postgresql-archive.org/PostgreSQL-general-f1843780.html

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Andrew Gierth 2019-10-02 15:24:05 Re: Possible bug: SQL function parameter in window frame definition
Previous Message Evelyn Dibben 2019-10-02 13:04:15 Questions about Partitioned Tables and Indexes