Re: Performance on JSONB select

Hi

so 5. 10. 2019 v 13:34 odesílatel PegoraroF10 <marcos(at)f10(dot)com(dot)br> napsal:

> 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.
>

you created multicolumn functional index (there are no any dynamic index :))

Pavel

>
> 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
>
>
>