Re: ORDER BY user defined function performance issues

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