From: Dean Gibson (DB Administrator) <postgresql3@ultimeth.com>
To: pgsql-sql@postgresql.org
Subject: Re: JOIN performance
Date: Mon, 20 Sep 2004 18:41:33 -0700
Message-ID: <5.1.0.14.2.20040920182022.00adf5e8@imaps.mailpen.net> (raw)
In-Reply-To: <23901.1095728070@sss.pgh.pa.us>
References: <5.1.0.14.2.20040920164348.00a99868@imaps.mailpen.net>
<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>
Ahh, that explains why a "plain" JOIN (which doesn't do what I need) gave
much better performance than the LEFT JOIN.
I could ask why a CASE statement is always non-nullable, but I don't think
the answer would help be solve my problem. <grin> I suppose it's that even
though my particular CASE statement has WHEN/ELSE values that come from the
nullable side of the JOIN, in general that's not true ...
Okay, now for my big question: I searched high and low for a function that
would return the minimum of two dates, and found none. Now you come up
with "date_smaller", which works fine (as does "date_larger"), but where
are those documented? More importantly, where are other functions like
them documented?
-- Dean
ps: Who dreamt up the names "date_smaller" and "date_larger" ??? Much more
intuitive are "min_date" and "max_date".
pps: I realize that "date_smaller" isn't exactly equivalent to my CASE
statement; a NULL value for one of the CASE operands causes the result of
the ELSE clause to be returned, whereas "date_smaller" just returns NULL in
that case. In my data, that's significant. I suppose that COALESCE has
the same problem as CASE ...
Tom Lane wrote on 2004-09-20 17:54:
>"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.
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: postgresql3@ultimeth.com
Subject: Re: JOIN performance
In-Reply-To: <5.1.0.14.2.20040920182022.00adf5e8@imaps.mailpen.net>
* 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