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

From Pavel Stehule
Subject Re: Performance on JSONB select
Date
Msg-id CAFj8pRAnbGm+S+zKZO4tMWxqChf_vjmLJe5vgmYiRU88NdFiMQ@mail.gmail.com
Whole thread Raw
In response to Re: Performance on JSONB select  (PegoraroF10 <marcos@f10.com.br>)
Responses Re: Performance on JSONB select
List pgsql-general
Hi

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.

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


pgsql-general by date:

Previous
From: Peter Eisentraut
Date:
Subject: Re: Postgres 12: backend crashes when creating non-deterministiccollation
Next
From: PegoraroF10
Date:
Subject: Re: Performance on JSONB select