Thread: factoring problem with view in 7.3.3
Hi ,
I have a view which is a union of select of certain feilds from
indentical tables. The problem is when we query a column on
which index exists exists foreach of the tables does not use the
indexes.
But when we query individual tables it uses indexes.
Regds
Mallah.
tradein_clients=# create view sent_enquiry_eyp_iid_ip_cat1 as
select rfi_id,sender_uid,receiver_uid,subject,generated from eyp_rfi UNION
select rfi_id,sender_uid,receiver_uid,subject,generated from iid_rfi UNION
select rfi_id,sender_uid,receiver_uid,subject,generated from ip_rfi UNION
select rfi_id,sender_uid,receiver_uid,subject,generated from catalog_rfi ;
CREATE VIEW
tradein_clients=#
tradein_clients=# explain analyze select rfi_id from
sent_enquiry_eyp_iid_ip_cat1 where sender_uid = 34866;
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------
Subquery Scan sent_enquiry_eyp_iid_ip_cat1 (cost=173347.05..182139.66
rows=58617 width=55) (actual time=57514.58..62462.15 rows=73 loops=1)
Filter: (sender_uid = 34866)
-> Unique (cost=173347.05..182139.66 rows=58617 width=55) (actual
time=57514.54..61598.82 rows=586230 loops=1)
-> Sort (cost=173347.05..174812.49 rows=586174 width=55) (actual
time=57514.54..58472.01 rows=586231 loops=1)
Sort Key: rfi_id, sender_uid, receiver_uid, subject, generated
-> Append (cost=0.00..90563.74 rows=586174 width=55) (actual
time=13.17..50500.95 rows=586231 loops=1)
-> Subquery Scan "*SELECT* 1" (cost=0.00..57800.63
rows=369463 width=42) (actual time=13.17..30405.33 rows=369536 loops=1)
-> Seq Scan on eyp_rfi (cost=0.00..57800.63
rows=369463 width=42) (actual time=13.14..28230.00 rows=369536 loops=1)
-> Subquery Scan "*SELECT* 2" (cost=0.00..7317.11
rows=45811 width=47) (actual time=0.04..534.89 rows=45811 loops=1)
-> Seq Scan on iid_rfi (cost=0.00..7317.11
rows=45811 width=47) (actual time=0.03..359.88 rows=45811 loops=1)
-> Subquery Scan "*SELECT* 3" (cost=0.00..22335.44
rows=145244 width=42) (actual time=0.08..17815.66 rows=145251 loops=1)
-> Seq Scan on ip_rfi (cost=0.00..22335.44
rows=145244 width=42) (actual time=0.05..16949.03 rows=145251 loops=1)
-> Subquery Scan "*SELECT* 4" (cost=0.00..3110.56
rows=25656 width=55) (actual time=0.07..469.60 rows=25633 loops=1)
-> Seq Scan on catalog_rfi (cost=0.00..3110.56
rows=25656 width=55) (actual time=0.06..380.64 rows=25633 loops=1)
Total runtime: 62504.24 msec
(15 rows)
tradein_clients=# explain analyze select rfi_id from eyp_rfi where
sender_uid = 34866;
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------
Index Scan using eyp_sender_uid_idx on eyp_rfi (cost=0.00..376.11 rows=117
width=4) (actual time=9.88..69.10 rows=12 loops=1)
Index Cond: (sender_uid = 34866)
Total runtime: 69.17 msec
(3 rows)
tradein_clients=#
On Wednesday 23 July 2003 11:21, Rajesh Kumar Mallah wrote: > Hi , > > I have a view which is a union of select of certain feilds from > indentical tables. The problem is when we query a column on > which index exists exists foreach of the tables does not use the > indexes. > > > But when we query individual tables it uses indexes. > > tradein_clients=# create view sent_enquiry_eyp_iid_ip_cat1 as > select rfi_id,sender_uid,receiver_uid,subject,generated from eyp_rfi UNION > select rfi_id,sender_uid,receiver_uid,subject,generated from iid_rfi UNION > select rfi_id,sender_uid,receiver_uid,subject,generated from ip_rfi UNION > select rfi_id,sender_uid,receiver_uid,subject,generated from catalog_rfi ; > > CREATE VIEW > tradein_clients=# > tradein_clients=# explain analyze select rfi_id from > sent_enquiry_eyp_iid_ip_cat1 where sender_uid = 34866; [snip query plan showing full selects being done and then filtering on the outputs] I do remember some talk about issues with pushing where clauses down into unions on a view (sorry - can't remember when - maybe check the archives). Actually, I thought work had been done on that for 7.3.3, but it might have been 7.4 If you generally do that particular query (checking agains sender_uid) then the simplest solution is to build an SQL query to push the comparison down for you: CREATE my_function(int4) RETURNS SETOF my_type AS ' SELECT ... FROM eyp_rfi WHERE sender_uid = $1 UNION ...etc... ' LANGUAGE 'SQL'; Note that you may get an error about an operator "=$" if you miss the spaces around the "=". HTH -- Richard Huxton Archonet Ltd
Rajesh Kumar Mallah <mallah@trade-india.com> writes:
> I have a view which is a union of select of certain feilds from
> indentical tables. The problem is when we query a column on
> which index exists exists foreach of the tables does not use the
> indexes.
Hard to be certain since you didn't show us the table definitions,
but I suspect the culprit is a datatype mismatch. Here are the
comments for 7.3's subquery_is_pushdown_safe, which determines whether
it's okay to push down a qualifier:
* Conditions checked here:
*
* 1. If the subquery has a LIMIT clause or a DISTINCT ON clause, we must
* not push down any quals, since that could change the set of rows
* returned. (Actually, we could push down quals into a DISTINCT ON
* subquery if they refer only to DISTINCT-ed output columns, but
* checking that seems more work than it's worth. In any case, a
* plain DISTINCT is safe to push down past.)
*
* 2. If the subquery has any functions returning sets in its target list,
* we do not push down any quals, since the quals
* might refer to those tlist items, which would mean we'd introduce
* functions-returning-sets into the subquery's WHERE/HAVING quals.
* (It'd be sufficient to not push down quals that refer to those
* particular tlist items, but that's much clumsier to check.)
*
* 3. If the subquery contains EXCEPT or EXCEPT ALL set ops we cannot push
* quals into it, because that would change the results. For subqueries
* using UNION/UNION ALL/INTERSECT/INTERSECT ALL, we can push the quals
* into each component query, so long as all the component queries share
* identical output types. (That restriction could probably be relaxed,
* but it would take much more code to include type coercion code into
* the quals, and I'm also concerned about possible semantic gotchas.)
1 and 2 don't seem to apply to your problem, which leaves 3 ...
(BTW, 7.4 has addressed all of the possible improvements noted in the
parenthetical remarks here.)
regards, tom lane
> Rajesh Kumar Mallah <mallah@trade-india.com> writes: >> I have a view which is a union of select of certain feilds from >> indentical tables. The problem is when we query a column on >> which index exists exists foreach of the tables does not use the >> indexes. > > Hard to be certain since you didn't show us the table definitions, but > I suspect the culprit is a datatype mismatch. Rightly guessed , one of the columns in the view was having a diffrent type (date vs timestamp ). The column was removed from the view it worked. the column 'generated' was timestamp in 2 place and date in 2 place, i wanted it in my and did a typecasting in the view below but it suffers from the same problem . I could use Richards suggestion then ? regds mallah. CREATE VIEW sent_enquiry_eyp_iid_ip_cat2 as ((((((SELECT eyp_rfi.rfi_id, eyp_rfi.sender_uid, eyp_rfi.receiver_uid, eyp_rfi.subject, eyp_rfi.generated::timestamp FROM ONLY eyp_rfi) UNION (SELECT iid_rfi.rfi_id, iid_rfi.sender_uid, iid_rfi.receiver_uid, iid_rfi.subject, iid_rfi.generated FROM ONLY iid_rfi))) UNION (SELECT ip_rfi.rfi_id, ip_rfi.sender_uid, ip_rfi.receiver_uid, ip_rfi.subject, ip_rfi.generated::timestamp FROM ONLY ip_rfi))) UNION (SELECT catalog_rfi.rfi_id, catalog_rfi.sender_uid, catalog_rfi.receiver_uid, catalog_rfi.subject, catalog_rfi.generated FROM ONLY catalog_rfi)); Here are the > comments for 7.3's subquery_is_pushdown_safe, which determines whether > it's okay to push down a qualifier: > > * Conditions checked here: > * > * 1. If the subquery has a LIMIT clause or a DISTINCT ON clause, we > must * not push down any quals, since that could change the set of rows > * returned. (Actually, we could push down quals into a DISTINCT ON * > subquery if they refer only to DISTINCT-ed output columns, but > * checking that seems more work than it's worth. In any case, a > * plain DISTINCT is safe to push down past.) > * > * 2. If the subquery has any functions returning sets in its target > list, * we do not push down any quals, since the quals > * might refer to those tlist items, which would mean we'd introduce * > functions-returning-sets into the subquery's WHERE/HAVING quals. * > (It'd be sufficient to not push down quals that refer to those > * particular tlist items, but that's much clumsier to check.) > * > * 3. If the subquery contains EXCEPT or EXCEPT ALL set ops we cannot > push * quals into it, because that would change the results. For > subqueries * using UNION/UNION ALL/INTERSECT/INTERSECT ALL, we can push > the quals * into each component query, so long as all the component > queries share * identical output types. (That restriction could > probably be relaxed, * but it would take much more code to include type > coercion code into * the quals, and I'm also concerned about possible > semantic gotchas.) > > 1 and 2 don't seem to apply to your problem, which leaves 3 ... > > (BTW, 7.4 has addressed all of the possible improvements noted in the > parenthetical remarks here.) > > regards, tom lane ----------------------------------------- Over 1,00,000 exporters are waiting for your order! Click below to get in touch with leading Indian exporters listed in the premier trade directory Exporters Yellow Pages. http://www.trade-india.com/dyn/gdh/eyp/
<mallah@trade-india.com> writes:
> the column 'generated' was timestamp in 2 place and date in 2 place,
> i wanted it in my and did a typecasting in the view below
> but it suffers from the same problem .
AFAIR it should work if you insert casts into the UNION's member selects.
Maybe you didn't get the casting quite right? (For instance,
"timestamp" isn't "timestamp with time zone" ...)
regards, tom lane
Yep!
it works perfectly now.
CREATE or replace VIEW sent_enquiry_eyp_iid_ip_cat2 as ((((((SELECT
eyp_rfi.rfi_id, eyp_rfi.sender_uid, eyp_rfi.receiver_uid, eyp_rfi.subject,
cast(eyp_rfi.generated as timestamp with time zone ) FROM ONLY eyp_rfi)
UNION (SELECT iid_rfi.rfi_id, iid_rfi.sender_uid, iid_rfi.receiver_uid,
iid_rfi.subject, iid_rfi.generated FROM ONLY iid_rfi))) UNION (SELECT
ip_rfi.rfi_id, ip_rfi.sender_uid, ip_rfi.receiver_uid, ip_rfi.subject,
cast(ip_rfi.generated as timestamp with time zone ) FROM ONLY ip_rfi)))
UNION (SELECT catalog_rfi.rfi_id, catalog_rfi.sender_uid,
catalog_rfi.receiver_uid, catalog_rfi.subject, catalog_rfi.generated FROM
ONLY catalog_rfi));
tradein_clients=# explain analyze SELECT rfi_id from
sent_enquiry_eyp_iid_ip_cat2 where sender_uid=38466;
QUERY
PLAN------------------------------------------------------------------------------------------------------------------------------------------------------------------
SubqueryScan sent_enquiry_eyp_iid_ip_cat2 (cost=641.62..644.67 rows=20
width=55) (actual time=0.17..0.17 rows=0 loops=1) -> Unique (cost=641.62..644.67 rows=20 width=55) (actual
time=0.17..0.17 rows=0 loops=1) -> Sort (cost=641.62..642.12 rows=204 width=55) (actual
time=0.17..0.17 rows=0 loops=1) Sort Key: rfi_id, sender_uid, receiver_uid, subject, generated
-> Append (cost=0.00..633.80 rows=204 width=55) (actual
time=0.08..0.08 rows=0 loops=1) -> Subquery Scan "*SELECT* 1" (cost=0.00..376.11
rows=117 width=42) (actual time=0.03..0.03 rows=0
loops=1) -> Index Scan using eyp_sender_uid_idx on
eyp_rfi (cost=0.00..376.11 rows=117 width=42)
(actual time=0.03..0.03 rows=0 loops=1) Index Cond:
(sender_uid= 38466)
-> Subquery Scan "*SELECT* 2" (cost=0.00..81.33
rows=21 width=47) (actual time=0.02..0.02 rows=0
loops=1) -> Index Scan using iid_sender_uid_idx on
iid_rfi (cost=0.00..81.33 rows=21 width=47)
(actual time=0.02..0.02 rows=0 loops=1) Index Cond:
(sender_uid= 38466)
-> Subquery Scan "*SELECT* 3" (cost=0.00..160.18
rows=57 width=42) (actual time=0.02..0.02 rows=0
loops=1) -> Index Scan using ip_sender_uid_idx on
ip_rfi (cost=0.00..160.18 rows=57 width=42)
(actual time=0.02..0.02 rows=0 loops=1) Index Cond:
(sender_uid= 38466)
-> Subquery Scan "*SELECT* 4" (cost=0.00..16.19
rows=8 width=55) (actual time=0.01..0.01 rows=0
loops=1) -> Index Scan using catalog_sender_uid_idx on
catalog_rfi (cost=0.00..16.19 rows=8 width=55)
(actual time=0.01..0.01 rows=0 loops=1) Index Cond:
(sender_uid= 38466)
Total runtime: 0.41 msec
(18 rows)
regds
mallah.
> <mallah@trade-india.com> writes:
>> the column 'generated' was timestamp in 2 place and date in 2 place, i
>> wanted it in my and did a typecasting in the view below
>> but it suffers from the same problem .
>
> AFAIR it should work if you insert casts into the UNION's member
> selects. Maybe you didn't get the casting quite right? (For instance,
> "timestamp" isn't "timestamp with time zone" ...)
>
> regards, tom lane
>
> ---------------------------(end of
> broadcast)--------------------------- TIP 6: Have you searched our list
> archives?
>
> http://archives.postgresql.org
-----------------------------------------
Over 1,00,000 exporters are waiting for your order! Click below to get
in touch with leading Indian exporters listed in the premier
trade directory Exporters Yellow Pages.
http://www.trade-india.com/dyn/gdh/eyp/