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

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

From: Laurette Cisneros <laurette(at)nextbus(dot)com>
To: Josh Berkus <josh(at)agliodbs(dot)com>
Cc: sszabo(at)megazone23(dot)bigpanda(dot)com,<pgsql-performance(at)postgresql(dot)org>, <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: Fwd: Re: [PERFORM] Odd Sort/Limit/Max Problem
Date: 2002-12-14 00:00:45
Message-ID: Pine.LNX.4.44.0212131559260.19814-100000@visor.corp.nextbus.com (view raw or flat)
Thread:
Lists: pgsql-hackerspgsql-performance
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(at)megazone23(dot)bigpanda(dot)com>
> To: Josh Berkus <josh(at)agliodbs(dot)com>
> Cc: <pgsql-performance(at)postgresql(dot)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(at)postgresql(dot)org
> 
> 
> -------------------------------------------------------
> 
> 

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


pgsql-performance by date

Next:From: Tom LaneDate: 2002-12-14 00:03:55
Subject: Re: Odd Sort/Limit/Max Problem
Previous:From: Hannu KrosingDate: 2002-12-13 22:22:08
Subject: Re: Odd Sort/Limit/Max Problem

pgsql-hackers by date

Next:From: Tom LaneDate: 2002-12-14 00:24:43
Subject: Re: PostgreSQL 7.3 installation on RedHat 8.0 fails
Previous:From: Neil ConwayDate: 2002-12-13 23:38:33
Subject: Re: Big 7.4 items

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