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
