Re: PostgreSQL 9.1 : why is this query slow?

From: "Kevin Grittner" <Kevin(dot)Grittner(at)wicourts(dot)gov>
To: "Joost Kraaijeveld" <J(dot)Kraaijeveld(at)Askesis(dot)nl>
Cc: <pgsql-performance(at)postgresql(dot)org>
Subject: Re: PostgreSQL 9.1 : why is this query slow?
Date: 2011-11-28 17:36:57
Message-ID: 4ED372590200002500043563@gw.wicourts.gov
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-performance

"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.

-Kevin

In response to

Responses

Browse pgsql-performance by date

  From Date Subject
Next Message Tomas Vondra 2011-11-28 19:32:20 Re: PostgreSQL 9.1 : why is this query slow?
Previous Message Kevin Grittner 2011-11-28 17:32:15 Re: PostgreSQL 9.1 : why is this query slow?