From: Gunnar \"Nick\" Bluth <gunnar.bluth.extern@elster.de>
To: Jeff Janes <jeff.janes@gmail.com>
Cc: pgsql-performance@postgresql.org <pgsql-performance@postgresql.org>
Subject: Re: select subquery versus join subquery
Date: Wed, 24 May 2017 08:41:37 +0200
Message-ID: <c5f23447-b13b-c4f5-d4bb-254c0ab4b795@elster.de> (raw)
In-Reply-To: <CAMkU=1z_zzi+h-NTo0Skftr3YxF0LPtZqDGLBSRr-pRcBKQOLQ@mail.gmail.com>
References: <CAMkU=1zxu+xZ7X44FpZ=Syot_STrUiGR9miB8K-HCqvb=KMtwA@mail.gmail.com>
<a7cc091e-b73e-389e-739d-c7d5b480d58d@elster.de>
<CAMkU=1z_zzi+h-NTo0Skftr3YxF0LPtZqDGLBSRr-pRcBKQOLQ@mail.gmail.com>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-performance>
Am 05/23/2017 um 06:59 PM schrieb Jeff Janes:
> On Tue, May 23, 2017 at 4:03 AM, Gunnar "Nick" Bluth
> <gunnar.bluth.extern@elster.de <mailto:gunnar.bluth.extern@elster.de>>
> wrote:
8>< -----
>
> Unfortunately that always uses the index scan, even at a high cutoff
> where aggregation on the seq scan and then hash joining is more
> appropriate. So it is very similar to view2, except that it doesn't
> return the rows from "thing" which have zero corresponding rows in
> thing_alias.
>
> *****
> Note btw. that both view1 and view2 don't return any md5 values for me,
> while view3 does!
> *****
>
>
> Because of the way I constructed the data, using the power transform of
> the uniform random distribution, the early rows of the view (if sorted
> by thing_id) are mostly null in the md5 column, so if you only look at
> the first few screen-fulls you might not see any md5. But your view
> does effectively an inner join rather than a left join, so your view
> gets rid of the rows with a NULL md5. Most things don't have aliases;
> of the things that do, most have 1; and some have a several.
D'oh, of course! My bad... shouldn't have looked at the results with
LIMIT :-/
My next best guess would involve a MatView for the aggregates...
--
Gunnar "Nick" Bluth
DBA ELSTER
Tel: +49 911/991-4665
Mobil: +49 172/8853339
Attachments:
smime.p7s (application/pkcs7-signature, 4.0K) download
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-performance@postgresql.org
Cc: gunnar.bluth.extern@elster.de, jeff.janes@gmail.com
Subject: Re: select subquery versus join subquery
In-Reply-To: <c5f23447-b13b-c4f5-d4bb-254c0ab4b795@elster.de>
* 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