From: Josh Berkus <josh@agliodbs.com>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: pgsql-performance@postgresql.org
Subject: Re: 7k records into Sort node, 4.5m out?
Date: Sun, 19 Aug 2012 12:53:37 -0700
Message-ID: <50314441.6050501@agliodbs.com> (raw)
In-Reply-To: <2703.1345096911@sss.pgh.pa.us>
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>
<2703.1345096911@sss.pgh.pa.us>
> 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.
Well, except for the part where it's choosing a plan which takes 486
seconds over a plan which takes 4 seconds.
I guess what I'm really not understanding is why it's calculating a cost
of 3.7m for the index scan, and then discarding that *entire* cost and
not including it in the total cost of the query? This seems wrong,
especially since that index scan, in fact, ends up being 85% of the
execution time of the query:
Merge Join (cost=7457.670..991613.190 rows=1092168 width=4) (actual
time=57.854..481062.706 rows=4514968 loops=1)
Merge Cond: (charlie.sierra = four.quebec_seven)
Index Scan using whiskey_delta on charlie (cost=0.000..3775171.860
rows=84904088 width=8) (actual time=0.006..459501.341 rows=20759070 loops=1)
If the cost of the index scan were included in the total cost of the
query plan, then the planner *would* chose the nestloop plan instead.
--
Josh Berkus
PostgreSQL Experts Inc.
http://pgexperts.com
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: josh@agliodbs.com, tgl@sss.pgh.pa.us
Subject: Re: 7k records into Sort node, 4.5m out?
In-Reply-To: <50314441.6050501@agliodbs.com>
* 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