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
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? |