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

Re: BUG #2930: Hash join abyssmal with many null fields.

From: Maciej Babinski <maciej(at)killer-robot(dot)net>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: Maciej Babinski <maciej+postgres(at)apathy(dot)killer-robot(dot)net>,pgsql-bugs(at)postgresql(dot)org
Subject: Re: BUG #2930: Hash join abyssmal with many null fields.
Date: 2007-01-26 16:15:25
Message-ID: 45BA291D.7050308@killer-robot.net (view raw or flat)
Thread:
Lists: pgsql-bugs
Tom Lane wrote:
> "Maciej Babinski" <maciej+postgres(at)apathy(dot)killer-robot(dot)net> writes:
>> Hash join of columns with many null fields is very slow unless the null
>> fields are commented out.
> 
> I see no bug here.  AFAICT your "much faster" query gets that way by
> having eliminated all the candidate join rows on the B side.
> 
> 			regards, tom lane


The additional clause eliminates no rows beyond what the existing clause 
would.
Any row eliminated by "b.join_id IS NOT NULL" could not possibly have 
satisfied
"a.join_id = b.join_id".

Please note that if the join columns are not null, but still produce no 
matches
for the join, the results are fast without the need for an extra clause 
in the join:

DROP TABLE a;
DROP TABLE b;

CREATE TABLE a (id integer, join_id integer);
CREATE TABLE b (id integer, join_id integer);

INSERT INTO a (id) SELECT generate_series(1,10000);
INSERT INTO b (id) SELECT generate_series(1,10000);

ANALYZE a;
ANALYZE b;

EXPLAIN ANALYZE SELECT * FROM a JOIN b ON a.join_id = b.join_id; /* 14 
seconds */
EXPLAIN ANALYZE SELECT * FROM a JOIN b ON a.join_id = b.join_id AND 
b.join_id IS NOT NULL; /* 5ms */

UPDATE a SET join_id=1;
UPDATE b SET join_id=2;

EXPLAIN ANALYZE SELECT * FROM a JOIN b ON a.join_id = b.join_id; /* 72ms */
EXPLAIN ANALYZE SELECT * FROM a JOIN b ON a.join_id = b.join_id AND 
b.join_id != 2; /* 48ms */


It seems to me that such a wild disparity in performance due to the 
addition of a clause that is
implied by the existing clause should be considered a bug, but if I need 
to submit a feature
request for the optimizer, then I'd be happy to. Thanks!

Maciej Babinski

In response to

Responses

pgsql-bugs by date

Next:From: Tom LaneDate: 2007-01-26 17:22:26
Subject: Re: BUG #2930: Hash join abyssmal with many null fields.
Previous:From: Tom LaneDate: 2007-01-26 05:49:52
Subject: Re: BUG #2930: Hash join abyssmal with many null fields.

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