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
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