pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Tom Lane <tgl@sss.pgh.pa.us>
To: Rhett Garber <rhettg@gmail.com>
Cc: Havasvölgyi Ottó <h.otto@freemail.hu>
Cc: pgsql-performance@postgresql.org
Subject: Re: Why hash join instead of nested loop?
Date: Tue, 09 Aug 2005 13:12:59 -0400
Message-ID: <17253.1123607579@sss.pgh.pa.us> (raw)
In-Reply-To: <41b0fe890508090951546156f7@mail.gmail.com>
References: <41b0fe890508051135712a086b@mail.gmail.com>
	<001801c59a0a$f4be7480$9a00a8c0@OTTO>
	<41b0fe8905080516164464e04f@mail.gmail.com>
	<15425.1123304631@sss.pgh.pa.us>
	<41b0fe89050808100021a2a4fc@mail.gmail.com>
	<10930.1123549106@sss.pgh.pa.us>
	<41b0fe890508090951546156f7@mail.gmail.com>

Rhett Garber <rhettg@gmail.com> writes:
> Duplicated your setup in a separate DB.
> At least its reproducable for me.....

Hmm.  Well, we now have several data points but they seem to be on
wildly varying hardware.  To try to normalize the results a little,
I computed the total actual time for the hash plan divided by the sum
of the actual times for the two scan nodes.  Thus, for your example:

> Hash Join  (cost=4.83..5.91 rows=1 width=14) (actual time=7.148..7.159
> rows=1 loops=1)
>    Hash Cond: ("outer".id = "inner".obj2)
>    ->  Seq Scan on rtmessagestate  (cost=0.00..1.05 rows=5 width=14)
> (actual time=0.007..0.015 rows=5 loops=1)
>    ->  Hash  (cost=4.83..4.83 rows=1 width=4) (actual
> time=0.055..0.055 rows=0 loops=1)
>          ->  Index Scan using connection_regid_obj1_index on
> connection  (cost=0.00..4.83 rows=1 width=4) (actual time=0.028..0.032
> rows=1 loops=1)
>                Index Cond: ((connection_registry_id = 40105) AND (obj1
> = 73582)) Total runtime: 7.693 ms
> (7 rows)

this would be 7.159 / (0.015 + 0.032).  This is probably not an
enormously robust statistic but it at least focuses attention in the
right place.  Here's what I get (rounded off to 4 digits which is surely
as much precision as we have in the numbers):

 Tom 7.4.8+	  1.619
 Ian 7.4.8	  6.000
 Ian 7.4.2	 13.95
 Steinar 7.4.7	  8.833
 Rhett orig	108.3
 Rhett test	152.3
 Michael 7.4.1	  2.015

My number seems to be a bit of an outlier to the low side, but yours are
way the heck to the high side.  And Michael's test seems to rule out the
idea that it's something broken in 7.4.1 in particular.

I'm now thinking you've got either a platform- or compiler-specific
problem.  Exactly what is the hardware (the CPU not the disks)?  How did
you build or come by the Postgres executables (compiler, configure
options, etc)?

			regards, tom lane



view thread (18+ messages)  latest in thread

Message-ID: <17253.1123607579@sss.pgh.pa.us>
Permalink:  ../17253.1123607579@sss.pgh.pa.us/
Also on:    postgresql.org/message-id/17253.1123607579@sss.pgh.pa.us

 · 

reply

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 from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-performance@postgresql.org
  Cc: tgl@sss.pgh.pa.us, rhettg@gmail.com, h.otto@freemail.hu
  Subject: Re: Why hash join instead of nested loop?
  In-Reply-To: <17253.1123607579@sss.pgh.pa.us>
  References: <41b0fe890508051135712a086b@mail.gmail.com> <001801c59a0a$f4be7480$9a00a8c0@OTTO> <41b0fe8905080516164464e04f@mail.gmail.com> <15425.1123304631@sss.pgh.pa.us> <41b0fe89050808100021a2a4fc@mail.gmail.com> <10930.1123549106@sss.pgh.pa.us> <41b0fe890508090951546156f7@mail.gmail.com> <17253.1123607579@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