Hi i have this query on postgres 12
select
count(*)
from
modulo730.ods_t_record as r
left join(
select
*
from
modulo730.ods_t_ire as oti
where
cod_azienda = '080239'
and not cod_scenario = 'CUPDT' ) B on
r.cod_evento = B.cod_evento
where
(r.uid_task = '41b7c8ea-4783-4f60-b3f2-980777b86693' )
and r.cod_azienda = '080239'
and r.rec_aggiornato ->> 'annoDocumento' = '2020'
This first version of the query behaves as I expect, it uses the field partition cod_azienda
It does a bitmap index scan on the uid_task field and on the annoDocumento field which is part of a json
The count takes 350ms
Now if I add another condition on the json then I find the last part like this
...
and r.rec_aggiornato ->> 'annoDocumento' = '2020'
and ( r.rec_aggiornato ->> 'tipo_documento' = 'FT' )
The db becomes unable to execute the query, not even plain returns. That is, with only one of the conditions (it also works by inverting the conditions) it executes it correctly if I combine them ... BOOM
Any suggestion? Thanks
This is the plan
Gather (cost=34356.37..34356.48 rows=1 width=8)
Workers Planned: 1
Single Copy: true
-> Aggregate (cost=33356.37..33356.38 rows=1 width=8)
-> Nested Loop Left Join (cost=1410.54..33356.37 rows=1 width=0)
-> Append (cost=1410.54..18597.25 rows=1 width=18)
-> Bitmap Heap Scan on ods_t_record_mch (cost=1410.54..18597.24 rows=1 width=18)
Recheck Cond: (((cod_azienda)::text = '080239'::text) AND ((uid_task)::text = '41b7c8ea-4783-4f60-b3f2-980777b86693'::text))
Filter: (((rec_aggiornato ->> 'annoDocumento'::text) = '2020'::text) AND ((rec_aggiornato ->> 'tipo_documento'::text) = 'FT'::text))
-> Bitmap Index Scan on ods_t_record_mch_cod_azienda_uid_task_idx (cost=0.00..1410.54 rows=14612 width=0)
Index Cond: (((cod_azienda)::text = '080239'::text) AND ((uid_task)::text = '41b7c8ea-4783-4f60-b3f2-980777b86693'::text))
-> Append (cost=0.00..14759.11 rows=1 width=18)
-> Seq Scan on ods_t_ire_mch oti (cost=0.00..14759.10 rows=1 width=18)
Filter: (((cod_scenario)::text <> 'CUPDT'::text) AND ((cod_azienda)::text = '080239'::text) AND ((r.cod_evento)::text = (cod_evento)::text))
This is the script of the first table
CREATE TABLE modulo730.ods_t_record (
uid varchar(36) NOT NULL,
rec_ori jsonb NOT NULL,
insert_time timestamp NOT NULL,
inserter varchar(36) NOT NULL,
modify_time timestamp NULL,
modifier varchar NULL,
stato int4 NOT NULL DEFAULT 0,
uid_task varchar(36) NOT NULL,
cod_evento varchar(128) NOT NULL,
uid_mbi_t_record varchar(36) NOT NULL,
cod_propretario varchar(128) NULL,
dat_evento timestamp NULL,
cod_azienda varchar(128) NOT NULL,
tip_evento varchar(128) NOT NULL,
cod_scenario varchar(128) NOT NULL,
flg_inviato_mef varchar(1) NULL DEFAULT 'N'::character varying,
flg_rre_mef varchar(1) NULL DEFAULT 'N'::character varying,
rec_aggiornato jsonb NULL,
flg_modo varchar(1) NULL,
flg_inviato_reg varchar(1) NULL,
flg_rre_reg varchar(1) NULL,
flg_inviato_dwh varchar(1) NULL,
rec_hash text NULL,
stato_classificazione varchar(50) NULL,
flg_classificazione varchar(1) NULL,
CONSTRAINT ods_t_record_key PRIMARY KEY (cod_azienda, tip_evento, cod_scenario, cod_evento)
)
PARTITION BY LIST (cod_azienda);
CREATE INDEX idx_ods_t_record_cliente ON ONLY modulo730.ods_t_record USING btree (cod_azienda, ((rec_aggiornato ->> 'cod_cliente_competenza'::text)));
CREATE INDEX idx_ods_t_record_dat_reg ON ONLY modulo730.ods_t_record USING btree (cod_azienda, ((rec_aggiornato ->> 'data_registrazione'::text)));
CREATE INDEX idx_ods_t_record_dat_ult_inc ON ONLY modulo730.ods_t_record USING btree (cod_azienda, ((rec_aggiornato ->> 'dataUltimoIncasso'::text)));
CREATE INDEX idx_ods_t_record_des_reason ON ONLY modulo730.ods_t_record USING btree (cod_azienda, (((rec_aggiornato -> 'classificationReason'::text) ->> 'des_reason'::text)));
CREATE INDEX idx_ods_t_record_doc_coll ON ONLY modulo730.ods_t_record USING btree (cod_azienda, ((rec_aggiornato ->> 'nr_Documento_Collegamento'::text)));
CREATE INDEX idx_ods_t_record_evsan ON ONLY modulo730.ods_t_record USING btree (cod_azienda, ((rec_aggiornato ->> 'codEventoSanitario'::text)));
CREATE INDEX idx_ods_t_record_note_pres ON ONLY modulo730.ods_t_record USING btree (cod_azienda, ((rec_aggiornato ->> 'note_prestazione'::text)));
CREATE INDEX idx_ods_t_record_nr_doc ON ONLY modulo730.ods_t_record USING btree (cod_azienda, ((rec_aggiornato ->> 'nr_Documento'::text)));
CREATE UNIQUE INDEX ods_t_record_cod_azienda_idx ON ONLY modulo730.ods_t_record USING btree (cod_azienda, uid);
CREATE INDEX ods_t_record_cod_propretario_idx ON ONLY modulo730.ods_t_record USING btree (cod_azienda, cod_propretario);
CREATE INDEX ods_t_record_uid_task_idx ON ONLY modulo730.ods_t_record USING btree (cod_azienda, uid_task);
the second table
CREATE TABLE modulo730.ods_t_ire (
uid varchar(36) NOT NULL,
inserter varchar(128) NULL,
insert_time timestamp NULL,
modifier varchar(128) NULL,
modify_time timestamp NULL,
uid_task varchar(36) NULL,
cod_evento varchar(128) NOT NULL,
cod_proprietario varchar(128) NULL,
cod_azienda varchar(128) NOT NULL,
tip_evento varchar(128) NOT NULL,
cod_scenario varchar(128) NOT NULL,
data_invio timestamp NULL,
flg_inviato varchar(1) NULL,
flg_ritorno varchar(1) NULL,
rec_inviato_ori jsonb NULL,
rec_inviato text NULL,
rec_ritorno text NULL,
uid_sent_file varchar(36) NULL,
flow_state varchar(50) NULL,
cod_error varchar NULL,
cod_evento_inviato varchar(128) NULL,
dat_evento timestamp NULL,
cod_evento_collegato varchar NULL,
dat_evento_collegato timestamp NULL,
ticket varchar NULL,
flg_cancellazione varchar NULL,
ticket_cancellazione varchar NULL,
cod_destinatario varchar NOT NULL,
CONSTRAINT ods_t_ire_pk PRIMARY KEY (cod_azienda, tip_evento, cod_scenario, cod_destinatario, cod_evento)
)
PARTITION BY LIST (cod_azienda);
CREATE UNIQUE INDEX ods_t_ire_cod_azienda_idx ON ONLY modulo730.ods_t_ire USING btree (cod_azienda, uid);
As a further indication below, the plain considering only one condition on the attributes of the json, are attributes with low cardinality. I'm not an expert but what I see is that in the faster case it performs a sort of parallel computation
Finalize Aggregate (cost=33423.14..33423.15 rows=1 width=8)
-> Gather (cost=33422.93..33423.14 rows=2 width=8)
Workers Planned: 2
-> Partial Aggregate (cost=32422.93..32422.94 rows=1 width=8)
-> Parallel Hash Left Join (cost=15479.31..32422.85 rows=30 width=0)
Hash Cond: ((r.cod_evento)::text = (oti.cod_evento)::text)
-> Parallel Append (cost=1410.56..18353.88 rows=30 width=18)
-> Parallel Bitmap Heap Scan on ods_t_record_mch r (cost=1410.56..18353.73 rows=30 width=18)
Recheck Cond: (((cod_azienda)::text = '080239'::text) AND ((uid_task)::text = '41b7c8ea-4783-4f60-b3f2-980777b86693'::text))
Filter: ((rec_aggiornato ->> 'tipo_documento'::text) = 'FT'::text)
-> Bitmap Index Scan on ods_t_record_mch_cod_azienda_uid_task_idx (cost=0.00..1410.54 rows=14612 width=0)
Index Cond: (((cod_azienda)::text = '080239'::text) AND ((uid_task)::text = '41b7c8ea-4783-4f60-b3f2-980777b86693'::text))
-> Parallel Hash (cost=13160.38..13160.38 rows=72669 width=18)
-> Parallel Append (cost=0.00..13160.38 rows=72669 width=18)
-> Parallel Seq Scan on ods_t_ire_mch oti (cost=0.00..12797.04 rows=72669 width=18)
Filter: (((cod_scenario)::text <> 'CUPDT'::text) AND ((cod_azienda)::text = '080239'::text))