pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Tom Lane <tgl@sss.pgh.pa.us>
To: Dave Held <dave.held@arrayservicesgrp.com>
Cc: performance pgsql <pgsql-performance@postgresql.org>
Cc: Arjen van der Meijden <acmmailing@vulcanus.its.tudelft.nl>
Subject: Re: Plan for relatively simple query seems to be very inefficient
Date: Wed, 06 Apr 2005 14:09:03 -0400
Message-ID: <28760.1112810943@sss.pgh.pa.us> (raw)
In-Reply-To: <49E94D0CFCD4DB43AFBA928DDD20C8F9026184A0@asg002.asg.local>
References: <49E94D0CFCD4DB43AFBA928DDD20C8F9026184A0@asg002.asg.local>

"Dave Held" <dave.held@arrayservicesgrp.com> writes:
> My completely amateur guess is that the planner is able to use
> Merge Join and Hash Join on your contrived queries because you
> are only trying to join one field to a single value (i.e.:
> operator=).  But the BETWEEN clause is what forces the Nested
> Loop.  You can see that here:

Yeah --- both merge and hash join are only usable for equality joins.
(Thinking about it, it seems possible that mergejoin could be extended
to work for range joins, but we're certainly far from being able to
do that today.)  So the basic alternatives the planner has are nestloops
with either postcode on the outside, or data_main on the outside.  The
postcode-on-the-outside case would be plausible with an index on
data_main.range, but Arjen didn't have one.  The data_main-on-the-outside
case could only use an index if the index was range-query-capable, which
a 2-column btree index isn't.  Given the small size of the postcodes
table it's not real clear that an index probe would be much of a win
anyway over a simple sequential scan.

Comparing the nestloop case to the hash case does make one think that
there's an awful lot of overhead somewhere, though.  Two int2
comparisons ought not take very long :-(.  Arjen, are you interested
in getting a gprof profile of what the backend is doing in the nestloop
-with-materialize plan?  Or if you don't want to mess with it, please
send me the data off-list and I'll run a profile.

			regards, tom lane



view thread (10+ messages)  latest in thread

Message-ID: <28760.1112810943@sss.pgh.pa.us>
Permalink:  ../28760.1112810943@sss.pgh.pa.us/
Also on:    postgresql.org/message-id/28760.1112810943@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 using the --to and --cc options:
  reply via email

  To: pgsql-performance@postgresql.org
  Cc: tgl@sss.pgh.pa.us, dave.held@arrayservicesgrp.com, acmmailing@vulcanus.its.tudelft.nl
  Subject: Re: Plan for relatively simple query seems to be very inefficient
  In-Reply-To: <28760.1112810943@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