Re: 7k records into Sort node, 4.5m out?

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Christophe Pettus <xof(at)thebuild(dot)com>
Cc: pgsql-performance(at)postgresql(dot)org
Subject: Re: 7k records into Sort node, 4.5m out?
Date: 2012-08-14 02:11:45
Message-ID: 10304.1344910305@sss.pgh.pa.us
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-performance

Christophe Pettus <xof(at)thebuild(dot)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

In response to

Responses

Browse pgsql-performance by date

  From Date Subject
Next Message Jeff Janes 2012-08-14 04:14:42 Re: Index Bloat Problem
Previous Message Christophe Pettus 2012-08-14 01:48:48 Re: 7k records into Sort node, 4.5m out?