Thread: How to push predicate down
Hello Guys,
In the past I had a view defined as follows
CREATE view abcd as
SELECT whatever ...... --- query1
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
salah jubeh wrote: > > 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 1. You sure you need UNION and not UNION ALL? 2. Can you post more detail example? For example: select anything from first_table union select anything from second_table where anything == something; This way WHERE clause is a part of second subselect and will not be propagated to the first one. -- Sphinx of black quartz judge my vow.
Sorry, The scenario, that I posted was not correct. I have traced it and the union was not the problem, As I said the query excusion plan is over 5000 line. I have created a scenario which similar to the scenario causes the problem I have.
CREATE TABLE TEST (
ID SERIAL PRIMARY KEY,
COL1 TEXT,
COL2 INT,
COL3 TEXT
);
CREATE TABLE TEST_REMOVE (
COL1 TEXT,
COL3 TEXT
);
INSERT INTO TEST (COL1, COL2, COL3) VALUES ('foo', 7 , 'Bar');
INSERT INTO TEST (COL1, COL2, COL3) VALUES ('foo', 8 , 'Bar');
INSERT INTO TEST (COL1, COL2, COL3) VALUES ('Boo', 8 , 'Far');
INSERT INTO TEST_REMOVE (COL1, COL3) VALUES ('foo', 'Bar');
CREATE OR REPLACE VIEW REMOVED_TEST_ENTRIES AS
SELECT T.*
FROM TEST T JOIN TEST_REMOVE TR ON(T.COL1 = TR.COL1 AND T.COL3 = TR.COL3)
CREATE OR REPLACE VIEW TEST_ENTRIES AS
SELECT * FROM TEST
EXCEPT
SELECT * FROM REMOVED_TEST_ENTRIES
;
EXPLAIN ANALYZE SELECT * FROM TEST_ENTRIES WHERE COL3 = 'BAR';
ID SERIAL PRIMARY KEY,
COL1 TEXT,
COL2 INT,
COL3 TEXT
);
CREATE TABLE TEST_REMOVE (
COL1 TEXT,
COL3 TEXT
);
INSERT INTO TEST (COL1, COL2, COL3) VALUES ('foo', 7 , 'Bar');
INSERT INTO TEST (COL1, COL2, COL3) VALUES ('foo', 8 , 'Bar');
INSERT INTO TEST (COL1, COL2, COL3) VALUES ('Boo', 8 , 'Far');
INSERT INTO TEST_REMOVE (COL1, COL3) VALUES ('foo', 'Bar');
CREATE OR REPLACE VIEW REMOVED_TEST_ENTRIES AS
SELECT T.*
FROM TEST T JOIN TEST_REMOVE TR ON(T.COL1 = TR.COL1 AND T.COL3 = TR.COL3)
CREATE OR REPLACE VIEW TEST_ENTRIES AS
SELECT * FROM TEST
EXCEPT
SELECT * FROM REMOVED_TEST_ENTRIES
;
EXPLAIN ANALYZE SELECT * FROM TEST_ENTRIES WHERE COL3 = 'BAR';
" Filter: (test_entries.col3 = 'BAR'::text)"
" -> SetOp Except (cost=195.40..205.61 rows=82 width=72) (actual time=0.134..0.135 rows=1 loops=1)"
" -> Sort (cost=195.40..197.44 rows=817 width=72) (actual time=0.119..0.124 rows=5 loops=1)"
" Sort Key: "*SELECT* 1".id, "*SELECT* 1".col1, "*SELECT* 1".col2, "*SELECT* 1".col3"
" Sort Method: quicksort Memory: 25kB"
" -> Append (cost=0.00..155.88 rows=817 width=72) (actual time=0.016..0.098 rows=5 loops=1)"
" -> Subquery Scan "*SELECT* 1" (cost=0.00..26.00 rows=800 width=72) (actual time=0.014..0.024 rows=3 loops=1)"
" -> Seq Scan on test (cost=0.00..18.00 rows=800 width=72) (actual time=0.009..0.013 rows=3 loops=1)"
" -> Subquery Scan "*SELECT* 2" (cost=117.09..129.88 rows=17 width=72) (actual time=0.045..0.061 rows=2 loops=1)"
" -> Merge Join (cost=117.09..129.71 rows=17 width=72) (actual time=0.043..0.054 rows=2 loops=1)"
" Merge Cond: ((t.col1 = tr.col1) AND (t.col3 = tr.col3))"
" -> Sort (cost=56.58..58.58 rows=800 width=72) (actual time=0.022..0.025 rows=3 loops=1)"
" Sort Key: t.col1, t.col3"
" Sort Method: quicksort Memory: 25kB"
" -> Seq Scan on test t (cost=0.00..18.00 rows=800 width=72) (actual time=0.002..0.005 rows=3 loops=1)"
" -> Sort (cost=60.52..62.67 rows=860 width=64) (actual time=0.010..0.012 rows=1 loops=1)"
" Sort Key: tr.col1, tr.col3"
" Sort Method: quicksort Memory: 25kB"
" -> Seq Scan on test_remove tr (cost=0.00..18.60 rows=860 width=64) (actual time=0.003..0.004 rows=1 loops=1)"
"Total runtime: 0.213 ms"
From: Volodymyr Kostyrko <c.kworr@gmail.com>
To: salah jubeh <s_jubeh@yahoo.com>
Cc: pgsql <pgsql-general@postgresql.org>
Sent: Thursday, January 26, 2012 3:49 PM
Subject: Re: [GENERAL] How to push predicate down
salah jubeh wrote:
>
> 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
1. You sure you need UNION and not UNION ALL?
2. Can you post more detail example?
For example:
select anything from first_table
union
select anything from second_table
where anything == something;
This way WHERE clause is a part of second subselect and will not be
propagated to the first one.
--
Sphinx of black quartz judge my vow.
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
salah jubeh <s_jubeh@yahoo.com> writes:
> Sorry,� The scenario, that I posted was not correct. I have traced it and the union was not the problem, As I said
thequery excusion plan is over 5000 line. I have created a scenario which similar to the scenario causes the problem I
have.
> [ query uses EXCEPT not UNION ]
Hmm. The code explicitly won't push conditions down through an EXCEPT:
* 3. If the subquery contains EXCEPT or EXCEPT ALL set ops we cannot push
* quals into it, because that could change the results.
I remember coming to the conclusion that this is safe for
UNION/INTERSECT but not EXCEPT, but right at the moment I'm not sure why
I thought that --- it seems like a qual that suppresses specific rows
should suppress all matching copies.
regards, tom lane
I think in my case, It is safe to push the predicate down. Can someone please, examine the behavior of other databases.If it behaves like postgres, I will assume there are some cases where it can lead to wrong result set. I tried SQL server but my windows refuses it :-)
Regards
From: Tom Lane <tgl@sss.pgh.pa.us>
To: salah jubeh <s_jubeh@yahoo.com>
Cc: Volodymyr Kostyrko <c.kworr@gmail.com>; pgsql <pgsql-general@postgresql.org>
Sent: Thursday, January 26, 2012 5:47 PM
Subject: Re: [GENERAL] How to push predicate down
salah jubeh <s_jubeh@yahoo.com> writes:
> Sorry, The scenario, that I posted was not correct. I have traced it and the union was not the problem, As I said the query excusion plan is over 5000 line. I have created a scenario which similar to the scenario causes the problem I have.
> [ query uses EXCEPT not UNION ]
Hmm. The code explicitly won't push conditions down through an EXCEPT:
* 3. If the subquery contains EXCEPT or EXCEPT ALL set ops we cannot push
* quals into it, because that could change the results.
I remember coming to the conclusion that this is safe for
UNION/INTERSECT but not EXCEPT, but right at the moment I'm not sure why
I thought that --- it seems like a qual that suppresses specific rows
should suppress all matching copies.
regards, tom lane
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
I wrote: > Hmm. The code explicitly won't push conditions down through an EXCEPT: > * 3. If the subquery contains EXCEPT or EXCEPT ALL set ops we cannot push > * quals into it, because that could change the results. > I remember coming to the conclusion that this is safe for > UNION/INTERSECT but not EXCEPT, but right at the moment I'm not sure why > I thought that --- it seems like a qual that suppresses specific rows > should suppress all matching copies. I dug in the archives and found the discussion that led up to the current behavior; see thread starting here http://archives.postgresql.org/pgsql-hackers/2002-08/msg00041.php The issue is that rows that are "equal" according to the rules used by UNION/INTERSECT/EXCEPT may nonetheless be distinguishable to the expression in the upper WHERE clause, and if that's the case, pushing down the WHERE can lead to provably incorrect results. So the behavior is correct, or at least very difficult to improve on, as it stands. regards, tom lane