X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 8A845D1B1DC for ; Fri, 11 Jun 2004 10:41:23 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 17750-05 for ; Fri, 11 Jun 2004 13:41:18 +0000 (GMT) Received: from anchor-post-31.mail.demon.net (anchor-post-31.mail.demon.net [194.217.242.89]) by svr1.postgresql.org (Postfix) with ESMTP id 360D3D1B1CB for ; Fri, 11 Jun 2004 10:41:11 -0300 (ADT) Received: from tmsl-adsl.demon.co.uk ([80.177.114.181] helo=bacon.tmsl.demon.co.uk) by anchor-post-31.mail.demon.net with esmtp (Exim 3.35 #1) id 1BYmHS-000DLR-0V; Fri, 11 Jun 2004 14:41:14 +0100 Date: Fri, 11 Jun 2004 14:41:08 +0100 From: Paul Thomas To: Nick Trainor Cc: "pgsql-performance @ postgresql . org" Subject: Re: ORDER BY user defined function performance issues Message-ID: <20040611144108.A28875@bacon> References: <20040611110837.E5F5215D888@ns.trainorthornton.co.uk> Mime-Version: 1.0 Content-Type: text/plain; format=flowed; charset=ISO-8859-1 In-Reply-To: <20040611110837.E5F5215D888@ns.trainorthornton.co.uk>; from nick.trainor@trainorthornton.co.uk on Fri, Jun 11, 2004 at 12:14:55 +0100 X-Mailer: Balsa 1.2.3 Lines: 35 X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.0 tagged_above=0.0 required=5.0 tests= X-Spam-Level: X-Archive-Number: 200406/124 X-Sequence-Number: 7182 On 11/06/2004 12:14 Nick Trainor wrote: > [snip] > However, when I seek to ORDER the results, then it takes 'forever': > > EXPLAIN ANALYSE SELECT t1.value1,t1.value2, > getday_total('1','23',t1.id::integer,'31','59','2','2004','182','153','6','2004','0') > FROM tblitem t1 WHERE t1.type_id=23::int2 and (t1.id >= 1::int8 and > t1.id<=9223372036854775807::int8) > ORDER BY > getday_total('1','23',t1.id::integer,'31','59','2','2004','182','153','6','2004','0') > DESC > OFFSET 0 LIMIT 20; I expect that pg is having to evaluate your function every time it does a compare within its sort. Something like SELECT t1.value1,t1.value2, getday_total(..) AS foo FROM tblitem t1 WHERE t1.type_id=23::int2 and (t1.id >= 1::int8 and t1.id<=9223372036854775807::int8) ORDER BY foo might work. Otherwise try selecting into a temp table then doing the order by on that table. HTH -- Paul Thomas +------------------------------+---------------------------------------------+ | Thomas Micro Systems Limited | Software Solutions for Business | | Computer Consultants | http://www.thomas-micro-systems-ltd.co.uk | +------------------------------+---------------------------------------------+