Skip site navigation (1) Skip section navigation (2)

Re: factoring problem with view in 7.3.3 [ PARTIALLY SOLVED ]

From: <mallah(at)trade-india(dot)com>
To: <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: <pgsql-performance(at)postgresql(dot)org>
Subject: Re: factoring problem with view in 7.3.3 [ PARTIALLY SOLVED ]
Date: 2003-07-23 16:37:09
Message-ID: (view raw, whole thread or download thread mbox)
Lists: pgsql-performance
> Rajesh Kumar Mallah <mallah(at)trade-india(dot)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 ?


 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.

In response to


pgsql-performance by date

Next:From: Tom LaneDate: 2003-07-23 16:47:43
Subject: Re: factoring problem with view in 7.3.3 [ PARTIALLY SOLVED ]
Previous:From: Tom LaneDate: 2003-07-23 15:43:21
Subject: Re: factoring problem with view in 7.3.3

Privacy Policy | About PostgreSQL
Copyright © 1996-2017 The PostgreSQL Global Development Group