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 980A3D1DA9B for ; Mon, 15 Mar 2004 20:34:35 +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 80164-03 for ; Mon, 15 Mar 2004 16:34:35 -0400 (AST) Received: from sss.pgh.pa.us (unknown [192.204.191.242]) by svr1.postgresql.org (Postfix) with ESMTP id 0140ED1C9E1 for ; Mon, 15 Mar 2004 16:34:33 -0400 (AST) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.12.11/8.12.11) with ESMTP id i2FKYViB003224; Mon, 15 Mar 2004 15:34:31 -0500 (EST) To: Arjen van der Meijden Cc: pgsql-performance@postgresql.org Subject: Re: Large CASE-statement is pretty slow? In-reply-to: <4054E8ED.3090200@vulcanus.its.tudelft.nl> References: <4054B36C.8030306@vulcanus.its.tudelft.nl> <25013.1079298580@sss.pgh.pa.us> <4054E8ED.3090200@vulcanus.its.tudelft.nl> Comments: In-reply-to Arjen van der Meijden message dated "Mon, 15 Mar 2004 00:21:17 +0100" Date: Mon, 15 Mar 2004 15:34:31 -0500 Message-ID: <3223.1079382871@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200403/201 X-Sequence-Number: 6057 Arjen van der Meijden writes: > [ huge CASE is pretty slow ] I did some profiling of the test case that Arjen was kind enough to send me. It seems there are two distinct problems. One is that the parser uses repeated lappend()'s to construct the list of CASE arms; this makes building the structure O(N^2) in the number of arms. (If you simply try to EXPLAIN the query, you find out that the parse time is about a third of the run time :-( ... and 90% of that is spent inside nconc() which is the guts of lappend.) This problem is slated to be fixed by Neil Conway's upcoming rewrite of the list support, which will convert lappend into a constant-time operation. The other difficulty is that the evaluation machinery for arithmetic expressions has a lot of overhead. The profile run shows: % cumulative self self total time seconds seconds calls s/call s/call name 38.15 41.92 41.92 229646 0.00 0.00 nconc 21.76 65.84 23.92 199054454 0.00 0.00 ExecEvalExpr 11.38 78.34 12.50 10000 0.00 0.00 ExecEvalCase 8.43 87.61 9.27 66348151 0.00 0.00 ExecEvalFuncArgs 8.12 96.54 8.93 66348151 0.00 0.00 ExecMakeFunctionResult 2.96 99.78 3.25 66348151 0.00 0.00 ExecEvalVar 1.23 101.14 1.36 10058 0.00 0.00 AllocSetCheck 1.23 102.49 1.35 66348151 0.00 0.00 ExecEvalOper 1.12 103.72 1.24 76537 0.00 0.00 OpernameGetCandidates 0.85 104.66 0.94 66424693 0.00 0.00 int4eq (Note: I added LIMIT 10000 to the query so that the CASE is only carried out 10000 times, rather than nearly 90000 times as in Arjen's original test case. Without this, the call-counter overflows for ExecEvalExpr, and the time percentages seem to get confused. One must recognize though that this overstates the parser overhead compared to the original test case.) Clearly the useful work (int4eq) is getting a bit swamped by the ExecEval mechanism. I have some ideas about reducing the overhead, which I'll post to the pghackers list in a bit. regards, tom lane