Re: How to push predicate down

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';

"Subquery Scan test_entries  (cost=195.40..206.64 rows=1 width=72) (actual time=0.140..0.140 rows=0 loops=1)"
"  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(dot)kworr(at)gmail(dot)com>
To: salah jubeh <s_jubeh(at)yahoo(dot)com>
Cc: pgsql <pgsql-general(at)postgresql(dot)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.