pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
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   |
+------------------------------+---------------------------------------------+



view thread (3+ messages)  latest in thread

Message-ID: <20040611144108.A28875@bacon>
Permalink:  ../20040611144108.A28875@bacon/
Also on:    postgresql.org/message-id/20040611144108.A28875@bacon

 · 

reply

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