Re: [HACKERS] having and union in v7beta

Tom Lane wrote:

> Jose Soares <jose(at)sferacarta(dot)com> writes:
> >> But it's tough to believe that that accounts for a 3-to-4x
> >> slowdown of this query; certainly I don't see much performance
> >> difference on the datatypes I tried. What datatypes are your fields,
> >> anyway?
>
> > 6.5 takes 0.463s
> > 7.0 takes 1.640s
> > the field type is CHAR(4)
>
> Hmm. I see no more than a few percent difference between 6.5 and
> current. There's something peculiar going on on your system.
>
> Current code would ultimately invoke strncmp() on the two char fields,
> whereas 6.5 used memcmp(). Is it possible that strncmp() is a huge
> performance dog on your platform? I assume you are running in a
> non-ASCII locale, which might reduce strncmp's performance, but still...
>
> A quick-and-dirty way for you to check this would be to change the
> strncmp call to call memcmp (just the one-word change should work)
> in bpchareq() in src/backend/utils/adt/varchar.c, and see if that
> changes the performance of this query materially.
>
> regards, tom lane
>

tests with strncmp:
^^^^^^^^^^^^^^^^^^^
$ time psql hygea1 -c 'select * from comuni union select * from comuni' >
/dev/n
real 0m1.685s
user 0m0.190s
sys 0m0.050s
$ time psql hygea1 -c 'select * from comuni union select * from comuni' >
/dev/n
real 0m1.681s
user 0m0.200s
sys 0m0.060s
$ time psql hygea1 -c 'select * from comuni union select * from comuni' >
/dev/n
real 0m1.680s
user 0m0.140s
sys 0m0.020s
$ time psql hygea1 -c 'select * from comuni union select * from comuni' >
/dev/n
real 0m1.695s
user 0m0.220s
sys 0m0.010s
$ time psql hygea1 -c 'select * from comuni union select * from comuni' >
/dev/n
real 0m1.681s
user 0m0.150s
sys 0m0.020s
=========================================
tests with memcmp:
^^^^^^^^^^^^^^^^^^
$ time psql hygea1 -c 'select * from comuni union select * from comuni' >
/dev/n
real 0m1.714s
user 0m0.220s
sys 0m0.010s
$ time psql hygea1 -c 'select * from comuni union select * from comuni' >
/dev/n
real 0m1.696s
user 0m0.190s
sys 0m0.010s
$ time psql hygea1 -c 'select * from comuni union select * from comuni' >
/dev/n
real 0m1.702s
user 0m0.220s
sys 0m0.010s
$ time psql hygea1 -c 'select * from comuni union select * from comuni' >
/dev/n
real 0m1.693s
user 0m0.190s
sys 0m0.020s
$ time psql hygea1 -c 'select * from comuni union select * from comuni' >
/dev/n
real 0m1.692s
user 0m0.180s
sys 0m0.030s
======================================

--
Jose' Soares
Bologna, Italy Jose(at)sferacarta(dot)com