> Rajesh Kumar Mallah <[EMAIL PROTECTED]> 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/ ---------------------------(end of broadcast)--------------------------- TIP 6: Have you searched our list archives? http://archives.postgresql.org