Better way to write aggregates?

Поиск
Список
Период
Сортировка
От Jan Dittmer
Тема Better way to write aggregates?
Дата
Msg-id 444899B6.6090001@l4x.org
обсуждение исходный текст
Ответы Re: Better way to write aggregates?
Список pgsql-performance
Hi,

I more or less often come about the problem of aggregating a
child table counting it's different states. The cleanest solution
I've come up with so far is:

BEGIN;
CREATE TABLE parent (
    id int not null,
      name text not null,
    UNIQUE(id)
);

CREATE TABLE child (
    name text not null,
    state int not null,
    parent int not null references parent(id)
);

CREATE VIEW parent_childs AS
SELECT
    c.parent,
    count(c.state) as childtotal,
    count(c.state) - count(nullif(c.state,1)) as childstate1,
    count(c.state) - count(nullif(c.state,2)) as childstate2,
    count(c.state) - count(nullif(c.state,3)) as childstate3
FROM child c
GROUP BY parent;

CREATE VIEW parent_view AS
SELECT p.*,
pc.*
FROM parent p
LEFT JOIN parent_childs pc ON (p.id = pc.parent);
COMMIT;

Is this the fastest way to build these aggregates (not considering
tricks with triggers, etc)? The count(state) - count(nullif(...)) looks
a bit clumsy.
I also experimented with a pgsql function to sum these up, but considered
it as not-so-nice and it also always forces a sequential scan on the
data.

Thanks for any advice,

Jan


В списке pgsql-performance по дате отправления:

Предыдущее
От: Markus Schaber
Дата:
Сообщение: Re: Introducing a new linux readahead framework
Следующее
От: luchot
Дата:
Сообщение: Little use of CPU ( < 5%)