Large CASE-statement is pretty slow? - Mailing list pgsql-performance

From Arjen van der Meijden
Subject Large CASE-statement is pretty slow?
Date
Msg-id 4054B36C.8030306@vulcanus.its.tudelft.nl
Whole thread Raw
Responses Re: Large CASE-statement is pretty slow?  (Tom Lane <tgl@sss.pgh.pa.us>)
Re: Large CASE-statement is pretty slow?  (Greg Stark <gsstark@mit.edu>)
List pgsql-performance
Hi list,

I was more or less toying with an idea for a project I have, which
includes renumbering a primary key (don't ask, it's necessary :/ )

Anyway, I was looking into the usefullness of a INSERT INTO newtable
SELECT field, field, CASE pkey WHEN x1 THEN y1 WHEN x2 THEN y2 etc END
FROM oldtable

The resulting select was about 1.7MB of query-text, mostly composed of
the CASE-statement. So I discarded that idea, I still wanted to know how
much time it would take on my database (MySQL) and found it to take
about 1100 seconds, in contrast to simply selecting the data, which'd
take about 0.7 seconds orso... The table I tested this on is about 30MB.

Of course I wanted to know how long it'd take on postgresql, selecting
the pkey-field only (without the case) took also some 0.7 seconds (the
entire table may have been more).
But the CASE-version took 9026139.201 ms, i.e. over 9000 seconds about 8
times slower than MySQL.

What I'm wondering about:
Although I was not expecting Postgresql to heavily beat MySQL, I was
surprised to see it so much slower. Is the CASE-statement in Postgresql
that inefficient? Or is it simply not very scalable (i.e. don't try to
have 100000 cases like I did)?

The database is a lightly optimised gentoo-compile of 7.4.2, the
mysql-version was 4.0.18 in case anyone wanted to know that.


Best regards,

Arjen van der Meijden


PS, don't try to "help improve the query" I discarded the idea as too
inefficient and went along with a simple left join to get the "new pkey"
out of a temporary table ;)



pgsql-performance by date:

Previous
From: Andrew Sullivan
Date:
Subject: Re: [ADMIN] syslog slowing the database?
Next
From: Tom Lane
Date:
Subject: Re: Large CASE-statement is pretty slow?