From: | "ir(dot) F(dot)T(dot)M(dot) van Vugt bc(dot)" <ftm(dot)van(dot)vugt(at)foxi(dot)nl> |
---|---|
To: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>, Andrew Sullivan <andrew(at)libertyrms(dot)info> |
Cc: | Postgresql performance <pgsql-performance(at)postgresql(dot)org> |
Subject: | Re: v7.2.3 versus v7.3 -> huge performance penalty for JOIN with UNION |
Date: | 2002-12-02 17:20:06 |
Message-ID: | 200212021820.06380.ftm.van.vugt@foxi.nl |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
Wow, the speed at which you guys are responding never ceases to amaze me !
TL> I don't suppose you have explain output for it from 7.2.3?
Nope, sorry 'bout that.
BTW, the performance comparison was not a 'hard' (measured) number, but a
watch-timed conclusion on a complete run of a conversiontool this query is
part of.
TL> It seems strange to me that the thing is picking a nestloop join here.
TL> oh, but wait: the implicit type coercion from varchar to text is what's
TL> putting the kibosh on a more intelligent join plan.
You're abolutely right, I'm back in business when putting a type coercion
inside the union:
trial=# explain select foo.*, c.id from
<cut>
QUERY PLAN
-------------------------------------------------------------------------------------------------
Sort (cost=588.66..601.10 rows=4976 width=530)
Sort Key: foo.old_id
-> Hash Join (cost=8.84..283.12 rows=4976 width=530)
Hash Cond: ("outer"."key" = "inner".old_creditor_id)
-> Subquery Scan foo (cost=0.00..174.76 rows=4976 width=150)
-> Append (cost=0.00..174.76 rows=4976 width=150)
<cut>
(as opposed to: (cost=54103.74..54116.18 rows=4976 width=498))
> This doesn't explain the slowdown from 7.2.3, though --- it had the same
> deficiency. (I am hoping to get around to fixing it for 7.4.)
Mmm, that's weird. Could be caused by somebody over here who has done 'work'
on some queries... ;( => I'll check on that, if I can be absolutely sure the
7.2.3 version planned *this* query differently, I'll let you know. Sorry
'bout that....
AS> It could easy be that --enable-locale explains the slowdown. Are you
AS> running 7.4 in C locale, or something else?
On v7.2.3. I wasn't doing anything with locale.
The v7.3 put 'POSIX' into the postgresql.conf file, changing that into 'C'
didn't seem to make any difference.
AS> Comparisons in locales like en_US can be *way* slower than in C locale.
AS> You can use pg_controldata to check this for sure.
O.K. this seems to help a lot as well !
I'll have to take a look at both ISO C and POSIX locale, 'cause I wouldn't
have expected it to make such a difference...
On the original v7.3, pg_controldata returned 'posix', upon changing the
postgresql.conf it confirmed the change to 'C'. This resulted in:
POSIX_trial=# explain analyse select foo.*, c.id from
<cut>
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------
Sort (cost=588.66..601.10 rows=4976 width=530) (actual time=2482.51..2530.54
rows=4976 loops=1)
<cut>
Total runtime: 2636.15 msec
C_trial=# explain analyse select foo.*, c.id from
<cut>
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------
Sort (cost=588.66..601.10 rows=4976 width=530) (actual time=1537.05..1549.34
rows=4976 loops=1)
<cut>
Total runtime: 1567.76 msec
Hey, I'm happy ;-)
Thanks a lot !!!
Frank.
From | Date | Subject | |
---|---|---|---|
Next Message | typea | 2002-12-02 18:30:50 | |
Previous Message | Tom Lane | 2002-12-02 16:13:44 | Re: v7.2.3 versus v7.3 -> huge performance penalty for JOIN with UNION |