Received: from malur.postgresql.org ([217.196.149.56])
	by arkaria.postgresql.org with esmtps
 (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256)
	(Exim 4.92)
	(envelope-from <pgsql-performance-owner+archive@lists.postgresql.org>)
	id 1lnZSz-0007o6-Be
	for pgsql-performance@arkaria.postgresql.org; Mon, 31 May 2021 04:23:53 +0000
Received: from localhost ([127.0.0.1] helo=malur.postgresql.org)
	by malur.postgresql.org with esmtp (Exim 4.92)
	(envelope-from <pgsql-performance-owner+archive@lists.postgresql.org>)
	id 1lnZSw-0007Nu-5K
	for pgsql-performance@arkaria.postgresql.org; Mon, 31 May 2021 04:23:50 +0000
Received: from makus.postgresql.org ([2001:4800:3e1:1::229])
	by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256)
	(Exim 4.92)
	(envelope-from <postgresql@mailpen.com>)
	id 1lnZSv-0007Nl-TF
	for pgsql-performance@lists.postgresql.org; Mon, 31 May 2021 04:23:49 +0000
Received: from aws.mailpen.com ([52.43.43.172] helo=aws2.mailpen.com)
	by makus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_GCM_SHA384:256)
	(Exim 4.92)
	(envelope-from <postgresql@mailpen.com>)
	id 1lnZSr-0007qw-IR
	for pgsql-performance@lists.postgresql.org; Mon, 31 May 2021 04:23:48 +0000
Received: from [172.20.70.208] (unknown [50.35.114.95])
	(using TLSv1.2 with cipher ECDHE-RSA-AES128-GCM-SHA256 (128/128 bits))
	(No client certificate requested)
	by aws2.mailpen.com (Postfix) with ESMTPSA id BA803C7E7D9
	for <pgsql-performance@lists.postgresql.org>;
 Sun, 30 May 2021 21:23:43 -0700 (PDT)
Subject: Re: AWS forcing PG upgrade from v9.6 a disaster
To: "pgsql-performance@lists.postgresql.org"
 <pgsql-performance@lists.postgresql.org>
References: <88dec70b-eb63-0b72-0b5d-253851ef127b@mailpen.com>
 <92cc8563-195f-8727-d887-5f2bb63f231f@mailpen.com>
 <2370D2C6-4D91-4C96-9410-11EFD9954B21@thebuild.com>
From: "Dean Gibson (DB Administrator)" <postgresql@mailpen.com>
Organization: UltiMeth Systems
Message-ID: <5463af86-9007-1094-78bf-18e54c60edaf@mailpen.com>
Date: Sun, 30 May 2021 21:23:43 -0700
User-Agent: Mozilla/5.0 (Windows NT 10.0; Win64; x64; rv:78.0) Gecko/20100101
 Thunderbird/78.10.2
MIME-Version: 1.0
In-Reply-To: <2370D2C6-4D91-4C96-9410-11EFD9954B21@thebuild.com>
Content-Type: multipart/alternative;
 boundary="------------7DA6C7EF0C22284EF8B98D83"
Content-Language: en-US
List-Id: <pgsql-performance.lists.postgresql.org>
List-Help: <https://lists.postgresql.org/manage/>
List-Subscribe: <https://lists.postgresql.org/manage/>
List-Post: <mailto:pgsql-performance@lists.postgresql.org>
List-Owner: <mailto:pgsql-performance-owner@lists.postgresql.org>
List-Archive: <https://www.postgresql.org/list/pgsql-performance>
Archived-At: 
 <https://www.postgresql.org/message-id/5463af86-9007-1094-78bf-18e54c60edaf%40mailpen.com>
Precedence: bulk

This is a multi-part message in MIME format.
--------------7DA6C7EF0C22284EF8B98D83
Content-Type: text/plain; charset=utf-8; format=flowed
Content-Transfer-Encoding: 8bit

On 2021-05-30 20:41, Christophe Pettus wrote:
> On May 30, 2021, at 20:07, Dean Gibson (DB Administrator) 
> <postgresql@mailpen.com> wrote:
>> The first two JOINs are not the problem, & are in fact retained in my solution.  The problem is the third JOIN, where "fips_county" from "County" is actually matched with the corresponding field from the "zip_code" VIEW.  Works fine, if you don't mind the performance impact in v10 & above.  It has now been rewritten, to be a sub-query for an output field.  Voila !  Back to sub-second query times.
> If, rather than a subquery, you explicitly called out the join criteria with ON, did it have the same performance benefit?

I thought that having a "USING" clause, was semantically equivalent to 
an "ON" clause with the equalities explicitly stated.  So no, I didn't 
try that.

The matching that occurred is *exactly *what I wanted.  I just didn't 
want the performance impact.


--------------7DA6C7EF0C22284EF8B98D83
Content-Type: text/html; charset=utf-8
Content-Transfer-Encoding: 8bit

<html>
  <head>
    <meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
  </head>
  <body>
    <div class="moz-cite-prefix">On 2021-05-30 20:41, Christophe Pettus
      wrote:<br>
    </div>
    <blockquote type="cite"
      cite="mid:2370D2C6-4D91-4C96-9410-11EFD9954B21@thebuild.com">On
      May 30, 2021, at 20:07, Dean Gibson (DB Administrator)
      <a class="moz-txt-link-rfc2396E" href="mailto:postgresql@mailpen.com">&lt;postgresql@mailpen.com&gt;</a> wrote:
      <blockquote type="cite">
        <pre class="moz-quote-pre" wrap="">The first two JOINs are not the problem, &amp; are in fact retained in my solution.  The problem is the third JOIN, where "fips_county" from "County" is actually matched with the corresponding field from the "zip_code" VIEW.  Works fine, if you don't mind the performance impact in v10 &amp; above.  It has now been rewritten, to be a sub-query for an output field.  Voila !  Back to sub-second query times.
</pre>
      </blockquote>
      <pre class="moz-quote-pre" wrap="">
If, rather than a subquery, you explicitly called out the join criteria with ON, did it have the same performance benefit?
</pre>
    </blockquote>
    <br>
    I thought that having a "USING" clause, was semantically equivalent
    to an "ON" clause with the equalities explicitly stated.  So no, I
    didn't try that.<br>
    <br>
    The matching that occurred is <b>exactly </b>what I wanted.  I
    just didn't want the performance impact.<br>
    <br>
  </body>
</html>

--------------7DA6C7EF0C22284EF8B98D83--