From: Paul Thomas <paul@tmsl.demon.co.uk>
To: Nick Trainor <nick.trainor@trainorthornton.co.uk>
Cc: pgsql-performance @ postgresql . org <pgsql-performance@postgresql.org>
Subject: Re: ORDER BY user defined function performance issues
Date: Fri, 11 Jun 2004 14:41:08 +0100
Message-ID: <20040611144108.A28875@bacon> (raw)
In-Reply-To: <20040611110837.E5F5215D888@ns.trainorthornton.co.uk>
References: <20040611110837.E5F5215D888@ns.trainorthornton.co.uk>
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 |
+------------------------------+---------------------------------------------+
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: paul@tmsl.demon.co.uk, nick.trainor@trainorthornton.co.uk
Subject: Re: ORDER BY user defined function performance issues
In-Reply-To: <20040611144108.A28875@bacon>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox