Skip site navigation (1) Skip section navigation (2)

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

From: Christophe Pettus <xof(at)thebuild(dot)com>
To: pgsql-performance(at)postgresql(dot)org
Subject: Re: 7k records into Sort node, 4.5m out?
Date: 2012-08-14 01:48:48
Message-ID: 32B51AEA-E940-40CF-BA54-C7D4E3E1F0D7@thebuild.com (view raw or flat)
Thread:
Lists: pgsql-performance
On Aug 13, 2012, at 6:35 PM, Tomas Vondra wrote:

> On 14 Srpen 2012, 3:15, Christophe Pettus wrote:
>> Consider this EXPLAIN ANALYZE output:
>> 
>> 	http://explain.depesz.com/s/TCi
>> 
>> Note the Bitmap Heap Scan at the bottom claims to be producing 7094 rows,
>> and the Sort above it expects to be processing 7330 rows (the same number
>> the Bitmap Heap Scan expected to produce)... but the sort is actually
>> producing 4512231 rows, which the sort time would indicate is what really
>> happened.  How can this be?
> 
> Hi,
> 
> notice there's a merge join right above the sort. If there are duplicate
> values in the first table (charlie in the explain plans), the matching
> rows from the sort will be read repeatedly (re-scanned) and thus counted
> multiple times.

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?

--
-- Christophe Pettus
   xof(at)thebuild(dot)com



In response to

Responses

pgsql-performance by date

Next:From: Tom LaneDate: 2012-08-14 02:11:45
Subject: Re: 7k records into Sort node, 4.5m out?
Previous:From: Tomas VondraDate: 2012-08-14 01:35:21
Subject: Re: 7k records into Sort node, 4.5m out?

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group