so 5. 10. 2019 v 13:34 odesílatel PegoraroF10 <marcos@f10.com.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.
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.