Re: Performance on JSONB select - Mailing list pgsql-general

I think I solved my problem. I didn´t know it was possible but I´ve created
an almost dynamic index, because it´ll index for status and corresponding
datevalue of an object with that status value.

Postgres, I love you.

Just one B-Tree index with 2 fields, Status and DateTime of that respective
Status object. My Json has always a status and a respective object of that
status with other values. So I know if it has that status it has a Date
Value or not on its correspondent object.

{
    "status": "visitadescartada",
    "contrato": {},
    "atribuido": {"datevalue": "2019-09-05 14:47:11.149095-03"},
    "trabalhando": {},
    "visitaagendada": {"datevalue": "2019-09-05
15:06:24.255548-03","caption": "Agendado"},
    "visitadescartada": {"datevalue": "2019-09-12
11:47:17.45782-03","caption": "Desagendado"},
    "digitacaodescartada": {}
}

create index IndexByStatusAndDate on MyTable (
(JsonBField->>$$status$$),
castimmutabletimestamp(JsonBField->(JsonBField->>$$status$$)->>'datevalue'))
where (JsonBField ? $$status$$);

select * from MyTable where (JsonBField ? $$status$$) and
case when JsonBField->>$$status$$=$$cadastrodescartado$$ then
castimmutabletimestamp(JsonBField->(JsonBField->>$$status$$)->>'datevalue')
> castimmutabletimestamp($$2019-10-01$$)
     when JsonBField->>$$status$$=$$visitadescartada$$ then
castimmutabletimestamp(JsonBField->(JsonBField->>$$status$$)->>'datevalue')
> castimmutabletimestamp($$2019-10-01$$)
     when JsonBField->>$$status$$=$$contrato$$ then
castimmutabletimestamp(funilvendas->(JsonBField->>$$status$$)->>'data') >
castimmutabletimestamp($$2019-10-01$$)
     when JsonBField->>$$status$$=$$naoatribuido$$ then True end;

And performance now is great because I´m using both fields on index.



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