On 20-05-2016 14:48, Felipe Santos wrote: > > Olá Danilo, > > Nenhum dos dois. > > Ele lê o índice do campo particionado nas tabelas filhas para saber se > deve entrar nos mesmos ou não. > > A trigger e os checks são acionados apenas nos eventos DML. >
Danilo,
Infelizmente vc está equivocado... o que determina o ajuste no plano de
execução para ir ou não apenas em determinada(s) partição é a CHECK
CONSTRAINT. Veja o exemplo abaixo (pouco extenso):
=====================================================================
1) Criar tabela e 3 particoes
fabrizio=# CREATE TABLE foo (id serial primary key, partkey date);
CREATE TABLE
fabrizio=# CREATE TABLE foo_20160520 () INHERITS (foo);
CREATE TABLE
fabrizio=# CREATE TABLE foo_20160521 () INHERITS (foo);
CREATE TABLE
fabrizio=# CREATE TABLE foo_20160522 () INHERITS (foo);
CREATE TABLE
fabrizio=# CREATE OR REPLACE FUNCTION foo_partition() RETURNS trigger AS
fabrizio-# $$
fabrizio$# BEGIN
fabrizio$# EXECUTE format('INSERT INTO %I VALUES (($1).*)',
'foo_'||to_char(NEW.partkey, 'FMYYYYMMDD'))
fabrizio$# USING NEW;
fabrizio$# RETURN NULL;
fabrizio$# END;
fabrizio$# $$
fabrizio-# LANGUAGE plpgsql;
CREATE FUNCTION
fabrizio=#
fabrizio=# CREATE TRIGGER foo_trigger BEFORE INSERT ON foo FOR EACH ROW
EXECUTE PROCEDURE foo_partition();
CREATE TRIGGER
fabrizio=# INSERT INTO foo (partkey) SELECT date '2016-05-20' + id%3
FROM generate_series(1,100) AS id;
INSERT 0 0
fabrizio=# SELECT count(*) FROM foo;
count
-------
100
(1 row)
=====================================================================
2) Executar algumas queries SEM a CHECK CONSTRAINT
fabrizio=# EXPLAIN (COSTS OFF) SELECT * FROM foo;
QUERY PLAN
--------------------------------
Append
-> Seq Scan on foo
-> Seq Scan on foo_20160520
-> Seq Scan on foo_20160521
-> Seq Scan on foo_20160522
(5 rows)
fabrizio=# EXPLAIN (COSTS OFF) SELECT * FROM foo WHERE partkey =
'2016-05-20';
QUERY PLAN
------------------------------------------------
Append
-> Seq Scan on foo
Filter: (partkey = '2016-05-20'::date)
-> Seq Scan on foo_20160520
Filter: (partkey = '2016-05-20'::date)
-> Seq Scan on foo_20160521
Filter: (partkey = '2016-05-20'::date)
-> Seq Scan on foo_20160522
Filter: (partkey = '2016-05-20'::date)
(9 rows)
fabrizio=# EXPLAIN (COSTS OFF) SELECT * FROM foo WHERE partkey =
'2016-05-21';
QUERY PLAN
------------------------------------------------
Append
-> Seq Scan on foo
Filter: (partkey = '2016-05-21'::date)
-> Seq Scan on foo_20160520
Filter: (partkey = '2016-05-21'::date)
-> Seq Scan on foo_20160521
Filter: (partkey = '2016-05-21'::date)
-> Seq Scan on foo_20160522
Filter: (partkey = '2016-05-21'::date)
(9 rows)
fabrizio=# EXPLAIN (COSTS OFF) SELECT * FROM foo WHERE partkey BETWEEN
'2016-05-20' AND '2016-05-21';
QUERY PLAN
---------------------------------------------------------------------------------------
Append
-> Seq Scan on foo
Filter: ((partkey >= '2016-05-20'::date) AND (partkey <=
'2016-05-21'::date))
-> Seq Scan on foo_20160520
Filter: ((partkey >= '2016-05-20'::date) AND (partkey <=
'2016-05-21'::date))
-> Seq Scan on foo_20160521
Filter: ((partkey >= '2016-05-20'::date) AND (partkey <=
'2016-05-21'::date))
-> Seq Scan on foo_20160522
Filter: ((partkey >= '2016-05-20'::date) AND (partkey <=
'2016-05-21'::date))
(9 rows)
=====================================================================
3) Adicionar as CHECK CONSTRAINTS
fabrizio=# EXPLAIN (COSTS OFF) SELECT * FROM foo;
QUERY PLAN
--------------------------------
Append
-> Seq Scan on foo
-> Seq Scan on foo_20160520
-> Seq Scan on foo_20160521
-> Seq Scan on foo_20160522
(5 rows)
fabrizio=# EXPLAIN (COSTS OFF) SELECT * FROM foo WHERE partkey =
'2016-05-20';
QUERY PLAN
------------------------------------------------
Append
-> Seq Scan on foo
Filter: (partkey = '2016-05-20'::date)
-> Seq Scan on foo_20160520
Filter: (partkey = '2016-05-20'::date)
(5 rows)
fabrizio=# EXPLAIN (COSTS OFF) SELECT * FROM foo WHERE partkey =
'2016-05-21';
QUERY PLAN
------------------------------------------------
Append
-> Seq Scan on foo
Filter: (partkey = '2016-05-21'::date)
-> Seq Scan on foo_20160521
Filter: (partkey = '2016-05-21'::date)
(5 rows)
fabrizio=# EXPLAIN (COSTS OFF) SELECT * FROM foo WHERE partkey BETWEEN
'2016-05-20' AND '2016-05-21';
QUERY PLAN
---------------------------------------------------------------------------------------
Append
-> Seq Scan on foo
Filter: ((partkey >= '2016-05-20'::date) AND (partkey <=
'2016-05-21'::date))
-> Seq Scan on foo_20160520
Filter: ((partkey >= '2016-05-20'::date) AND (partkey <=
'2016-05-21'::date))
-> Seq Scan on foo_20160521
Filter: ((partkey >= '2016-05-20'::date) AND (partkey <=
'2016-05-21'::date))
(7 rows)
Note que após adicionar as CHECK CONSTAINTS o planejador começou a ter
conhecimento de quais partições precisava efetuar a busca de acordo com
o predicado.
E também não existe nenhum índice associado a coluna "partkey" que foi
utilizada como chave do particionamento.
Att,
--
Fabrízio de Royes Mello Timbira - http://www.timbira.com.br/
PostgreSQL: Consultoria, Desenvolvimento, Suporte 24x7 e Treinamento
signature.asc
Description: OpenPGP digital signature
_______________________________________________ pgbr-geral mailing list pgbr-geral@listas.postgresql.org.br https://listas.postgresql.org.br/cgi-bin/mailman/listinfo/pgbr-geral
