Received: from rossini.ednet.co.uk (root@rossini.ednet.co.uk [212.20.226.132]) by hub.org (8.9.3/8.9.3) with ESMTP id LAA47048 for ; Tue, 18 Apr 2000 11:31:21 -0400 (EDT) (envelope-from allan.kelly@buildstore.co.uk) Received: from qube.buildstore.office (dialup93.ednet.co.uk [212.20.227.156]) by rossini.ednet.co.uk (8.9.3/8.9.3) with ESMTP id QAA03003 for ; Tue, 18 Apr 2000 16:31:07 +0100 Received: from buildstore.co.uk (babyb.buildstore.office [192.168.1.78]) by qube.buildstore.office (8.9.2/8.9.2) with ESMTP id QAA24030 for ; Tue, 18 Apr 2000 16:18:05 +0100 (BST) Message-ID: <38FC7C4D.7D8E1C12@buildstore.co.uk> Date: Tue, 18 Apr 2000 16:16:29 +0100 From: Allan Kelly Organization: buildstore X-Mailer: Mozilla 4.6 [en-gb]C-CCK-MCD NetscapeOnline.co.uk (Win98; I) X-Accept-Language: en-GB,en MIME-Version: 1.0 To: "pgsql-sql@postgresql.org" Subject: Mystery: functions are slow with group by, but... Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit X-Archive-Number: 200004/186 Hi there. I appear to have much quicker performance by asking for more work! First of all, though: can I see the definition of a function in psql? \df total_live_a4_properties( text ) just gives me the args, return type etc. Hox can I see the AS '...' clause? Q2: I have a big performance problem using a function: create function total_live_a4_properties( text ) returns int4 as 'select count(*) from properties where status<>''sold'' and status<>''suspended'' and a4 = $1' language 'sql'; This takes 5-10 _minutes_ : select a4, total_live_a4_properties( a4 ) from properties group by a4; There are 5132 records in the properties table, and 64 distinct a4 values. Explain gives NOTICE: QUERY PLAN: Group ( cost=411.70 rows=5112 width=12) -> Sort ( cost=411.70 rows=5112 width=12) -> Seq Scan on Properties ( cost=411.70 rows=5112 width=12) However this next one is < 3 _seconds_! select a4, count(*), (100*count(*)/total_live_a4_properties( a4 )) as percentage from properties group by a4; Explain gives NOTICE: QUERY PLAN: Aggregate ( cost=411.70 rows=5112 width=12) Group ( cost=411.70 rows=5112 width=12) -> Sort ( cost=411.70 rows=5112 width=12) -> Seq Scan on Properties ( cost=411.70 rows=5112 width=12) Can anyone tell me what's going on? thanks, al. -- # Allan Kelly http://www.plotsearch.co.uk # (+44) (0)131 524 8500 # allan.kelly@buildstore.co.uk... .. # /Software Engineer/i . . . . . # ------------------------------ * . . . . . # "If you are a Visual Basic programmer, * . . . # these details are none of your business." * . . . # Mr Bunny's Guide to Active X, by Carlton Egremont III * . . # ------------------------------ vi: set noet tw=80 sts=4 ts=8 : .