Vous n'êtes pas identifié(e).
- Contributions : Récentes | Sans réponse
Pages : 1
#1 17/01/2011 17:46:49
- arthurr
- Membre
Table heritees
Bonjour,
j'ai une base de données d'environ 50 go sous PG 9.0.2 (meme symptômes sur 8.2.4 en production).
j'ai une table "documents" et 9 tables héritées de cette table.
Je fais une requête avec une sous requête (ou une jointure suivant le cas).
Et j'ai trouvé un comportement très étrange suivant que j’utilise IN ou = ...
pool=# explain (analyse true) select id_partition,count(*) from documents where id_source = (select id from sources where name='S201' limit 1) group by id_partition ;
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------
HashAggregate (cost=2506276.37..2506278.87 rows=200 width=2) (actual time=4635.768..4635.770 rows=2 loops=1)
InitPlan 1 (returns $0)
-> Limit (cost=0.00..3.27 rows=1 width=4) (actual time=0.038..0.038 rows=1 loops=1)
-> Index Scan using idx_sources_name on sources (cost=0.00..3.27 rows=1 width=4) (actual time=0.037..0.037 rows=1 loops=1)
Index Cond: (name = 'S201'::text)
-> Append (cost=0.00..2347056.49 rows=31843323 width=2) (actual time=4635.532..4635.683 rows=194 loops=1)
-> Seq Scan on documents (cost=0.00..18.25 rows=3 width=2) (actual time=0.003..0.003 rows=0 loops=1)
Filter: (id_source = $0)
-> Seq Scan on documents_a documents (cost=0.00..229632.19 rows=10067455 width=2) (actual time=2301.493..2301.493 rows=0 loops=1)
Filter: (id_source = $0)
-> Seq Scan on documents_b documents (cost=0.00..262056.68 rows=11489014 width=2) (actual time=2333.959..2333.959 rows=0 loops=1)
Filter: (id_source = $0)
-> Index Scan using idx_documents_c_id_source_nod on documents_c documents (cost=0.00..203716.13 rows=3658109 width=2) (actual time=0.023..0.023 rows=0 loops=1)
Index Cond: (id_source = $0)
-> Bitmap Heap Scan on documents_d documents (cost=36379.09..310417.26 rows=3066414 width=2) (actual time=0.011..0.011 rows=0 loops=1)
Recheck Cond: (id_source = $0)
-> Bitmap Index Scan on idx_documents_d_id_source_nod (cost=0.00..35612.49 rows=3066414 width=0) (actual time=0.009..0.009 rows=0 loops=1)
Index Cond: (id_source = $0)
-> Bitmap Heap Scan on documents_e documents (cost=26425.31..787901.69 rows=2227231 width=2) (actual time=0.009..0.009 rows=0 loops=1)
Recheck Cond: (id_source = $0)
-> Bitmap Index Scan on idx_documents_e_id_source_nod (cost=0.00..25868.50 rows=2227231 width=0) (actual time=0.008..0.008 rows=0 loops=1)
Index Cond: (id_source = $0)
-> Bitmap Heap Scan on documents_f documents (cost=15275.76..494875.88 rows=1287450 width=2) (actual time=0.008..0.008 rows=0 loops=1)
Recheck Cond: (id_source = $0)
-> Bitmap Index Scan on idx_documents_f_id_source_nod (cost=0.00..14953.90 rows=1287450 width=0) (actual time=0.007..0.007 rows=0 loops=1)
Index Cond: (id_source = $0)
-> Index Scan using idx_documents_z_id_source_nod on documents_z documents (cost=0.00..58438.40 rows=47647 width=2) (actual time=0.022..0.138 rows=194 loops=1)
Index Cond: (id_source = $0)
Total runtime: 4635.979 ms
(29 rows)
Time: 4641,288 ms
pool=# explain (analyse true) select id_partition,count(*) from documents where id_source IN (select id from sources where name='S201' limit 1) group by id_partition ;
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
HashAggregate (cost=2933423.78..2933426.28 rows=200 width=2) (actual time=0.922..0.925 rows=2 loops=1)
-> Nested Loop (cost=3.28..2929075.49 rows=869658 width=2) (actual time=0.202..0.756 rows=194 loops=1)
Join Filter: (public.documents.id_source = sources.id)
-> HashAggregate (cost=3.28..3.29 rows=1 width=4) (actual time=0.051..0.052 rows=1 loops=1)
-> Limit (cost=0.00..3.27 rows=1 width=4) (actual time=0.042..0.042 rows=1 loops=1)
-> Index Scan using idx_sources_name on sources (cost=0.00..3.27 rows=1 width=4) (actual time=0.040..0.040 rows=1 loops=1)
Index Cond: (name = 'S201'::text)
-> Append (cost=0.00..2531022.45 rows=31843980 width=4) (actual time=0.133..0.525 rows=194 loops=1)
-> Seq Scan on documents (cost=0.00..16.60 rows=660 width=4) (actual time=0.001..0.001 rows=0 loops=1)
-> Bitmap Heap Scan on documents_a documents (cost=119432.44..323895.99 rows=10067455 width=4) (actual time=0.020..0.020 rows=0 loops=1)
Recheck Cond: (public.documents.id_source = sources.id)
-> Bitmap Index Scan on idx_documents_a_id_source_nod (cost=0.00..116915.58 rows=10067455 width=0) (actual time=0.017..0.017 rows=0 loops=1)
Index Cond: (public.documents.id_source = sources.id)
-> Bitmap Heap Scan on documents_b documents (cost=136296.58..369630.72 rows=11489014 width=4) (actual time=0.014..0.014 rows=0 loops=1)
Recheck Cond: (public.documents.id_source = sources.id)
-> Bitmap Index Scan on idx_documents_b_id_source_nod (cost=0.00..133424.33 rows=11489014 width=0) (actual time=0.012..0.012 rows=0 loops=1)
Index Cond: (public.documents.id_source = sources.id)
-> Bitmap Heap Scan on documents_c documents (cost=43397.55..202298.64 rows=3658109 width=4) (actual time=0.015..0.015 rows=0 loops=1)
Recheck Cond: (public.documents.id_source = sources.id)
-> Bitmap Index Scan on idx_documents_c_id_source_nod (cost=0.00..42483.02 rows=3658109 width=0) (actual time=0.014..0.014 rows=0 loops=1)
Index Cond: (public.documents.id_source = sources.id)
-> Bitmap Heap Scan on documents_d documents (cost=36379.09..302751.23 rows=3066414 width=4) (actual time=0.013..0.013 rows=0 loops=1)
Recheck Cond: (public.documents.id_source = sources.id)
-> Bitmap Index Scan on idx_documents_d_id_source_nod (cost=0.00..35612.49 rows=3066414 width=0) (actual time=0.012..0.012 rows=0 loops=1)
Index Cond: (public.documents.id_source = sources.id)
-> Bitmap Heap Scan on documents_e documents (cost=26425.31..782333.62 rows=2227231 width=4) (actual time=0.016..0.016 rows=0 loops=1)
Recheck Cond: (public.documents.id_source = sources.id)
-> Bitmap Index Scan on idx_documents_e_id_source_nod (cost=0.00..25868.50 rows=2227231 width=0) (actual time=0.016..0.016 rows=0 loops=1)
Index Cond: (public.documents.id_source = sources.id)
-> Bitmap Heap Scan on documents_f documents (cost=15275.76..491657.26 rows=1287450 width=4) (actual time=0.015..0.015 rows=0 loops=1)
Recheck Cond: (public.documents.id_source = sources.id)
-> Bitmap Index Scan on idx_documents_f_id_source_nod (cost=0.00..14953.90 rows=1287450 width=0) (actual time=0.015..0.015 rows=0 loops=1)
Index Cond: (public.documents.id_source = sources.id)
-> Index Scan using idx_documents_z_id_source_nod on documents_z documents (cost=0.00..58438.40 rows=47647 width=4) (actual time=0.034..0.335 rows=194 loops=1)
Index Cond: (public.documents.id_source = sources.id)
Total runtime: 1.160 ms
(36 rows)
Time: 6,888 ms
Le 2eme exemple se comporte exactement comme une jointure (select d.id_partition,count(*) from documents d join sources s on (d.id_source =s.id) where s.name='S201' group by d.id_partition ;)
On passe de 7 ms a 4.5 secondes ...
Vous avez une explications, une piste ?
Merci
Hors ligne
#2 17/01/2011 18:06:17
- Marc Cousin
- Membre
Re : Table heritees
En fait, vous avez de la chance dans l'écriture de la seconde requête. Et pas dans la première. Mais dans tous les cas, vous auriez mieux fait de l'écrire de façon relationnelle (comme vous l'avez montré sur la fin), avant d'essayer de l'écrire avec des sous-requêtes.
Le problème est qu'au moment de la planification il n'a aucune idée de l'enregistrement que
"select id from sources where name='S201' limit 1" va lui ramener, donc du nombre d'enregistrements qu'il va ramener de la table documents.
id_source semble bien étrange: l'id source que vous utilisez semble très rare. Alors qu'en moyenne (en tout cas pour certaines partitions) il y a l'air d'y avoir très peu de valeurs différentes.
Dans la vue système pg_stats, que vaut n_distinct pour la attname=id_source, pour les partitions de la table ?
Marc.
Hors ligne
#3 17/01/2011 18:18:38
- arthurr
- Membre
Re : Table heritees
Merci pour votre réponse !
pool=# select tablename , n_distinct from pg_stats where attname='id_source';
tablename | n_distinct
-------------+------------
documents_a | 1
documents_b | 1
documents_c | 3
documents_d | 5
documents_e | 22
documents_f | 24
documents_z | 969
(7 rows)
les id_source sont ventilés dans les tables héritées, ils servent de contraintes de Check :
pool=# \d documents_a
Table "public.documents_a"
Column | Type | Modifiers
---------------+-----------------------------+--------------------------------------------------------
id | integer | not null default nextval('documents_id_seq'::regclass)
id_source | smallint |
...
...
Indexes:
...
...
Check constraints:
"documents_a_id_source_check" CHECK (id_source = 103::smallint)
Inherits: documents
Dernière modification par arthurr (17/01/2011 18:22:14)
Hors ligne
#4 17/01/2011 18:27:00
- Marc Cousin
- Membre
Re : Table heritees
Ok. Donc déjà, si vous partitionnez par id_source, votre requête, telle qu'elle est écrite, ne profite pas du partitionnement: id_source doit être une constante pour pouvoir être substitué. Il ne vous reste plus qu'à le faire en deux requêtes (une pour récupérer l'id_source, l'autre pour la requête réelle). C'est une des limitations du partitionnement.
Cf: http://docs.postgresqlfr.org/9.0/ddl-partitioning.html : «l'exclusion de contrainte ne fonctionne que si la clause WHERE de la requête contient des constantes. Une requête avec paramètre n'est pas optimisée car le planificateur ne peut avoir connaissance au préalable des partitions sélectionnées par la valeur du paramètre à l'exécution. Pour la même raison, il faut éviter les fonctions « stable »s comme CURRENT_DATE ;»
Marc.
Hors ligne
#5 17/01/2011 18:30:20
- arthurr
- Membre
Re : Table heritees
Merci pour votre rapidité.
Je vais voir pour faire modifier le code des requêtes qui posent problème.
Hors ligne
#6 17/01/2011 18:35:22
- Marc Cousin
- Membre
Re : Table heritees
Oui. Le partitionnement sous PostgreSQL est assez brut de décoffrage 
Marc.
Hors ligne
#7 18/01/2011 18:36:53
- SQLpro
- Membre
Re : Table heritees
id_source = (select id...
n'est sémantiquement pas la même chose que
id_source IN (select id...
En effet derrière un opérateur =, SQL s'attend à une seule valeur. Je m'étonne d'ailleurs que PostGreSQL laisse passer cette exécution et ne renvoi pas un message du genre "la sous requête a retourné plusieurs valeurs. Ceci est interdit lorsque l'opérateur attend une valeur scalaire" ! Peu importe que vous ayez fait un LIMIT 1 qui n'est qu'une clause cosmétique non relationnelle !
A +
Frédéric Brouard, alias SQLpro, ARCHITECTE DE DONNÉES, Expert langage SQL
Le site sur les SGBD relationnel et langage SQL : http://sqlpro.developpez.com/
Modélisation de données, conseil, expertise, audit, optimisation, tuning, formation
* * * * * Enseignant CNAM PACA, ISEN Toulon, CESI Aix en Provence * * * * *
Hors ligne
#8 18/01/2011 18:41:22
- Marc Cousin
- Membre
Re : Table heritees
Parce que le select ne ramène effectivement qu'une seule valeur. Et que donc on calcule l'égalité de deux enregistrements…
Il l'interprète de façon assez large, du point de vue relationnel, on est d'accord, on exécute l'égalité d'un attribut de relation avec une relation (pas d'une «valeur» à «plusieurs valeurs»).
De mémoire, Oracle tolère aussi cette approximation syntaxique. Simplement parce qu'elle est bien pratique.
Marc.
Hors ligne
Pages : 1