On 2/15/19 12:43 PM, Nicklas Avén wrote:
> I have not had chance to fully go through all of below. Some
questions/suggestions:
>
> 1) Thanks for the formatted queries. If I could make a suggestion,
when aliasing could you include AS. It would make finding what l.*
refers to easier for those of us with old eyes:)
>
Yes, of course, sorry :-)
> 2) t4e_contractor_id is in the shiny_adm.contractor_access table?
> If not where?
Yes, sorry again, it is there
>
> 3) What is the schema for shiny_adm.contractor_access?
> In particular what indexes are on it?
>
shiny_adm.contractor_access looks like this:
CREATE TABLE shiny_adm.contractor_access
(
machine_key text,
t4e_contractor_id text,
active integer DEFAULT 1,
id serial NOT NULL,
CONSTRAINT contractor_access_pkey PRIMARY KEY (id),
CONSTRAINT contractor_unique UNIQUE (machine_key, t4e_contractor_id),
CONSTRAINT co_check_t4e_co_email CHECK
(utils.verify_email(t4e_contractor_id))
)
CREATE INDEX idx_contractor
ON shiny_adm.contractor_access
USING btree
(t4e_contractor_id COLLATE pg_catalog."default");
CREATE INDEX idx_contractor_mk
ON shiny_adm.contractor_access
USING btree
(machine_key COLLATE pg_catalog."default");
I tried to format the below a little better with AS and some more
consistent indents.
I also, in the first query, changed the where clause to filter on
machine_key in table contractor _access. Just to illustrate the problem
better.
Both queries filter on the same table which is joined the same way. But
in the second example the where clause is not pushed to the subquery l
Thanks a lot for looking into it
Nicklas
Next query, the slow one that calculates the whole dataset:
EXPLAIN ANALYZE
SELECT
l.machine_key,
o.object_name,
o.sub_object_name,
o.object_user_id,
o.sub_object_user_id,
o.start_date AS object_start_date,
s.species_group_name,
p.product_group_name,
l.m3_sub AS volume_m3sub,
l.number_of_logs,
mi.basemachine_manufacturer,
mi.basemachine_model
FROM shiny_adm.contractor_access ci join
(
SELECT
hl.contractor_id,
hl.machine_key,
hl.operator_key,
hl.object_key,
hl.sub_object_key,
date(hl.harvest_date) AS harvest_date,
hl.species_group_key,
hl.product_key,
sum(hl.m3_sub) AS m3_sub,
count(*) AS number_of_logs
FROM
version_union_tables_r02.harvester_logs AS hl
GROUP BY
hl.machine_key, hl.contractor_id, hl.operator_key,
hl.object_key, hl.sub_object_key, (date(hl.harvest_date)),
hl.species_group_key, hl.product_key
) AS l on l.machine_key=ci.machine_key
LEFT JOIN version_union_tables_r02.machine_info AS mi ON
l.machine_key::text = mi.machine_key::text
LEFT JOIN version_union_tables_r02.objects AS o ON
l.machine_key::text = o.machine_key::text AND l.object_key =
o.object_key AND l.sub_object_key = o.sub_object_key
LEFT JOIN version_union_tables_r02.products AS p ON
l.machine_key::text = p.machine_key::text AND l.product_key = p.product_key
LEFT JOIN version_union_tables_r02.species AS s ON
l.machine_key::text = s.machine_key::text AND l.species_group_key =
s.species_group_key
WHERE t4e_contractor_id = 'nicklas.aven@xxxxxxxxxxxxx';
To make it apples to apples try changing above to be more like first query:
...
AS l
LEFT JOIN version_union_tables_r02.machine_info AS mi ON
l.machine_key::text = mi.machine_key::text
LEFT JOIN version_union_tables_r02.objects AS o ON
l.machine_key::text = o.machine_key::text AND l.object_key =
o.object_key AND l.sub_object_key = o.sub_object_key
LEFT JOIN version_union_tables_r02.products AS p ON
l.machine_key::text = p.machine_key::text AND l.product_key = p.product_key
LEFT JOIN version_union_tables_r02.species AS s ON
l.machine_key::text = s.machine_key::text AND l.species_group_key =
s.species_group_key
JOIN shiny_adm.contractor_access AS ci ON l.machine_key=ci.machine_key
WHERE t4e_contractor_id = 'nicklas.aven@xxxxxxxxxxxxx'
;
results in this query plan:
--
Adrian Klaver
adrian.klaver@xxxxxxxxxxx
