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?
Date: Mon, 13 Aug 2012 22:11:45 -0400
Message-ID: <10304.1344910305@sss.pgh.pa.us> (raw)
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>
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
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, xof@thebuild.com
Subject: Re: 7k records into Sort node, 4.5m out?
In-Reply-To: <10304.1344910305@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