Skip site navigation (1) Skip section navigation (2)

Re: Odd Sort/Limit/Max Problem

From: Stephan Szabo <sszabo(at)megazone23(dot)bigpanda(dot)com>
To: Josh Berkus <josh(at)agliodbs(dot)com>
Cc: <pgsql-performance(at)postgresql(dot)org>
Subject: Re: Odd Sort/Limit/Max Problem
Date: 2002-12-13 20:10:20
Message-ID: 20021213120738.D25935-100000@megazone23.bigpanda.com (view raw or flat)
Thread:
Lists: pgsql-performance
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.



In response to

pgsql-performance by date

Next:From: Tom LaneDate: 2002-12-13 20:24:23
Subject: Re: Odd Sort/Limit/Max Problem
Previous:From: Josh BerkusDate: 2002-12-13 19:55:51
Subject: Odd Sort/Limit/Max Problem

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group