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 45E72329D68
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Tue, 21 Sep 2004 05:42:53 +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 42127-03
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Tue, 21 Sep 2004 04:42:51 +0000 (GMT)
Received: from fw1.mailpen.net (fw1.mailpen.net [66.13.45.202])
	by svr1.postgresql.org (Postfix) with ESMTP id B469A329D4D
	for <pgsql-sql@postgresql.org>; Tue, 21 Sep 2004 05:42:51 +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 5E1B331C01E
	for <pgsql-sql@postgresql.org>; Mon, 20 Sep 2004 21:42:50 -0700 (PDT)
Message-Id: <5.1.0.14.2.20040920213442.00ae4450@imaps.mailpen.net>
X-Sender: mailer@postgresql3.ultimeth.com (Unverified)
X-Mailer: QUALCOMM Windows Eudora Version 5.1
Date: Mon, 20 Sep 2004 21:41:40 -0700
To: pgsql-sql@postgresql.org
From: "Dean Gibson (DB Administrator)" <postgresql3@ultimeth.com>
Subject: Re: JOIN performance 
In-Reply-To: <25393.1095739813@sss.pgh.pa.us>
References: <5.1.0.14.2.20040920182022.00adf5e8@imaps.mailpen.net>
	<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>
	<5.1.0.14.2.20040920182022.00adf5e8@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/180
X-Sequence-Number: 19026

It turns out that even when I removed the CASE statement from the VIEW, the 
performance problem remained.  I had to remove the conditional as well from 
the VIEW.

To refresh your memory, there was this line in the VIEW (said VIEW being 
the subject of the LEFT JOIN):

cancel_date < expire_date AS canceled,

(cancel_date & expire_date are DATEs in the base view)

When I removed the above line from the VIEW, the performance problem 
disappeared.

Do you know why?  I'm running 7.3.4, by the way.

-- Dean