pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
factoring problem with view in 7.3.3
3+ messages / 3 participants
[nested] [flat]

* factoring problem with view in 7.3.3
@ 2003-07-23 10:21 Rajesh Kumar Mallah <mallah@trade-india.com>
  2003-07-23 14:13 ` Re: factoring problem with view in 7.3.3 Richard Huxton <dev@archonet.com>
  2003-07-23 15:43 ` Re: factoring problem with view in 7.3.3 Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 2 replies; 3+ messages in thread

From: Rajesh Kumar Mallah @ 2003-07-23 10:21 UTC (permalink / raw)
  To: pgsql-performance


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=#






^ permalink  raw  reply  [nested|flat] 3+ messages in thread

* Re: factoring problem with view in 7.3.3
  2003-07-23 10:21 factoring problem with view in 7.3.3 Rajesh Kumar Mallah <mallah@trade-india.com>
@ 2003-07-23 14:13 ` Richard Huxton <dev@archonet.com>
  1 sibling, 0 replies; 3+ messages in thread

From: Richard Huxton @ 2003-07-23 14:13 UTC (permalink / raw)
  To: Rajesh Kumar Mallah <mallah@trade-india.com>; pgsql-performance

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



^ permalink  raw  reply  [nested|flat] 3+ messages in thread

* Re: factoring problem with view in 7.3.3
  2003-07-23 10:21 factoring problem with view in 7.3.3 Rajesh Kumar Mallah <mallah@trade-india.com>
@ 2003-07-23 15:43 ` Tom Lane <tgl@sss.pgh.pa.us>
  1 sibling, 0 replies; 3+ messages in thread

From: Tom Lane @ 2003-07-23 15:43 UTC (permalink / raw)
  To: Rajesh Kumar Mallah <mallah@trade-india.com>; +Cc: pgsql-performance

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



^ permalink  raw  reply  [nested|flat] 3+ messages in thread


end of thread, other threads:[~2003-07-23 15:43 UTC | newest]

Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-07-23 10:21 factoring problem with view in 7.3.3 Rajesh Kumar Mallah <mallah@trade-india.com>
2003-07-23 14:13 ` Richard Huxton <dev@archonet.com>
2003-07-23 15:43 ` Tom Lane <tgl@sss.pgh.pa.us>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox