Re: PostgreSQL 9.1 : why is this query slow?

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