Re: PostgreSQL 9.1 : why is this query slow?

От: Joost Kraaijeveld
Тема: Re: PostgreSQL 9.1 : why is this query slow?
Дата: ,
Msg-id: 1322581186.16401.0.camel@panoramix.Askesis.nl
(см: обсуждение, исходный текст)
Ответ на: Re: PostgreSQL 9.1 : why is this query slow?  ("Kevin Grittner")
Список: pgsql-performance

Скрыть дерево обсуждения

PostgreSQL 9.1 : why is this query slow?  (Joost Kraaijeveld, )
 Re: PostgreSQL 9.1 : why is this query slow?  (Joost Kraaijeveld, )
  Re: PostgreSQL 9.1 : why is this query slow?  ("Kevin Grittner", )
   Re: PostgreSQL 9.1 : why is this query slow?  (Joost Kraaijeveld, )
    Re: PostgreSQL 9.1 : why is this query slow?  ("Kevin Grittner", )
     Re: PostgreSQL 9.1 : why is this query slow?  ("Kevin Grittner", )
      Re: PostgreSQL 9.1 : why is this query slow?  (Joost Kraaijeveld, )
 Re: PostgreSQL 9.1 : why is this query slow?  (Tomas Vondra, )

On Mon, 2011-11-28 at 11:36 -0600, Kevin Grittner wrote:
> "Kevin Grittner" <> 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 по дате сообщения:

От: MirrorX
Дата:
Сообщение: vacuum internals and performance affect
От: Benjamin Johnson
Дата:
Сообщение: Guidance Requested - Bulk Inserting + Queries