Re: Fwd: Re: [PERFORM] Odd Sort/Limit/Max Problem

Thank you for a good workaround.

Even BETTER would be to fix the aggregates so workarounds wouldn't have to
be found.

Thanks again,

L.
On Fri, 13 Dec 2002, Josh Berkus wrote:

---------- Forwarded Message ----------

Subject: Re: [PERFORM] Odd Sort/Limit/Max Problem
Date: Fri, 13 Dec 2002 12:10:20 -0800 (PST)
From: Stephan Szabo <sszabo@megazone23.bigpanda.com>
To: Josh Berkus <josh@agliodbs.com>
Cc: <pgsql-performance@postgresql.org>

On Fri, 13 Dec 2002, Josh Berkus wrote:

First, as expected, a regular aggregate is slow:

So we use the workaround standard for PostgreSQL:

... which is fast, but returns NULL, since nulls sort to the bottom! So we
add IS NOT NULL:

jwnet=> explain analyze select date_resolved from case_clients where
date_resolved is not null order by date_resolved desc limit 1;
NOTICE: QUERY PLAN:

Limit (cost=0.00..4.06 rows=1 width=4) (actual time=219.63..219.64 rows=1
loops=1)
-> Index Scan Backward using idx_caseclients_resolved on case_clients
(cost=0.00..163420.59 rows=40272 width=4) (actual time=219.62..219.62 rows=2
loops=1)
Total runtime: 219.76 msec

Aieee! Almost as slow as the aggregate!

I'd suggest trying a partial index on date_resolved where date_resolve is
not null. In my simple tests on about 200,000 rows of ints where 50% are
null that sort of index cut the runtime on my machine from 407.66 msec to
0.15 msec.

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org

-------------------------------------------------------

--
Laurette Cisneros
The Database Group
(510) 420-3137
NextBus Information Systems, Inc.
www.nextbus.com
----------------------------------
There's more to life than just SQL.