Message-ID: <50314441.6050501@agliodbs.com>
Date: Sun, 19 Aug 2012 12:53:37 -0700
From: Josh Berkus <josh@agliodbs.com>
Organization: PostgreSQL Experts Inc.
User-Agent: Mozilla/5.0 (Macintosh; Intel Mac OS X 10.6;
	rv:14.0) Gecko/20120713 Thunderbird/14.0
MIME-Version: 1.0
To: Tom Lane <tgl@sss.pgh.pa.us>
CC: pgsql-performance@postgresql.org
Subject: Re: 7k records into Sort node, 4.5m out?
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>
In-Reply-To: <2703.1345096911@sss.pgh.pa.us>
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: 7bit


> 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