Search Postgresql Archives

How to push predicate down

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

 




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




[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