Re: factoring problem with view in 7.3.3 [ PARTIALLY SOLVED ] - Mailing list 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.


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/