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 066B4329E66 for ; Tue, 21 Sep 2004 01:54:32 +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 81087-02 for ; Tue, 21 Sep 2004 00:54:27 +0000 (GMT) Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by svr1.postgresql.org (Postfix) with ESMTP id A7531329C63 for ; Tue, 21 Sep 2004 01:54:29 +0100 (BST) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.13.1/8.13.1) with ESMTP id i8L0sUIH023902; Mon, 20 Sep 2004 20:54:30 -0400 (EDT) To: "Dean Gibson (DB Administrator)" Cc: pgsql-sql@postgresql.org Subject: Re: JOIN performance 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> Comments: In-reply-to "Dean Gibson (DB Administrator)" message dated "Mon, 20 Sep 2004 17:10:58 -0700" Date: Mon, 20 Sep 2004 20:54:30 -0400 Message-ID: <23901.1095728070@sss.pgh.pa.us> From: Tom Lane 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/176 X-Sequence-Number: 19022 "Dean Gibson (DB Administrator)" 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