pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Tom Lane <tgl@sss.pgh.pa.us>
To: Rod Taylor <pg@rbt.ca>
Cc: mailing@impactmedia.de, Postgresql Performance <pgsql-performance@postgresql.org>
Subject: Re: No index usage with "left join"
Date: Mon, 02 Aug 2004 10:03:47 -0400
Message-ID: <9716.1091455427@sss.pgh.pa.us> (raw)
In-Reply-To: <1091450721.36221.104.camel@jester>
References: <20040802120851.B32852202B7@p15110922.pureserver.info>
	<1091450721.36221.104.camel@jester>

Rod Taylor <pg@rbt.ca> writes:
>> How can I force the usage of the indexes when using "left join".

> Anyway, we can be fairly sure this isn't PostgreSQL 7.4 (which would
> likely choose a far better plan -- hash join rather than nested loop)

Indeed, the lack of any join-condition line in the EXPLAIN output
implies it's 7.2 or older.  IIRC 7.4 is the first release that is
capable of using merge or hash join with a condition more complicated
than plain "Var = Var".  In this case, since the two fields are of
different datatypes, the planner sees something like "Var = Var::text"
(ie, there's an inserted cast function).  7.2 will just say "duh, too
complicated for me" and generate a nestloop.  With the columns being
of different datatypes, you don't even have a chance for an inner
indexscan in the nestloop.

In short: change the column datatypes to be the same, or update to
7.4.something.  There are no other solutions.

(Well, if you were really desperate you could create a set of
mergejoinable "text op bigint" comparison operators, and then 7.2
would be able to cope; but I should think that updating to 7.4 would
be much less work.) 

			regards, tom lane



view thread (5+ messages)  latest in thread

Message-ID: <9716.1091455427@sss.pgh.pa.us>
Permalink:  ../9716.1091455427@sss.pgh.pa.us/
Also on:    postgresql.org/message-id/9716.1091455427@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, pg@rbt.ca
  Subject: Re: No index usage with "left join"
  In-Reply-To: <9716.1091455427@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