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

Re: strange explain analyze output

From: Jeff Davis <pgsql(at)j-davis(dot)com>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: pgsql-general(at)postgresql(dot)org
Subject: Re: strange explain analyze output
Date: 2008-08-28 16:34:35
Message-ID: 1219941275.8211.112.camel@jdavis (view raw or flat)
Thread:
Lists: pgsql-general
On Thu, 2008-08-28 at 00:42 -0400, Tom Lane wrote:
> The reason that these statements are not inconsistent is that the
> Sort is the inner relation for a mergejoin.  In the presence of
> duplicate keys in the outer relation, a mergejoin will "rewind" and
> rescan duplicate keys in the inner relation; that is, any row in the
> inner relation will be fetched approximately as many times as it has
> matches in the outer relation.  So it looks like you've got roughly
> 1000X duplication in these tables?
> 

Thanks for the explanation.

> (BTW, the planner knows that this is expensive and will avoid mergejoins
> when there are many duplicate keys.  But apparently a hash join seemed
> even worse for the stats of this particular pair of tables.)
> 

Then wouldn't the planner have estimated more rows returned by the sort
(including rescanned rows) than the HashAgg? It estimated exactly the
same number as it estimated for the output of the HashAgg.

Regards,
	Jeff Davis


In response to

Responses

pgsql-general by date

Next:From: John T. DowDate: 2008-08-28 16:35:50
Subject: Re: WAL file questions - how to relocate on Windows, how to replay after total loss, etc
Previous:From: Andrew SullivanDate: 2008-08-28 16:27:43
Subject: Re: Vaccuum best practice: cronjob or autovaccuum?

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