Re: Partitionement

Hello,

J'ai fait le petit test suivant :

Creation d'une table sales avec 4 partitions et deux critéres de
partitionnement.
un étant une date et l'autre un code.

Il s'avere que l'écriture de la clause CHECK est importante ....

CREATE TABLE sales_part1
(CHECK (org = 6 and temps between to_date('20101001'::text,
'yyyymmdd'::text) and to_date('20131001'::text, 'yyyymmdd'::text)) )
INHERITS (sales);

transformé en :
test=# \d+ sales_part1
Table "public.sales_part1"
Column | Type | Modifiers | Storage | Description
--------+-----------------------------+-----------+----------+-------------
org | integer | | plain |
temps | timestamp without time zone | | plain |
name | character varying(10) | | extended |
Check constraints:
"sales_part1_check" CHECK (org = 6 AND temps >=
to_date('20101001'::text, 'yyyymmdd'::text) AND temps <=
to_date('20131001'::text, 'yyyymmdd'::text))
Inherits: sales
Has OIDs: no

​fait que la requete suivante parcourt toutes les partitions.

​explain analyze select * from sales where temps >= date '20131101' and
temps <= date '20141001';
QUERY
PLAN
-------------------------------------------------------------------------------------------------------------------------
Result (cost=0.00..103.73 rows=21 width=50) (actual time=0.082..0.296
rows=5 loops=1)
-> Append (cost=0.00..103.73 rows=21 width=50) (actual
time=0.067..0.216 rows=5 loops=1)
-> Seq Scan on sales (cost=0.00..2.53 rows=1 width=50) (actual
time=0.019..0.019 rows=0 loops=1)
Filter: ((temps >= '2013-11-01'::date) AND (temps <=
'2014-10-01'::date))
-> Seq Scan on sales_part1 sales (cost=0.00..25.30 rows=5
width=50) (actual time=0.011..0.011 rows=0 loops=1)
Filter: ((temps >= '2013-11-01'::date) AND (temps <=
'2014-10-01'::date))
-> Seq Scan on sales_part2 sales (cost=0.00..25.30 rows=5
width=50) (actual time=0.009..0.023 rows=2 loops=1)
Filter: ((temps >= '2013-11-01'::date) AND (temps <=
'2014-10-01'::date))
-> Seq Scan on sales_part3 sales (cost=0.00..25.30 rows=5
width=50) (actual time=0.024..0.024 rows=0 loops=1)
Filter: ((temps >= '2013-11-01'::date) AND (temps <=
'2014-10-01'::date))
-> Seq Scan on sales_part4 sales (cost=0.00..25.30 rows=5
width=50) (actual time=0.013..0.034 rows=3 loops=1)
Filter: ((temps >= '2013-11-01'::date) AND (temps <=
'2014-10-01'::date))
Total runtime: 0.410 ms​

​alors que si la contrainte est écrite comme ci-dessous :
​CREATE TABLE ventes_part1
(CHECK ( (temps >= date '20101001' and temps <= date '20131001')
and org=6 ) )
INHERITS (ventes);​

​transformé en :
d+
ventes_part1

Table "public.ventes_part1"
Column | Type | Modifiers | Storage | Description
--------+-----------------------------+-----------+----------+-------------
org | integer | | plain |
temps | timestamp without time zone | | plain |
name | character varying(10) | | extended |
Check constraints:
"ventes_part1_check" CHECK (temps >= '2010-10-01'::date AND temps <=
'2013-10-01'::date AND org = 6)
Inherits: ventes
Has OIDs: no​

​la meme requete ne parcourera que deux partitions :-)​

​test=# explain analyse select * from ventes where temps >= date
'20131101' and temps <= date '20141001';
QUERY
PLAN
---------------------------------------------------------------------------------------------------------------------------
Result (cost=0.00..53.13 rows=11 width=50) (actual time=0.056..0.169
rows=5 loops=1)
-> Append (cost=0.00..53.13 rows=11 width=50) (actual
time=0.046..0.120 rows=5 loops=1)
-> Seq Scan on ventes (cost=0.00..2.53 rows=1 width=50) (actual
time=0.023..0.023 rows=0 loops=1)
Filter: ((temps >= '2013-11-01'::date) AND (temps <=
'2014-10-01'::date))
-> Seq Scan on ventes_part2 ventes (cost=0.00..25.30 rows=5
width=50) (actual time=0.011..0.021 rows=2 loops=1)
Filter: ((temps >= '2013-11-01'::date) AND (temps <=
'2014-10-01'::date))
-> Seq Scan on ventes_part4 ventes (cost=0.00..25.30 rows=5
width=50) (actual time=0.009..0.022 rows=3 loops=1)
Filter: ((temps >= '2013-11-01'::date) AND (temps <=
'2014-10-01'::date))
Total runtime: 0.235 ms
(9 rows)
​
si on ajoute un critere de filtre supplémentaire à la clause where .
on retrouve le meme comportement sur le parcours des partitions.

Parcours d'une partition
test=# explain analyse select * from ventes where temps >= date '20131101'
and temps <= date '20141001' and org=7;
QUERY
PLAN
---------------------------------------------------------------------------------------------------------------------------
Result (cost=0.00..30.64 rows=2 width=50) (actual time=0.028..0.069
rows=3 loops=1)
-> Append (cost=0.00..30.64 rows=2 width=50) (actual time=0.022..0.048
rows=3 loops=1)
-> Seq Scan on ventes (cost=0.00..2.79 rows=1 width=50) (actual
time=0.009..0.009 rows=0 loops=1)
Filter: ((temps >= '2013-11-01'::date) AND (temps <=
'2014-10-01'::date) AND (org = 7))
-> Seq Scan on ventes_part4 ventes (cost=0.00..27.85 rows=1
width=50) (actual time=0.005..0.014 rows=3 loops=1)
Filter: ((temps >= '2013-11-01'::date) AND (temps <=
'2014-10-01'::date) AND (org = 7))
Total runtime: 0.108 ms
(7 rows)

Parcours de deux partitions.
test=# explain analyse select * from sales where temps >= date '20131101'
and temps <= date '20141001' and org=7;
QUERY
PLAN
-------------------------------------------------------------------------------------------------------------------------
Result (cost=0.00..58.48 rows=3 width=50) (actual time=0.049..0.103
rows=3 loops=1)
-> Append (cost=0.00..58.48 rows=3 width=50) (actual time=0.041..0.074
rows=3 loops=1)
-> Seq Scan on sales (cost=0.00..2.79 rows=1 width=50) (actual
time=0.011..0.011 rows=0 loops=1)
Filter: ((temps >= '2013-11-01'::date) AND (temps <=
'2014-10-01'::date) AND (org = 7))
-> Seq Scan on sales_part3 sales (cost=0.00..27.85 rows=1
width=50) (actual time=0.007..0.007 rows=0 loops=1)
Filter: ((temps >= '2013-11-01'::date) AND (temps <=
'2014-10-01'::date) AND (org = 7))
-> Seq Scan on sales_part4 sales (cost=0.00..27.85 rows=1
width=50) (actual time=0.007..0.020 rows=3 loops=1)
Filter: ((temps >= '2013-11-01'::date) AND (temps <=
'2014-10-01'::date) AND (org = 7))
Total runtime: 0.177 ms

En conclusion ....
Cela marche mais il est impératif d'éxaminer soigneusement la requete la
clause CHECK des tables.

je mesure la sensibilité :-(

Merci .

*Question subsidiaire* : il me semble avoir lu dans la documentation que
l'on ne devrait pas aller au delà de 100 partitions.

Est-ce encore d'actualité ?

​Merci ​

Pierre.

G+ <https://plus.google.com/u/0/107377830070954284209/about>

Le 5 octobre 2013 07:24, Pierre BOIZOT <pierre(dot)boizot(at)gmail(dot)com> a écrit :

> hello,
>
> oui oui le code ne provient pas des articles.
>
> c'est un extrait de code du cas qui m'occupe.
>
> Les clause de contrainte de la partition.
>
> Donc le partitionnement est fait sur un code et un interval de date.
>
> La table a 380 partition et en aura à terme 750 environs.
>
> Les requètes sont un peu lentes :-( + de 30 secondes.
>
> je recherche la cause ....
>
> la parametre contraint_exclusion est bien à on .
> malgré cela le plan cherche à parcourir toutes les partition de la table.
>
> d'ou ma question sur la relation entre les contraintes de la tables et
> les restrictions de la clause where.
>
> Y a t il une régle enoncée dans la documentation...
>
> s'il y a une différence entre contrainte et clause where cela
> revient il à intéroger la table sans les contraintes ?
>
> Je vais me faire un petit test pour explorer cela.
>
> ma question avait pour but de comprendre s'il y a des régles précises
> le point 5.9.4 et 5.9.6. de la documentation ne dit pas comment se
> comporte l'exclusion de contrainte dans le cas ou la clause where ne
> comporte pas toutes les colonnes déclarées dans la contrainte de la
> table.
>
> A+
> Pierre
>
>
>
>
>
> Pierre.
>
> G+
>
>
>
> Le 4 octobre 2013 23:39, Guillaume Lelarge <guillaume(at)lelarge(dot)info> a
> écrit :
> > On Fri, 2013-10-04 at 19:24 +0200, Pierre BOIZOT wrote:
> >> Effectivement google donne pas mal de resource sur le sujet.
> >>
> >> Deux on retenu mon attention.
> >>
> >>
> http://www.enterprisedb.com/resources-community/tutorials-quickstarts/all-platforms/how-partition-table-postgres-plus
> >>
> >>
> http://www.fuzzy.cz/en/articles/automatic-management-of-partitions-in-postgresql/
> >> 
> >>
> >>
> >> Je constate que les tables de partition ont des contraintes de ce style.
> >>
> >> CONSTRAINT analow_t20130924_pkey PRIMARY KEY (tor, trtime),
> >> CONSTRAINT analow_t20130924_check
> >> CHECK (lifetime::text = 'W'::text
> >> AND trtime >= to_date('20130924'::text, 'yyyymmdd'::text)
> >> AND trtime < to_date('20130925'::text, 'yyyymmdd'::text)
> >> )
> >>
> >> Mais que les requetes ne font références dans leur clause where.
> >> Qu'aux colonnes trtime et tor ...
> >>
> >> est-ce que les requetes doivent avoir les deux clauses lifetime
> intervalle
> >> de date ?
> >>
> >> est-ce une piste '
> >>
> >> Ce que je testerai ce week-end si c'est le cas.
> >>
> >
> > Je devrais peut-être allé me coucher. On est d'accord que le code des
> > contraintes ci-dessus ne fait pas partie des articles dont tu as donné
> > le lien ?
> >
> > Du coup, pourrais-tu donner un peu de contexte ?
> >
> >
> > --
> > Guillaume
> > http://blog.guillaume.lelarge.info
> > http://www.dalibo.com
> >
>