Re: [HACKERS] having and union in v7beta

Tom Lane wrote:

> Jose Soares <jose@sferacarta.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@sferacarta.com