Re: v7.2.3 versus v7.3 -> huge performance penalty for JOIN with UNION - Mailing list 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.