Re: PostgreSQL 9.1 : why is this query slow? - Mailing list pgsql-performance

From Joost Kraaijeveld
Subject Re: PostgreSQL 9.1 : why is this query slow?
Date
Msg-id 1322581186.16401.0.camel@panoramix.Askesis.nl
Whole thread Raw
In response to Re: PostgreSQL 9.1 : why is this query slow?  ("Kevin Grittner" <Kevin.Grittner@wicourts.gov>)
List pgsql-performance
On Mon, 2011-11-28 at 11:36 -0600, Kevin Grittner wrote:
> "Kevin Grittner" <Kevin.Grittner@wicourts.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


pgsql-performance by date:

Previous
From: CSS
Date:
Subject: Re: Benchmarking tools, methods
Next
From: Marcus Engene
Date:
Subject: Re: WAL in RAM