X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.2]) by svr1.postgresql.org (Postfix) with ESMTP id 4E56CD1DD14 for ; Sun, 14 Mar 2004 19:33:32 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024) with ESMTP id 47901-01 for ; Sun, 14 Mar 2004 15:33:36 -0400 (AST) Received: from mailhost3.tudelft.nl (mailhost3.tudelft.nl [130.161.180.14]) by svr1.postgresql.org (Postfix) with ESMTP id D835DD1DAA7 for ; Sun, 14 Mar 2004 15:33:28 -0400 (AST) Received: from localhost (localhost [127.0.0.1]) by rav.antivirus (Postfix) with ESMTP id 94BAF2EE21 for ; Sun, 14 Mar 2004 20:33:34 +0100 (MET) Received: from listserv.tudelft.nl (listserv.tudelft.nl [130.161.180.33]) by mailhost3.tudelft.nl (Postfix) with ESMTP id 14BB02EE12 for ; Sun, 14 Mar 2004 20:33:34 +0100 (MET) Received: from vulcanus.its.tudelft.nl (x193056-2.shuis-s.tudelft.nl [145.94.193.58]) by listserv.tudelft.nl (8.12.10/8.12.8) with ESMTP id i2EJXWDR014993 for ; Sun, 14 Mar 2004 20:33:33 +0100 (MET) Message-ID: <4054B36C.8030306@vulcanus.its.tudelft.nl> Date: Sun, 14 Mar 2004 20:33:00 +0100 From: Arjen van der Meijden User-Agent: Mozilla Thunderbird 0.5 (Windows/20040207) X-Accept-Language: en-us, en MIME-Version: 1.0 To: pgsql-performance@postgresql.org Subject: Large CASE-statement is pretty slow? Content-Type: text/plain; charset=us-ascii; format=flowed Content-Transfer-Encoding: 7bit X-Virus-Scanned: by amavisd-new at tudelft.nl X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200403/186 X-Sequence-Number: 6042 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 ;)