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

Re: merge join killing performance

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Matthew Wakeling <matthew(at)flymine(dot)org>
Cc: Scott Marlowe <scott(dot)marlowe(at)gmail(dot)com>, pgsql-performance(at)postgresql(dot)org
Subject: Re: merge join killing performance
Date: 2010-05-19 16:53:03
Message-ID: 21497.1274287983@sss.pgh.pa.us (view raw or flat)
Thread:
Lists: pgsql-hackerspgsql-performance
Matthew Wakeling <matthew(at)flymine(dot)org> writes:
> On Tue, 18 May 2010, Scott Marlowe wrote:
>> Aggregate  (cost=902.41..902.42 rows=1 width=4)
>>     ->  Merge Join  (cost=869.97..902.40 rows=1 width=4)
>>         Merge Cond: (f.eid = ev.eid)
>>         ->  Index Scan using files_eid_idx on files f
>>         (cost=0.00..157830.39 rows=3769434 width=8)

> Okay, that's weird. How is the cost of the merge join only 902, when the 
> cost of one of the branches 157830, when there is no LIMIT?

It's apparently estimating (wrongly) that the merge join won't have to
scan very much of "files" before it can stop because it finds an eid
value larger than any eid in the other table.  So the issue here is an
inexact stats value for the max eid.

			regards, tom lane

In response to

Responses

pgsql-performance by date

Next:From: Scott MarloweDate: 2010-05-19 17:08:21
Subject: Re: merge join killing performance
Previous:From: Scott MarloweDate: 2010-05-19 03:06:25
Subject: Re: merge join killing performance

pgsql-hackers by date

Next:From: Scott MarloweDate: 2010-05-19 17:08:21
Subject: Re: merge join killing performance
Previous:From: Florian PflugDate: 2010-05-19 16:44:15
Subject: Re: BYTEA / DBD::Pg change in 9.0 beta

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