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

Re: PostgreSQL 9.1 : why is this query slow?

From: Joost Kraaijeveld <J(dot)Kraaijeveld(at)Askesis(dot)nl>
To: Kevin Grittner <Kevin(dot)Grittner(at)wicourts(dot)gov>
Cc: pgsql-performance(at)postgresql(dot)org
Subject: Re: PostgreSQL 9.1 : why is this query slow?
Date: 2011-11-29 15:39:46
Message-ID: 1322581186.16401.0.camel@panoramix.Askesis.nl (view raw or flat)
Thread:
Lists: pgsql-performance
On Mon, 2011-11-28 at 11:36 -0600, Kevin Grittner wrote:
> "Kevin Grittner" <Kevin(dot)Grittner(at)wicourts(dot)gov> wrote:
>  
> > If you really want the intersection, perhaps:
>  
> Or maybe closer:
>  
> with x as
>   (
>     select
>         word,
>         count(*) as countall,
>         count(case when filetype = 'f' then 1 else null end)
>           as countf,
>         count(case when filetype = 's' then 1 else null end)
>           as counts,
>         count(case when filetype = 'n' then 1 else null end)
>           as countn
>       from unique_words
>       group by word
>   )
> select word, least(countf, counts, countn) from x
>   where countf > 0 and counts > 0 and countn > 0
>   order by word;
>  
> Cranked out rather quickly and untested.

I tested it and it worked as advertised. Takes ~ 3 secs to complete.
Thanks.

-- 
Groeten,

Joost Kraaijeveld
Askesis B.V.
Molukkenstraat 14
6524NB Nijmegen
tel: 024-3888063 / 06-51855277
fax: 024-3608416
web: www.askesis.nl


In response to

pgsql-performance by date

Next:From: Marcus EngeneDate: 2011-11-29 16:58:56
Subject: Re: WAL in RAM
Previous:From: CSSDate: 2011-11-28 22:32:59
Subject: Re: Benchmarking tools, methods

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