From: Tom Lane <tgl@sss.pgh.pa.us>
To: Christophe Pettus <xof@thebuild.com>
cc: pgsql-performance@postgresql.org
Subject: Re: 7k records into Sort node, 4.5m out?
In-reply-to: <32B51AEA-E940-40CF-BA54-C7D4E3E1F0D7@thebuild.com>
References: <D2CA9292-023A-4DF4-82B8-6E99CE82CCB1@thebuild.com>
	<dda48e8e4cd63cc6f98f9eca78016c05.squirrel@sq.gransy.com>
	<32B51AEA-E940-40CF-BA54-C7D4E3E1F0D7@thebuild.com>
Comments: In-reply-to Christophe Pettus <xof@thebuild.com>
	message dated "Mon, 13 Aug 2012 18:48:48 -0700"
Date: Mon, 13 Aug 2012 22:11:45 -0400
Message-ID: <10304.1344910305@sss.pgh.pa.us>

Christophe Pettus <xof@thebuild.com> writes:
> Thanks, that makes sense.  Something a colleague of mine just noticed is that the estimate cost of the Index Scan node isn't being included in the cost of the Merge Join above it, which makes the Merge Join seem much cheaper than it really is.  Could this be a planner bug?

No, that looks sane.  It's probably expecting that the range of keys on
the right-hand side is a lot less than the range of keys on the left,
and thus the merge won't have to read all of the left side.  Since the
output shows an estimated total number of rows in the LHS of 84 million,
but the join stopped after reading 20 million of them, it looks like
that effect did in fact occur.  If the planner had that fraction dead
on, it would only have charged the mergejoin with a quarter of the
indexscan's total estimated cost.  It's hard to tell though exactly what
it did think.

The whole thing looks a bit weird to me --- why did it not use a
nestloop join with inner indexscan on charlie?  With 7000 rows on the
other side, the estimated cost for that shouldn't have been more than
about 30000 ...

			regards, tom lane