From: Tom Lane <tgl@sss.pgh.pa.us>
To: Josh Berkus <josh@agliodbs.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: 7k records into Sort node, 4.5m out?
Date: Thu, 16 Aug 2012 02:01:51 -0400
Message-ID: <2703.1345096911@sss.pgh.pa.us> (raw)
In-Reply-To: <502C3DE2.9060703@agliodbs.com>
References: <D2CA9292-023A-4DF4-82B8-6E99CE82CCB1@thebuild.com>
<dda48e8e4cd63cc6f98f9eca78016c05.squirrel@sq.gransy.com>
<32B51AEA-E940-40CF-BA54-C7D4E3E1F0D7@thebuild.com>
<10304.1344910305@sss.pgh.pa.us>
<623F2FF3-B79D-419A-929A-41C707DD5EA3@thebuild.com>
<16268.1345063872@sss.pgh.pa.us>
<502C0E6B.1040402@agliodbs.com>
<20520.1345067076@sss.pgh.pa.us>
<502C3DE2.9060703@agliodbs.com>
Josh Berkus <josh@agliodbs.com> writes:
> SELECT COUNT(*)
> FROM "user"
> INNER JOIN "house"
> ON ("user"."house_id" = "house"."id")
> LEFT OUTER JOIN "district"
> ON ("house"."district_id" = "district"."id")
> WHERE ("user"."status" = 0
> AND ("district"."update_status" = 2
> OR "district"."update_status" = 3 )
> AND ("user"."valid" = 1
> OR "user"."valid" = 3 )
> AND "district"."is_test" = false );
> However, since the anonymization above doesn't quite match that used in
> the EXPLAIN plan, I'm not sure what you'll get out of it. And yes, we
> know that the outer join is being invalidated.
Ah, I see where I was confused: in the original query plan I'd been
imagining that charlie.sierra was a unique column, but your gloss on
that as being house.district_id implies that it's highly non-unique.
And looking at the rowcounts in the original plan backs that up:
there are about 600 house rows per district row. So my thought of
having district as the outer side of a nestloop scanning the index
on house.district_id would not really work very well --- maybe it
would end up cheaper than the mergejoin plan, but it's far from a
clear-cut win.
On the whole I'm thinking the code is operating as designed here.
regards, tom lane
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: tgl@sss.pgh.pa.us, josh@agliodbs.com
Subject: Re: 7k records into Sort node, 4.5m out?
In-Reply-To: <2703.1345096911@sss.pgh.pa.us>
* 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