From: | "Hiroshi Inoue" <Inoue(at)tpf(dot)co(dot)jp> |
---|---|
To: | "pgsql-hackers" <pgsql-hackers(at)postgreSQL(dot)org> |
Subject: | Hash Join is very slooow in some cases |
Date: | 1999-11-19 01:14:56 |
Message-ID: | 000201bf322b$7df2a620$2801007e@cadzone.tpf.co.jp |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
Hi all,
We are converting Oracle system to PostgreSQL.
But some queries takes very looong time.
I have tried variously and made a typical example
in PostgreSQL 6.5.2 .
It's already known ?
select count(*) from a,b where a.id1=b.id1;
returns immeidaitely and EXPLAIN shows
Aggregate (cost=3380.24 rows=24929 width=8)
-> Hash Join (cost=3380.24 rows=24929 width=8)
-> Seq Scan on a (cost=1551.41 rows=27558 width=4)
-> Hash (cost=604.21 rows=8885 width=4)
-> Seq Scan on b (cost=604.21 rows=8885 width=4)
But
select count(*) from a,b where a.id1=b.id1 and a.id2=b.id2;
takes very looong time.
EXPLAIN shows
Aggregate (cost=3382.24 rows=8195 width=12)
-> Hash Join (cost=3382.24 rows=8195 width=12)
-> Seq Scan on a (cost=1551.41 rows=27558 width=6)
-> Hash (cost=604.21 rows=8885 width=6)
-> Seq Scan on b (cost=604.21 rows=8885 width=6)
Query plans are almost same.
Why is there a such difference ?
I examined an output by EXPLAIN VERBOSE and found that
the 1st query uses id1 as its hashkey and 2nd query uses id2
as its hashkey.
So I tried the following query.
select count(*) from a,b where a.id2=b.id2 and a.id1=b.id1;
This returns immediately as I expected.
id1-s are type int4 and their disbursions are 0.00010181/
0.000203409.
id2-s are type int2 and their disbursions are 0.523526/
0.328712 .
Is id2 suitable for a hashkey ?
I can't try it in current now,sorry.
But seems the following code in createplan.c is unchanged.
/* Now the righthand op of the sole hashclause is the inner hash key. */
innerhashkey = get_rightop(lfirst(hashclauses));
Comments ?
Hiroshi Inoue
Inoue(at)tpf(dot)co(dot)jp
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 1999-11-19 01:57:50 | Re: [HACKERS] psql & regress tests |
Previous Message | Bruce Momjian | 1999-11-19 01:12:18 | Getting OID in psql of recent insert |