pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Tom Lane <tgl@sss.pgh.pa.us>
To: Rajesh Kumar Mallah <mallah@trade-india.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: factoring problem with view in 7.3.3
Date: Wed, 23 Jul 2003 11:43:21 -0400
Message-ID: <13446.1058975001@sss.pgh.pa.us> (raw)
In-Reply-To: <200307231551.48444.mallah@trade-india.com>
References: <200307231551.48444.mallah@trade-india.com>

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



view thread (3+ messages)

Message-ID: <13446.1058975001@sss.pgh.pa.us>
Permalink:  ../13446.1058975001@sss.pgh.pa.us/
Also on:    postgresql.org/message-id/13446.1058975001@sss.pgh.pa.us

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-performance@postgresql.org
  Cc: tgl@sss.pgh.pa.us, mallah@trade-india.com
  Subject: Re: factoring problem with view in 7.3.3
  In-Reply-To: <13446.1058975001@sss.pgh.pa.us>
  References: <200307231551.48444.mallah@trade-india.com> <13446.1058975001@sss.pgh.pa.us>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

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