From: Tom Lane <tgl@sss.pgh.pa.us>
To: Dean Rasheed <dean_rasheed@hotmail.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Performance problem (outer join + view + non-strict functions)þ
Date: Sun, 18 Nov 2007 13:28:39 -0500
Message-ID: <6613.1195410519@sss.pgh.pa.us> (raw)
In-Reply-To: <BAY113-W343691836A5BE3477831ECF27D0@phx.gbl>
References: <BAY113-W343691836A5BE3477831ECF27D0@phx.gbl>
Dean Rasheed <dean_rasheed@hotmail.com> writes:
> I am having performance problems running a number of queries
> involving views based on non-strict functions. I have reproduced the
> problem with the simple test-case below which shows how the query plan
> is different depending on whether the view uses strict or non-strict
> functions (even though those columns do not appear in the WHERE
> clause).
Subqueries that produce non-nullable output columns can't be pulled up
underneath the nullable side of an outer join, because their output
values wouldn't go to NULL properly when expanding an unmatched row
from the other side of the join (see has_nullable_targetlist in
prepjointree.c). In this context that means that we can't recognize
the option of using a inner indexscan for the table within the subquery.
I have some vague ideas about how to eliminate that restriction,
but don't hold your breath. At the earliest it might happen in 8.4.
regards, tom lane
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 using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: tgl@sss.pgh.pa.us, dean_rasheed@hotmail.com
Subject: Re: Performance problem (outer join + view + non-strict functions)þ
In-Reply-To: <6613.1195410519@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