Forums PostgreSQL.fr

Ours77

Membre

SELECT *, ORDER BY et CLUSTER

Bonjour,

J'ai un table A qui contient 2 colonne d'entier C1 et C2.
J'ai un index id_C1 qui est un index sur la première colonne.
Puis j'ai fait un CLUSTER de A avec cet index.

Je veux parcourir toute ma table (près de 700 millions d’éléments...) en utilisant l'index c1.

Si je fait :

Select * from A order by C1.

Le plan de requête n'utilise pas l'index  et le temps de réponse est très très long  (trop pour mon application....).

QUERY PLAN                                         
--------------------------------------------------------------------------------------------
Sort  (cost=163542949.23..165224909.23 rows=672784000 width=8)
   Sort Key:C1
   ->  Seq Scan onA  (cost=0.00..9704761.00 rows=672784000 width=8)
(3 lignes)

Si je fait un "Select * from A" est ce que je suis assuré de récupérer les données selon C1 vu que j'ai fait un CLUSTER sur ma table selon cette colonne ?

Existe t il une requete du genre
"Select * from A using index  id_C1

Merci d'avance pour votre aide

Marc Cousin

Membre

Re : SELECT *, ORDER BY et CLUSTER

Pour du debug (uniquement) vous pouvez désactiver le scan séquentiel et regarder le coût et le temps d'exécution de la requête:

tapez «set enable_sequscan to off;» dans la session et reessayez l'explain. Et comparez les temps d'exécution avec et sans le set.

Par ailleurs, non, CLUSTER ne réalise qu'une seule fois le tri sur la table. Au fil des mises à jour, elle va se retrouver à nouveau dans le désordre. En plus si vous avez 2 sessions faisant chacune un scan séquentiel, la seconde commencera à l'endroit où se trouve la première afin de minimiser les entrées-sorties, et vous retournera donc les données dans le désordre. Il faut utiliser ORDER BY.

Donc déjà, postez les EXPLAIN (voire explain analyze, si c'est possible) de la requête avec enable_seqscan à on et à off. Et précisez la version de PostgreSQL, ainsi que les valeurs de seq_page_cost et random_page_cost.

---

Marc.

Ours77

Membre

Re : SELECT *, ORDER BY et CLUSTER

merci pour votre réponse rapide
j'ai un posgres version 8.3
seq_page_cost = 1.0 (valeur par defaut)
random_page_cost = 4.0    (valeur par defaut)

Concernant la table, je ne fais pas d'update ni d'insert dessus. elle est en "Lecture Seule"

Sinon voici les valeurs :

set enable_seqscan to off;
explain select * from A ORDER BY C1;
QUERY PLAN                                             
------------------------------------------------------------------------------------------------------
Sort  (cost=263542949.23..265224909.23 rows=672784000 width=8)
   Sort Key: C1
   ->  Seq Scan on A  (cost=100000000.00..109704761.00 rows=672784000 width=8)

set enable_seqscan to on;
explain select * from A ORDER BY C1;
                                         QUERY PLAN                                         
--------------------------------------------------------------------------------------------
Sort  (cost=163542949.23..165224909.23 rows=672784000 width=8)
   Sort Key: C1
   ->  Seq Scan on A  (cost=0.00..9704761.00 rows=672784000 width=8)
(3 lignes)

Marc Cousin

Membre

Re : SELECT *, ORDER BY et CLUSTER

Bon, on a un truc louche: malgré le enable_seqscan to off, il fait un seqscan. Ça veut dire qu'il pense vraiment que le scan d'index n'est pas possible (ou alors une vraiment très mauvaise idée).

Est-ce que vous pouvez donner la définition complète de la table et de l'index ?

Et le résultat de SELECT * from pg_stats where attname = 'c1' and tablename = 'a';

---

Marc.

Ours77

Membre

Re : SELECT *, ORDER BY et CLUSTER

La structure complete de la table et de l'index est:

CREATE TABLE A (   C1 integer,   C2 integer ) ;
CREATE INDEX id_C1  ON A   USING btree    (C1);

Voici le résultat de la requete
SELECT * from pg_stats where attname = 'C1' and tablename = 'A';

schemaname |        tablename         | attname | null_frac | avg_width | n_distinct  |   most_common_vals    |     most_common_freqs     |                                              histogram_bounds                                              | correlation
------------+--------------------------+---------+-----------+-----------+-------------+-----------------------+---------------------------+------------------------------------------------------------------------------------------------------------+-------------
public     | A | C1 |         0 |         4 | 2.24102e+06 | {260776233,321886708} | {0.000666667,0.000666667} | {317430,29140989,72490852,111639837,162726571,217745980,264734876,305660178,336754552,385767595,458987002} |   0.0102128
(1 ligne)

J'ai refait un explain analyze :
set enable_seqscan to on;
explain analyze select * from t_mes_hom_ss_doublon_res ORDER BY mes_id1;
                                                                     QUERY PLAN                                                                     
----------------------------------------------------------------------------------------------------------------------------------------------------
Sort  (cost=163542949.23..165224909.23 rows=672784000 width=8) (actual time=843426.850..1023723.740 rows=672784026 loops=1)
   Sort Key: C1
   Sort Method:  external merge  Disk: 15783792kB
   ->  Seq Scan on A  (cost=0.00..9704761.00 rows=672784000 width=8) (actual time=13.487..146788.687 rows=672784026 loops=1)
Total runtime: 1084920.213 ms
(5 lignes)

set enable_seqscan to off;
explain analyze select * from t_mes_hom_ss_doublon_res ORDER BY mes_id1;
                                                                          QUERY PLAN                                                                         
--------------------------------------------------------------------------------------------------------------------------------------------------------------
Sort  (cost=263542949.23..265224909.23 rows=672784000 width=8) (actual time=840698.987..1017110.236 rows=672784026 loops=1)
   Sort Key: C1
   Sort Method:  external merge  Disk: 15783792kB
   ->  Seq Scan on A  (cost=100000000.00..109704761.00 rows=672784000 width=8) (actual time=15.570..143822.771 rows=672784026 loops=1)
Total runtime: 1077951.970 ms

Marc Cousin

Membre

Re : SELECT *, ORDER BY et CLUSTER

La correlation à 0.01 sur une colonne sur laquelle un CLUSTER a eu lieu n'est pas bonne.

Avez-vous repassé les stats après la commande cluster ? Sinon, essayez déjà un ANALYZE a; et réessayez (et redonnez le résultat du select sur pg_stats)

---

Marc.

Ours77

Membre

Re : SELECT *, ORDER BY et CLUSTER

Effectivement je n'avais pas fait d'ANALYZE après le CLUSTER.
Je pensais qu'il n'y en avait pas besoin, Désolé.
Maintenant cela semble beaucoup mieux...

Voici le résultat :

schemaname |        tablename         | attname | null_frac | avg_width | n_distinct |                                       most_common_vals                                       |                                         most_common_freqs                                         |                                             histogram_bounds                                              | correlation
------------+--------------------------+---------+-----------+-----------+------------+----------------------------------------------------------------------------------------------+---------------------------------------------------------------------------------------------------+-----------------------------------------------------------------------------------------------------------+-------------
public     |  A  | C1 |         0 |         4 |      98419 | {18331342,122919545,166293478,336171882,8235717,9989273,12796743,14820287,46257437,54247373} | {0.001,0.001,0.001,0.001,0.000666667,0.000666667,0.000666667,0.000666667,0.000666667,0.000666667} | {52191,29147811,70914521,110918314,152345409,198650891,249337412,300888640,335856464,387211576,459899680} |           1
(1 ligne)

explain analyze select * from A ORDER BY C1;
                                                                          QUERY PLAN                                                                           
---------------------------------------------------------------------------------------------------------------------------------------------------------------
Index Scan using C1 on A (cost=0.00..20447583.96 rows=672784128 width=8) (actual time=82.764..251109.888 rows=672784026 loops=1)
Total runtime: 312674.035 ms
(2 lignes)

merci pour votre aide,

Je ne connaissais pas l'usage de pg_stats de cette manière

Merci encore

Marc Cousin

Membre

Re : SELECT *, ORDER BY et CLUSTER

En fait c'est pg_stats (enfin pg_statistic, qui est moins lisible) qu'utilise l'optimiseur pour prendre ses décisions… s'il prend une mauvaise décision, c'est souvent le plus simple d'aller voir dans pg_stats pour comprendre ce qui l'induit en erreur.

---

Marc.