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 EF6C3D1DAA7 for ; Mon, 15 Mar 2004 17:15: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 00670-03 for ; Mon, 15 Mar 2004 13:15:52 -0400 (AST) Received: from smtp.istop.com (dci.doncaster.on.ca [66.11.168.194]) by svr1.postgresql.org (Postfix) with ESMTP id C23DFD1DA9B for ; Mon, 15 Mar 2004 13:15:49 -0400 (AST) Received: from stark.xeocode.com (gsstark.mtl.istop.com [66.11.160.162]) by smtp.istop.com (Postfix) with ESMTP id ED7FB17C30A; Mon, 15 Mar 2004 12:15:45 -0500 (EST) Received: from localhost ([127.0.0.1] helo=stark.xeocode.com) by stark.xeocode.com with smtp (Exim 3.36 #1 (Debian)) id 1B2vgn-0006Vd-00; Mon, 15 Mar 2004 12:15:45 -0500 To: Arjen van der Meijden Cc: pgsql-performance@postgresql.org Subject: Re: Large CASE-statement is pretty slow? References: <4054B36C.8030306@vulcanus.its.tudelft.nl> In-Reply-To: <4054B36C.8030306@vulcanus.its.tudelft.nl> From: Greg Stark Organization: The Emacs Conspiracy; member since 1992 Date: 15 Mar 2004 12:15:45 -0500 Message-ID: <87y8q22fr2.fsf@stark.xeocode.com> Lines: 18 User-Agent: Gnus/5.09 (Gnus v5.9.0) Emacs/21.3 MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200403/194 X-Sequence-Number: 6050 Arjen van der Meijden writes: > > 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. Was this the select with the CASE, or the update? If you did the update and have lots of foreign key references to the table then every record that's updated forces a check to make sure there are no references to that record (or an update if it's ON UPDATE CASCADE). If there are no indexes on the referencing table columns that will be very slow. -- greg