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.aven@jordogskog.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.aven@jordogskog.no'

;
> 
> results in this query plan:



-- 
Adrian Klaver
adrian.klaver@aklaver.com