Search Postgresql Archives

Re: Problems pushing down WHERE-clause to underlying view

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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




[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]

  Powered by Linux