Re: [PERFORM] ORDER BY user defined function performance issues

[Paul Thomas]

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   |
+--+-+

---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster

Re: [PERFORM] ORDER BY user defined function performance issues

[Tom Lane]

Nick Trainor [EMAIL PROTECTED] writes:
 What am I missing here?

The ORDER BY query has to evaluate the function at *every* row of the
table before it can sort.  The other query was only evaluating the
function at twenty rows.

regards, tom lane

---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
  subscribe-nomail command to [EMAIL PROTECTED] so that your
  message can get through to the mailing list cleanly