Re: Problems pushing down WHERE-clause to underlying view

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(dot)aven(at)jordogskog(dot)no';

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(dot)aven(at)jordogskog(dot)no'

;
>
> results in this query plan:

--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com