X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org
Received: from localhost (unknown [200.46.204.144])
	by svr1.postgresql.org (Postfix) with ESMTP id 41F86329E94
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Tue, 21 Sep 2004 02:44:20 +0100 (BST)
Received: from svr1.postgresql.org ([200.46.204.71])
	by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024)
	with ESMTP id 94123-02
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Tue, 21 Sep 2004 01:43:59 +0000 (GMT)
Received: from fw1.mailpen.net (fw1.mailpen.net [66.13.45.202])
	by svr1.postgresql.org (Postfix) with ESMTP id 50789329C63
	for <pgsql-sql@postgresql.org>; Tue, 21 Sep 2004 02:44:01 +0100 (BST)
Received: from TpA22p1.ultimeth.com (TpA22p1.internal.mailpen.net
	[192.168.128.199])
	(using SSLv3 with cipher DES-CBC3-SHA (168/168 bits))
	(No client certificate requested)
	by fw1.mailpen.net (Postfix) with ESMTP
	id 740C531C01E; Mon, 20 Sep 2004 18:44:01 -0700 (PDT)
Message-Id: <5.1.0.14.2.20040920182022.00adf5e8@imaps.mailpen.net>
X-Sender: mailer@postgresql3.ultimeth.com (Unverified)
X-Mailer: QUALCOMM Windows Eudora Version 5.1
Date: Mon, 20 Sep 2004 18:41:33 -0700
To: pgsql-sql@postgresql.org
From: "Dean Gibson (DB Administrator)" <postgresql3@ultimeth.com>
Subject: Re: JOIN performance 
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>
Mime-Version: 1.0
Content-Type: text/plain; charset="us-ascii"; format=flowed
X-Virus-Scanned: by amavisd-new at hub.org
X-Spam-Status: No, hits=0.0 tagged_above=0.0 required=5.0 tests=
X-Spam-Level: 
X-Archive-Number: 200409/177
X-Sequence-Number: 19023

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.