como encadear RULEs postgres?

Da próxima vez, inclua a saída EXPLAIN em vez de nos fazer procurá-la em seus scripts. Não há garantia de que meu sistema esteja usando o mesmo plano que o seu (embora com seus dados de teste seja provável).

O sistema de regras aqui está funcionando corretamente. Primeiro, desejo incluir minhas próprias consultas de diagnóstico (observe que não executei EXPLAIN ANALYZE, pois estava interessado apenas em qual plano de consulta foi gerado):

rulestest=# explain DELETE FROM user_hits WHERE day = '2013-03-16';
                                              QUERY PLAN                        

--------------------------------------------------------------------------------
----------------------
 Delete on application_hits  (cost=0.00..3953181.85 rows=316094576 width=24)
   ->  Nested Loop  (cost=0.00..3953181.85 rows=316094576 width=24)
         ->  Seq Scan on user_hits  (cost=0.00..1887.00 rows=49763 width=10)
               Filter: (day = '2013-03-16'::date)
         ->  Materialize  (cost=0.00..128.53 rows=6352 width=22)
               ->  Nested Loop  (cost=0.00..96.78 rows=6352 width=22)
                     ->  Seq Scan on project_hits  (cost=0.00..14.93 rows=397 wi
dth=10)
                           Filter: (day = '2013-03-16'::date)
                     ->  Materialize  (cost=0.00..2.49 rows=16 width=16)
                           ->  Nested Loop  (cost=0.00..2.41 rows=16 width=16)
                                 ->  Seq Scan on application_hits  (cost=0.00..1
.10 rows=4 width=10)
                                       Filter: (day = '2013-03-16'::date)
                                 ->  Materialize  (cost=0.00..1.12 rows=4 width=
10)
                                       ->  Seq Scan on client_hits  (cost=0.00..
1.10 rows=4 width=10)
                                             Filter: (day = '2013-03-16'::date)

 Delete on client_hits  (cost=0.00..989722.41 rows=79023644 width=18)
   ->  Nested Loop  (cost=0.00..989722.41 rows=79023644 width=18)
         ->  Seq Scan on user_hits  (cost=0.00..1887.00 rows=49763 width=10)
               Filter: (day = '2013-03-16'::date)
         ->  Materialize  (cost=0.00..43.83 rows=1588 width=16)
               ->  Nested Loop  (cost=0.00..35.89 rows=1588 width=16)
                     ->  Seq Scan on project_hits  (cost=0.00..14.93 rows=397 wi
dth=10)
                           Filter: (day = '2013-03-16'::date)
                     ->  Materialize  (cost=0.00..1.12 rows=4 width=10)
                           ->  Seq Scan on client_hits  (cost=0.00..1.10 rows=4 
width=10)
                                 Filter: (day = '2013-03-16'::date)

 Delete on project_hits  (cost=0.00..248851.80 rows=19755911 width=12)
   ->  Nested Loop  (cost=0.00..248851.80 rows=19755911 width=12)
         ->  Seq Scan on user_hits  (cost=0.00..1887.00 rows=49763 width=10)
               Filter: (day = '2013-03-16'::date)
         ->  Materialize  (cost=0.00..16.91 rows=397 width=10)
               ->  Seq Scan on project_hits  (cost=0.00..14.93 rows=397 width=10
)
                     Filter: (day = '2013-03-16'::date)

 Delete on user_hits  (cost=0.00..1887.00 rows=49763 width=6)
   ->  Seq Scan on user_hits  (cost=0.00..1887.00 rows=49763 width=6)
         Filter: (day = '2013-03-16'::date)
(39 rows)

rulestest=# select distinct day from application_hits;
    day     
------------
 2013-03-15
 2013-03-16
(2 rows)

rulestest=# select count(*), day from application_hits group by day;
 count |    day     
-------+------------
     4 | 2013-03-15
     4 | 2013-03-16
(2 rows)

rulestest=# select count(*), day from client_hits group by day;
 count |    day     
-------+------------
     4 | 2013-03-15
     4 | 2013-03-16
(2 rows)

rulestest=# select count(*), day from project_hits group by day;
 count |    day     
-------+------------
   397 | 2013-03-15
   397 | 2013-03-16
(2 rows)

Se seus dados forem parecidos com os dados existentes, nem as regras nem os gatilhos funcionarão muito bem. Melhor será uma stored procedure que você passa um valor e ela apaga tudo que você quiser.

Primeiro, vamos observar que os índices aqui não levarão a lugar nenhum porque em todos os casos você está puxando metade das tabelas (eu adicionei índices no dia em todas as tabelas para ajudar o planejador, mas isso não fez nenhuma diferença real).

Você precisa começar com o que está fazendo com as REGRAS. As RULEs basicamente reescrevem as consultas e o fazem usando as formas mais robustas possíveis. Seu código também não corresponde ao seu exemplo, embora corresponda melhor à sua pergunta. Você tem regras em tabelas que se propagam para regras em outras tabelas que se propagam para regras em outras tabelas

Portanto, quando você delete from user_hits where [criteria], as regras transformam isso em um conjunto de consultas:

DELETE FROM application_hits 
 WHERE day IN (SELECT day FROM client_hits 
               WHERE day IN (SELECT day FROM user_hits WHERE [condition]));
DELETE FROM client_hits
  WHERE day IN (SELECT day FROM user_hits WHERE [condition]);
DELETE FROM user_hits WHERE [condition];

Agora, você pode pensar que poderíamos pular a varredura em client_hits no primeiro, mas não é isso que acontece aqui. O problema é que você pode ter dias em user_hits e application_hits que não estão em client_hits, então você realmente precisa verificar todas as tabelas.

Now here there is no magic bullet. A trigger isn't going to work much better because, while it gets to avoid scanning every table, it gets fired every row that gets deleted so you basically end up with the same nested loop sequential scans that are currently killing performance. It will work a bit better because it will delete rows along the way rather than rewriting the query along the way, but it isn't going to perform very well.

A much better solution is to just define a stored procedure and have the application call that. Something like:

CREATE OR REPLACE FUNCTION delete_stats_at_date(in_day date) RETURNS BOOL 
LANGUAGE SQL AS
$$
DELETE FROM application_hits WHERE day = $1;
DELETE FROM project_hits WHERE day = $1;
DELETE FROM client_hits WHERE day  = $1;
DELETE FROM user_hits WHERE day = $1;
SELECT TRUE;
$$;

On the test data this runs in 280 ms on my laptop.

One of the hard things regarding RULEs is remembering what they are and noting that the computer cannot, in fact, read your mind. This is why I would not consider them a beginner's tool.

-- this is the datamodel with the (correct?) PRIMARY and FOREIGN KEYs 
-- the `deferrable initially deferred` thing is there to accomodate the
-- table filling (which had to be altered to avoid duplicate keys)
-- ------------------------------------------------------------------------

DROP SCHEMA tmp CASCADE;
CREATE SCHEMA tmp ;
SET search_path = tmp ;

-- table definitions

CREATE TABLE application_hits
  ( zday DATE NOT NULL
  , application_id INTEGER NOT NULL
  , hits INTEGER NOT NULL DEFAULT 0
        , PRIMARY KEY (zday,application_id)
  );

CREATE TABLE client_hits
  ( zday DATE NOT NULL
  , client_id INTEGER NOT NULL
  , application_id INTEGER NOT NULL
  , hits INTEGER NOT NULL DEFAULT 0
        , PRIMARY KEY (zday,client_id,application_id)
        , FOREIGN KEY (zday,application_id)
           REFERENCES application_hits (zday,application_id) DEFERRABLE INITIALLY DEFERRED
  );

CREATE TABLE project_hits
  ( zday DATE NOT NULL
  , project_id INTEGER NOT NULL
  , client_id INTEGER NOT NULL
  , application_id INTEGER NOT NULL
  , hits INTEGER NOT NULL DEFAULT 0
        , PRIMARY KEY (zday,project_id,client_id,application_id)
        , FOREIGN KEY (zday,client_id,application_id)
           REFERENCES client_hits (zday,client_id,application_id) DEFERRABLE INITIALLY DEFERRED
  );

CREATE TABLE user_hits
  ( zday DATE NOT NULL
  , user_id INTEGER NOT NULL
  , project_id INTEGER NOT NULL
  , client_id INTEGER NOT NULL
  , application_id INTEGER NOT NULL
  , hits INTEGER NOT NULL DEFAULT 0
        , PRIMARY KEY (zday,user_id,project_id,client_id,application_id)
        , FOREIGN KEY (zday,project_id,client_id,application_id)
           REFERENCES project_hits (zday,project_id,client_id,application_id) DEFERRABLE INITIALLY DEFERRED
  );

--- rules
CREATE RULE delete_children AS ON DELETE TO user_hits
  DO ALSO
  UPDATE project_hits dst SET hits = dst.hits - OLD.hits
        WHERE dst.zday = OLD.zday
        AND dst.project_id = OLD.project_id
        AND dst.client_id = OLD.client_id
        AND dst.application_id = OLD.application_id
        ;

CREATE RULE delete_children AS ON DELETE TO project_hits
  DO ALSO
  UPDATE client_hits dst SET hits = dst.hits - OLD.hits
        WHERE dst.zday = OLD.zday
        AND dst.client_id = OLD.client_id
        AND dst.application_id = OLD.application_id
        ;

CREATE RULE delete_children AS ON DELETE TO client_hits
  DO ALSO
  UPDATE application_hits dst SET hits = dst.hits - OLD.hits
        WHERE dst.zday = OLD.zday
        AND dst.application_id = OLD.application_id
        ;

        -- Rules for UPDATE
CREATE RULE update_children AS ON UPDATE TO project_hits
  DO ALSO
  UPDATE client_hits dst SET hits = dst.hits - OLD.hits +NEW.hits
        WHERE dst.zday = OLD.zday
        AND dst.client_id = OLD.client_id
        AND dst.application_id = OLD.application_id
        ;

CREATE RULE update_children AS ON UPDATE TO client_hits
  DO ALSO
  UPDATE application_hits dst SET hits = dst.hits - OLD.hits +NEW.hits
        WHERE dst.zday = OLD.zday
        AND dst.application_id = OLD.application_id
        ;

-- filling user_hits
BEGIN WORK;
INSERT INTO user_hits (zday, user_id, project_id, client_id, application_id, hits)
SELECT
  current_date - (s%1313)::INT
        , (s % 1001)::INT , (s % 101)::INT , (s%13)::INT , (s%11)::INT
        , (50*random())::INT
FROM
  generate_series(1, 100000) s;

-- filling project_hits
INSERT INTO project_hits (zday, project_id, client_id, application_id, hits)
SELECT zday, project_id, client_id, application_id, SUM(hits)
FROM user_hits
GROUP BY zday, project_id, client_id, application_id
        ;

-- filling client_hits
INSERT INTO client_hits (zday, client_id, application_id, hits)
SELECT zday , client_id , application_id , SUM(hits)
FROM project_hits
GROUP BY zday, client_id, application_id;

-- filling application_hits
INSERT INTO application_hits (zday, application_id, hits)
SELECT zday, application_id, SUM(hits)
FROM client_hits
GROUP BY zday, application_id
        ;
COMMIT WORK;


-- create view for today
CREATE VIEW v_today
AS SELECT
  (SELECT SUM(hits) FROM user_hits WHERE zday = current_date) AS user_hits
  , (SELECT SUM(hits) FROM project_hits WHERE zday = current_date) AS project_hits
  , (SELECT SUM(hits) FROM client_hits WHERE zday = current_date) AS client_hits
  , (SELECT SUM(hits) FROM application_hits WHERE zday = current_date) AS application_hits
   ;


SELECT * FROM v_today;
-- explain analyse
DELETE FROM user_hits WHERE zday = current_date;
SELECT * FROM v_today;

E a seguir: crie algumas regras para INSERT... E não se esqueça dos casos desagradáveis ​​​​dos UPDATEprincipais arquivos.

OK, já faz muito tempo, mas se você executar um EXPLAINpodemos ver se minha lembrança está correta. Acho que o plano para as consultas subsidiárias está sendo criado na hora errada, antes que o planejador possa levar em consideração os índices. Acho que você está recebendo Table Scans.

Dito isso, você comparou que uma chave estrangeira de exclusão em cascata padrão é muito lenta? E que uma regra seria mais rápida?

[Editar após comentários]

CREATE RULE delete_children AS ON DELETE TO user_hits
  WHERE day = OLD.day  -- added
  DO ALSO
  DELETE FROM project_hits WHERE day = OLD.day;

Lendo os documentos com atenção, parece que (ao contrário de um gatilho) quando uma regra é usada, ela será (na ausência da cláusula where adicionada) aplicada a toda a tabela original?!