From: | Paul Thomas <paul(at)tmsl(dot)demon(dot)co(dot)uk> |
---|---|
To: | Nick Trainor <nick(dot)trainor(at)trainorthornton(dot)co(dot)uk> |
Cc: | "pgsql-performance (at) postgresql (dot) org" <pgsql-performance(at)postgresql(dot)org> |
Subject: | Re: ORDER BY user defined function performance issues |
Date: | 2004-06-11 13:41:08 |
Message-ID: | 20040611144108.A28875@bacon |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
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 |
+------------------------------+---------------------------------------------+
From | Date | Subject | |
---|---|---|---|
Next Message | Domenico Sgarbossa | 2004-06-11 13:54:44 | Problems with vacuum! |
Previous Message | Nick Trainor | 2004-06-11 11:14:55 | ORDER BY user defined function performance issues |