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 0D660D1E155 for ; Sun, 14 Mar 2004 23:21:54 +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 87461-09 for ; Sun, 14 Mar 2004 19:21:58 -0400 (AST) Received: from mailhost3.tudelft.nl (mailhost3.tudelft.nl [130.161.180.14]) by svr1.postgresql.org (Postfix) with ESMTP id 54D37D1E154 for ; Sun, 14 Mar 2004 19:21:50 -0400 (AST) Received: from localhost (localhost [127.0.0.1]) by rav.antivirus (Postfix) with ESMTP id 27C3E2EE9F; Mon, 15 Mar 2004 00:21:58 +0100 (MET) Received: from listserv.tudelft.nl (listserv.tudelft.nl [130.161.180.33]) by mailhost3.tudelft.nl (Postfix) with ESMTP id EA9E82EE8E; Mon, 15 Mar 2004 00:21:57 +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 i2ENLuDR022367; Mon, 15 Mar 2004 00:21:57 +0100 (MET) Message-ID: <4054E8ED.3090200@vulcanus.its.tudelft.nl> Date: Mon, 15 Mar 2004 00:21:17 +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: Tom Lane Cc: pgsql-performance@postgresql.org Subject: Re: Large CASE-statement is pretty slow? References: <4054B36C.8030306@vulcanus.its.tudelft.nl> <25013.1079298580@sss.pgh.pa.us> In-Reply-To: <25013.1079298580@sss.pgh.pa.us> 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/190 X-Sequence-Number: 6046 Tom Lane wrote: > Arjen van der Meijden writes: > >>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. > > > Hm, you mean one single SELECT, one single CASE? How many WHEN clauses > exactly? Exactly what did a typical clause of the CASE look like? Yes, one SELECT-query with one single CASE-statement. The CASE-statement had the simple-case-structure like: SELECT CASE UserID WHEN 1 THEN 1 WHEN 34 THEN 2 ... etc I noticed, by the way, that the ordering is on the THEN y parameter, the x parameter (WHEN x THEN y) is "more or less increasing". But some numbers: The table I did my tests on has 88291 rows, I did the select on the integer primary key, so the CASE was the only column in the select. I'm running the query again on a table that has only the primary key of my original table and it seems to be as slow. I'm not really sure how many WHEN's there are in that CASE, but it is supposed to be a relocation of all primary key-values to some other value, so it will contain some number close to that 88291. > I wouldn't be too surprised to find some bit of code that's O(N^2) in > the number of arms of the CASE, or something like that; it's not an area > that we've ever felt the need to optimize. But I'd like a fairly > specific test case before trying to look into it. Well, I have discarded this type of query as "too inefficient" and found a better way, so don't feel the need to optimize it just because I noticed it is slow with very large CASEs. Although CASEs with a few hundred WHENs wont be that uncommon and might improve a bit as well? I can send you the "primary key only"-table and the query off list if you want to. That won't make me violate any privacy rule and is probably a good test case? Best regards, Arjen van der Meijden