How to push predicate down

From: salah jubeh <s_jubeh(at)yahoo(dot)com>
To: pgsql <pgsql-general(at)postgresql(dot)org>
Subject: How to push predicate down
Date: 2012-01-26 13:51:18
Message-ID: 1327585878.63499.YahooMailNeo@web161504.mail.bf1.yahoo.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Hello Guys,

In the past  I had a view defined as follows

CREATE view abcd as
SELECT whatever ...... --- query1

Some business requierments came up and I had to change it like this

 
CREATE view abcd as
SELECT whatever ...... --- query1
UNION
SELECT whatever ......---- query2

Now I have a problem in the time for calculating the query when using a predicate

-- this time makes sense
SELECT * FROM abcd

Query time ( Past)   = X
Query time (current)  = X +Y -- (Y is the time which introduced by query2)

But If I run the query

-- This does not make sense
SELECT * FROM abcd  where predicate = 'predicate_a'
Query time ( Past)   = 1 /10 * X
Query time (current) = X + Y  -- I assume the time should be 1/10*X + Y

--Note,  Y is much smaller than X  so I do not care too much about it, so X is the dominant factor

I had a look on the execution plane and the predicate 'predicate_a' was pushed up  on the top  in the current situation

i.e.

In the past  the excution plane was like this

Filter using the predicate 'predicate_a' and then do the calculation of the rest of  query1, this is why the time is reduced to 1/10 * X

Now the execution plan is like this

Calculate query1  and  then calculate query2 and then Union the result and finally   filter using predicate 'predicate_a',

Why it is not like this

Filter  first using the predicate 'predicate_a' when calculating query1
Filter  first using the predicate 'predicate_a' when calculating query2
Then do the union 

Sorry I did not post the execution plan but it is more than 5000 line

Regards

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Jon Smark 2012-01-26 14:37:49 Let-bindings in SQL statements
Previous Message Chris Angelico 2012-01-26 11:56:09 Re: Best way to create unique primary keys across schemas?