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@xxxxxxxxx>
To: salah jubeh <s_jubeh@xxxxxxxxx>
Cc: pgsql <pgsql-general@xxxxxxxxxxxxxx>
Sent: Thursday, January 26, 2012 3:49 PM
Subject: Re: 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@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
