From: Tom Lane <tgl@sss.pgh.pa.us>
To: Dean Gibson (DB Administrator) <postgresql3@ultimeth.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: JOIN performance
Date: Mon, 20 Sep 2004 20:54:30 -0400
Message-ID: <23901.1095728070@sss.pgh.pa.us> (raw)
In-Reply-To: <5.1.0.14.2.20040920164348.00a99868@imaps.mailpen.net>
References: <5.1.0.14.2.20040920121606.00a89c68@imaps.mailpen.net>
<414F02FF.8000603@numerixtechnology.de>
<414F02FF.8000603@numerixtechnology.de>
<5.1.0.14.2.20040920121606.00a89c68@imaps.mailpen.net>
<5.1.0.14.2.20040920164348.00a99868@imaps.mailpen.net>
"Dean Gibson (DB Administrator)" <postgresql3@ultimeth.com> writes:
> Question: why do the last two column definitions in the second VIEW change
> the scan on _LicHD from indexed to sequential ??
It's the CASE that's getting you. The poor plan is basically because
the sub-view isn't getting "flattened" into the upper query, and so it's
not possible to choose a plan for it that's dependent on the upper query
context. And the reason it's not getting flattened is that subselects
that are on the nullable side of an outer join can't be flattened unless
they have nullable targetlists --- otherwise the results might not go to
NULL when they are supposed to. A CASE construct is always going to be
treated as non-nullable.
Fixing this properly is a research project, and I haven't thought of any
quick-and-dirty hacks that aren't too ugly to consider :-(
In the meantime, you could easily replace that CASE construct with a
min() function that's declared strict. I think date_smaller would
do nicely, assuming the columns are actually of type date.
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-sql@postgresql.org
Cc: tgl@sss.pgh.pa.us, postgresql3@ultimeth.com
Subject: Re: JOIN performance
In-Reply-To: <23901.1095728070@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