Terrible plan choice for view with distinct on clause

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

 



Hey all, first off, Postgres version 9.4.4 (also tested on 9.5 beta).

I have been having a pretty hard time getting a view of mine to play nice with any other queries I need it for.

I have a few tables you'd need to know about to understand why i'm doing what i'm doing.

First thing is we have a "contract_item", it can have either a product or a grouping of products (only one or the other, not both) on it, and a few extra attributes.

The groupings are defined in a hierarchy, and they can have many of these groupings or products on a single "contract", but only unique records per product or grouping.

Now when it comes down to finding what the extra attributes that are valid on a contract are for the specific product you're looking for, the most relevant is the product being on the contract directly. If the product doesn't exist, we choose the lowest level grouping that is defined on the contract that contains that product.

The view I am having trouble with is able to push down it's where clause when the id's are directly specified like so:
SELECT *
FROM contract_product cp
WHERE cp.contract_id = '16d6df05-d8a0-4ec9-ae39-f4d8e13da597'
AND cp.product_id = '00c117d7-6451-4842-b17b-baa44baa375f';

But the where clause or join conditions are not pushed down in these cases (which is how I need to use the view): 
SELECT *
FROM contract_product cp
WHERE EXISTS (
SELECT 1
WHERE cp.contract_id = '16d6df05-d8a0-4ec9-ae39-f4d8e13da597'
AND cp.product_id = '00c117d7-6451-4842-b17b-baa44baa375f'
);

or 

SELECT *
FROM contract_product cp
INNER JOIN (
SELECT '16d6df05-d8a0-4ec9-ae39-f4d8e13da597'::uuid as contract_id, '00c117d7-6451-4842-b17b-baa44baa375f'::uuid as product_id
) p
ON cp.contract_id = p.contract_id
AND cp.product_id = p.product_id;


The definition of the view i'm having trouble with:
CREATE OR REPLACE VIEW contract_product AS 
SELECT DISTINCT ON (ci.contract_id, p.product_id) 
  ci.contract_item_id,
  ci.contract_id,
  p.product_id,
  ci.uom_type_id,
  ci.rebate_direct_rate,
  ci.decimal_model,
  ci.rebate_deviated_value,
  ci.rebate_deviated_type
FROM contract_item ci
LEFT JOIN grouping_hierarchy gh
  ON gh.original_grouping_id = ci.grouping_id
  AND NOT (EXISTS (
  SELECT 1
  FROM contract_item cig
  WHERE cig.contract_id = ci.contract_id
  AND gh.grouping_id = cig.grouping_id
  AND cig.grouping_id <> ci.grouping_id)
  )
LEFT JOIN product_grouping pg
  ON pg.grouping_id = gh.grouping_id
  AND NOT (EXISTS (
  SELECT 1
  FROM contract_item cip
  WHERE cip.contract_id = ci.contract_id
  AND pg.product_id = cip.product_id)
  )
JOIN product p
  ON p.product_id = COALESCE(ci.product_id, pg.product_id)
ORDER BY ci.contract_id, p.product_id, gh.level;

That view references another view to make it easy to find the correct level in my hierarchy, so here is the definition for that:
CREATE OR REPLACE VIEW grouping_hierarchy AS 
 WITH RECURSIVE groupings_list(original_grouping_id, parent_grouping_id, grouping_id) AS (
         SELECT pg.grouping_id AS original_grouping_id,
            pg.parent_grouping_id,
            pg.grouping_id,
            0 AS level
           FROM grouping pg
        UNION ALL
         SELECT gl.original_grouping_id,
            cg.parent_grouping_id,
            cg.grouping_id,
            gl.level + 1
           FROM groupings_list gl
             JOIN grouping cg ON cg.parent_grouping_id = gl.grouping_id
          WHERE cg.active_ind = true
        )
 SELECT groupings_list.original_grouping_id,
    groupings_list.parent_grouping_id,
    groupings_list.grouping_id,
    groupings_list.level
   FROM groupings_list;


And here are the query plans (in order) for those three queries:
http://explain.depesz.com/s/YCee
http://explain.depesz.com/s/1SE2
http://explain.depesz.com/s/ci7

Any help would be greatly appreciated on how to speed this up, or if i'm doing something Postgres just doesn't like and what an alternative method would be.

Thanks,
-Adam

[Postgresql General]     [Postgresql PHP]     [PHP Users]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Yosemite]

  Powered by Linux