From: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
---|---|
To: | Rajesh Kumar Mallah <mallah(at)trade-india(dot)com> |
Cc: | pgsql-performance(at)postgresql(dot)org |
Subject: | Re: factoring problem with view in 7.3.3 |
Date: | 2003-07-23 15:43:21 |
Message-ID: | 13446.1058975001@sss.pgh.pa.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
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. 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
From | Date | Subject | |
---|---|---|---|
Next Message | mallah | 2003-07-23 16:37:09 | Re: factoring problem with view in 7.3.3 [ PARTIALLY SOLVED ] |
Previous Message | Tom Lane | 2003-07-23 15:21:06 | Re: different query plan for same select |