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

From Kevin Grittner
Subject Re: PostgreSQL 9.1 : why is this query slow?
Date
Msg-id 4ED3713F020000250004355E@gw.wicourts.gov
Whole thread Raw
In response to Re: PostgreSQL 9.1 : why is this query slow?  (Joost Kraaijeveld <J.Kraaijeveld@Askesis.nl>)
Responses Re: PostgreSQL 9.1 : why is this query slow?  ("Kevin Grittner" <Kevin.Grittner@wicourts.gov>)
List pgsql-performance
Joost Kraaijeveld <J.Kraaijeveld@Askesis.nl> wrote:

> I would like the answer to be "the number of times the word
> appears in all three the queries", the intersection of the three
> queries.

That's still not entirely clear to me.  If there are two 'f' rows,
three 's' rows, and four 'n' rows, do you want to see an answer of 2
(which seems like the intersection you request here), 9 (which is
the sum), 24 (which is the product), or something else?

If you really want the intersection, perhaps:

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
          as counts,
        count(case when filetype = 'n' then 1 else null end) as
          as countn
      from unique_words
  )
select word, least(countf, counts, countn) from x
  where countf > 0 and counts > 0 and countn > 0
  order by word;

-Kevin

pgsql-performance by date:

Previous
From: Joost Kraaijeveld
Date:
Subject: Re: PostgreSQL 9.1 : why is this query slow?
Next
From: "Kevin Grittner"
Date:
Subject: Re: PostgreSQL 9.1 : why is this query slow?