Re: Particionamiento de tablas

El 28/11/05, Alvaro Herrera<alvherre(at)commandprompt(dot)com> escribió:
> Ing. Jhon Carrillo // Caracas, Venezuela escribió:
> > Saludos,
> >
> > estoy haciendo pruebas con el particionamiento de tablas del 8.1, pero
> > cuando hago un explain de un query ej. select * from
> > tabla_particionada where campo=valor el motor chequea todos los
> > indices de todas las tablas secundarias, ¿Esto es correcto? ¿Debería
> > el motor chequear solo los indices donde esta la información?
> >
> > ya coloque SET constraint_exclusion = on;
>
> Observa que necesitas tener restricciones CHECK apropiadas en cada
> particion! Que tal si nos enseñas la definicion de las tablas?

Esto fue lo que hice, es una prueba parecida a el ejemplo de la
documentación, el problema es que me chequea todos los indices de las
tablas particionadas y NO hace exclusión :

CREATE TABLE tabla_data (
tabla_data_id serial,
tabla_data_date timestamp NOT NULL,
)

CREATE TABLE tabla_data_0501 (
CHECK ( tabla_data_date >= DATE '2005-01-01' AND tabla_data_date <
DATE '2005-01-30' )
) INHERITS (tabla_data);

CREATE TABLE tabla_data_0502 (
CHECK ( tabla_data_date >= DATE '2005-02-01' AND tabla_data_date <
DATE '2005-02-28' )
) INHERITS (tabla_data);

CREATE TABLE tabla_data_0503 (
CHECK ( tabla_data_date >= DATE '2005-03-01' AND tabla_data_date <
DATE '2005-03-30' )
) INHERITS (tabla_data);

CREATE TABLE tabla_data_0504 (
CHECK ( tabla_data_date >= DATE '2005-04-01' AND tabla_data_date <
DATE '2005-04-30' )
) INHERITS (tabla_data);

CREATE TABLE tabla_data_0505 (
CHECK ( tabla_data_date >= DATE '2005-05-01' AND tabla_data_date <
DATE '2005-05-30' )
) INHERITS (tabla_data);

CREATE TABLE tabla_data_0506 (
CHECK ( tabla_data_date >= DATE '2005-06-01' AND tabla_data_date <
DATE '2005-06-30' )
) INHERITS (tabla_data);

CREATE TABLE tabla_data_0601 (
CHECK ( tabla_data_date >= DATE '2006-01-01' AND tabla_data_date <
DATE '2006-01-30' )
) INHERITS (tabla_data);

CREATE TABLE tabla_data_0602 (
CHECK ( tabla_data_date >= DATE '2006-02-01' AND tabla_data_date <
DATE '2006-02-28' )
) INHERITS (tabla_data);

CREATE TABLE tabla_data_0603 (
CHECK ( tabla_data_date >= DATE '2006-03-01' AND tabla_data_date <
DATE '2006-03-30' )
) INHERITS (tabla_data);

CREATE TABLE tabla_data_0604 (
CHECK ( tabla_data_date >= DATE '2006-04-01' AND tabla_data_date <
DATE '2006-04-30' )
) INHERITS (tabla_data);

CREATE TABLE tabla_data_0605 (
CHECK ( tabla_data_date >= DATE '2006-05-01' AND tabla_data_date <
DATE '2006-05-30' )
) INHERITS (tabla_data);

CREATE TABLE tabla_data_0606 (
) INHERITS (tabla_data);

CREATE INDEX tabla_data_0501_INDEX_tabla_data_date ON tabla_data_0501
(tabla_data_date);
CREATE INDEX tabla_data_0502_INDEX_tabla_data_date ON tabla_data_0502
(tabla_data_date);
CREATE INDEX tabla_data_0503_INDEX_tabla_data_date ON tabla_data_0503
(tabla_data_date);
CREATE INDEX tabla_data_0504_INDEX_tabla_data_date ON tabla_data_0504
(tabla_data_date);
CREATE INDEX tabla_data_0505_INDEX_tabla_data_date ON tabla_data_0505
(tabla_data_date);
CREATE INDEX tabla_data_0506_INDEX_tabla_data_date ON tabla_data_0506
(tabla_data_date);
CREATE INDEX tabla_data_0601_INDEX_tabla_data_date ON tabla_data_0601
(tabla_data_date);
CREATE INDEX tabla_data_0602_INDEX_tabla_data_date ON tabla_data_0602
(tabla_data_date);
CREATE INDEX tabla_data_0603_INDEX_tabla_data_date ON tabla_data_0603
(tabla_data_date);
CREATE INDEX tabla_data_0604_INDEX_tabla_data_date ON tabla_data_0604
(tabla_data_date);
CREATE INDEX tabla_data_0605_INDEX_tabla_data_date ON tabla_data_0605
(tabla_data_date);
CREATE INDEX tabla_data_0606_INDEX_tabla_data_date ON tabla_data_0606
(tabla_data_date);

CREATE OR REPLACE RULE tabla_data_0501_rule AS
ON INSERT TO tabla_data WHERE
( tabla_data_date >= DATE '2005-01-01' AND tabla_data_date < DATE
'2005-01-30' )
DO INSTEAD
INSERT INTO tabla_data_0501 VALUES ( NEW.tabla_data_date);

CREATE OR REPLACE RULE tabla_data_0502_rule AS
ON INSERT TO tabla_data WHERE
( tabla_data_date >= DATE '2005-02-01' AND tabla_data_date < DATE
'2005-02-28' )
DO INSTEAD
INSERT INTO tabla_data_0502 VALUES (NEW.tabla_data_date);

CREATE OR REPLACE RULE tabla_data_0503_rule AS
ON INSERT TO tabla_data WHERE
( tabla_data_date >= DATE '2005-03-01' AND tabla_data_date < DATE
'2005-03-30' )
DO INSTEAD
INSERT INTO tabla_data_0503 VALUES (NEW.tabla_data_date);

CREATE OR REPLACE RULE tabla_data_0504_rule AS
ON INSERT TO tabla_data WHERE
( tabla_data_date >= DATE '2005-04-01' AND tabla_data_date < DATE
'2005-04-30' )
DO INSTEAD
INSERT INTO tabla_data_0504 VALUES (NEW.tabla_data_date);

CREATE OR REPLACE RULE tabla_data_0505_rule AS
ON INSERT TO tabla_data WHERE
( tabla_data_date >= DATE '2005-05-01' AND tabla_data_date < DATE
'2005-05-30' )
DO INSTEAD
INSERT INTO tabla_data_0505 VALUES (NEW.tabla_data_date);

CREATE OR REPLACE RULE tabla_data_0506_rule AS
ON INSERT TO tabla_data WHERE
( tabla_data_date >= DATE '2005-06-01' AND tabla_data_date < DATE
'2005-06-30' )
DO INSTEAD
INSERT INTO tabla_data_0506 VALUES (NEW.tabla_data_date);

CREATE OR REPLACE RULE tabla_data_0601_rule AS
ON INSERT TO tabla_data WHERE
( tabla_data_date >= DATE '2006-01-01' AND tabla_data_date < DATE
'2006-01-30' )
DO INSTEAD
INSERT INTO tabla_data_0601 VALUES (NEW.tabla_data_date);

CREATE OR REPLACE RULE tabla_data_0602_rule AS
ON INSERT TO tabla_data WHERE
( tabla_data_date >= DATE '2006-02-01' AND tabla_data_date < DATE
'2006-02-28' )
DO INSTEAD
INSERT INTO tabla_data_0602 VALUES ( NEW.tabla_data_date);

CREATE OR REPLACE RULE tabla_data_0603_rule AS
ON INSERT TO tabla_data WHERE
( tabla_data_date >= DATE '2006-03-01' AND tabla_data_date < DATE
'2006-03-30' )
DO INSTEAD
INSERT INTO tabla_data_0603 VALUES (NEW.tabla_data_date);

CREATE OR REPLACE RULE tabla_data_0604_rule AS
ON INSERT TO tabla_data WHERE
( tabla_data_date >= DATE '2006-04-01' AND tabla_data_date < DATE
'2006-04-30' )
DO INSTEAD
INSERT INTO tabla_data_0604 VALUES (NEW.tabla_data_date);

CREATE OR REPLACE RULE tabla_data_0605_rule AS
ON INSERT TO tabla_data WHERE
( tabla_data_date >= DATE '2006-05-01' AND tabla_data_date < DATE
'2006-05-30' )
DO INSTEAD
INSERT INTO tabla_data_0605 VALUES (NEW.tabla_data_date);

CREATE OR REPLACE RULE tabla_data_0606_rule AS
ON INSERT TO tabla_data WHERE
DO INSTEAD
INSERT INTO tabla_data_0606 VALUES (NEW.tabla_data_date);

--Para cargar
CREATE OR REPLACE FUNCTION cargar_datos_audimeter() RETURNS text as
$BODY$
DECLARE
i int8;
BEGIN
for i in 1..1000 loop
INSERT INTO tabla_data (tabla_data_date)
VALUES ( current_date - 328 + i);
end loop;
return 'OK';
END;
$BODY$
LANGUAGE 'plpgsql';

)

vacuum verbose analyze tabla_data;
SET constraint_exclusion = on;
select * from tabla_data where tabla_data_date = DATE '2006-01-01';

>
> > Se le deben colocar indices a la tabla principal o solo a las secundarias?
>
> Humm ..? Los indices no se heredan, creo.
>
> > También, le tengo rules a la tabla principal para enviarle la fila a
> > la tabla secundaria correspondiente como indica el ejemplo de la
> > documentación, ¿queda una copia de la fila en la primaria? ó ¿debo
> > borrarla? ó ¿el motor ya hace el trabajo?
>
> Si la regla es INSTEAD, el motor ya hace el trabajo. Si es ALSO (o no
> especificas nada), entonces la fila se insertara duplicada.
>
> --
> Alvaro Herrera http://www.CommandPrompt.com/
> PostgreSQL Replication, Consulting, Custom Development, 24x7 support
>

--
Jhon Carrillo
DBA / Software Engineer
Caracas-Venezuela