Hi I have the following query to make a report from certain date to the begin of the DB.
explain (analyze, buffers)
select
(
select array_to_json(array_agg(row_to_json(v)))
from (
select cl.fkp as fkpueblo,
cr.pk::varchar(255),
(
(
cr.creado at TIME zone 'Mexico/General' at TIME zone 'UTC'
)::date || ' ' || cl.n || ' ' || cl.paterno || ' ' || cl.materno
) as nombre,
cr.montocredito,
(
cr.creado at TIME zone 'Mexico/General' at TIME zone 'UTC'
)::date as creado,
(
select array_to_json(array_agg(row_to_json(u)))
from (
select h.pk::varchar(255),
h.concepto as nombre,
h.monto,
h.tipo,
(
h.fecha at TIME zone 'Mexico/General' at TIME zone 'UTC'
)::date as fecha
from historial h
where h.fkcr = cr.pk
and (
h.fecha at TIME zone 'Mexico/General' at TIME zone 'UTC'
)::date < '2022-07-30'
and h.del = 0
and (
select COUNT(h.pk)
from historial h
where h.fkcr = cr.pk
and h.tipo in (3, 6, 9, 10)
and h.del = 0
) > 10
order by h.fkcr,
h.fecha asc
) as u
) as historiales
from credito cr,
cliente cl
where cr.del = 0
and cr.fkc = cl.pk
and cl.fkp = p.pk
and (
cr.creado at TIME zone 'Mexico/General' at TIME zone 'UTC'
)::date < '2022-07-30'
and (
cr.fecha_cierre is null
or (
cr.fecha_cierre is not null
and cr.fecha_cierre >= '2022-07-23'
)
)
order by cr.creado asc
) as v
) as creditos,
(
select array_to_json(array_agg(row_to_json(u)))
from (
select csm.tipo_monto
from corte_semanal cs,
corte_semanal_montos csm,
corte_pueblo_padre pp
where pp.del = 0
and p.pk = pp.fkpueblo
and pp.fksupervisor = cs.fksupervisor
and cs.pk = csm.fkcorte_semanal
and csm.del = 0
and cs.del = 0
and p.del = 0
and (
cs.fecha_corte at TIME zone 'Mexico/General' at TIME zone 'UTC'
)::date >= '2022-07-23'
and (
cs.fecha_corte at TIME zone 'Mexico/General' at TIME zone 'UTC'
)::date < '2022-07-30'
and csm.tipo_monto < 4
order by csm.tipo desc,
csm.tipo_monto asc,
csm.concepto asc
) as u
) as montos
from pueblo p,
ruta r
where p.del = 0
and p.fkr = r.pk
order by r.ruta,
p.pueblo asc
And this is the planning
Sorry, I had to place it on Pastebin since it was more than the body limit of the post.
Using DBeaver I exported the planning as a graph image
As you can see, this is an expensive query to run so I went a try to optimize it.
When I started a few days ago, I noticed that some nodes had an External Merge on Disk plan so I checked the work_mem and it was on default, 4MB and so I changed it to 1GB but it didn't affect significantly to the overall execution.
I used this to change the work_mem
ALTER ROLE <user> SET work_mem TO '1GB';
Indexes
In this query there are two major tables:
historial and corte_semanal_montos on which historial has 1,882,074 rows and corte_semanal_montos 106,705 but at the end of the query there are about 1,084 rows due to aggregations.
publo has 1,141 rows and rutas only 33 rows.
Historial is the biggest table on the query by a large amount to I tried to make some indexes on where columns.
create index idx_del_zero on historial(del) where del = 0;
create index idx_tipo on historial(tipo) where tipo in (3, 6, 9, 10);
create index idx_fkcr_fecha on historial(fkcr, fecha);
CREATE INDEX idx_expr ON historial ((fecha at TIME zone 'Mexico/General' at TIME zone 'UTC'));
create index idx_del_zero_p on historial(del) where del = 0;
create index idx_del_zero_csm on corte_semanal_montos(del) where del = 0;
But again, it didn't affect significantly to the overall execution time.
Right now I don't know how can I proceed on making this query faster.
My postgreSQL version is
PostgreSQL 9.5.23 on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 5.4.0-6ubuntu1~16.04.12) 5.4.0 20160609, 64-bit