Re: Performances fetchs 3.5M lignes

On Friday 20 January 2012 14:00:29 Pierre Y. wrote:

Bonjour,

C'est mon premier post ici. Alors j'espère que je respecte bien les règles
d'usage.

J'ai une base postgresql 9.0.6 (Backports Debian Squeeze) qui sert une
application Ruby on Rails.

J'ai 3.5M lignes dans une table et 570000 dans l'autre.

Voilà à quoi elles ressemblent :

-- Table: user_magazines (570 000 lignes)

CREATE TABLE user_magazines
(
id serial NOT NULL,
user_id integer,
libelle character varying(255),
/* ... */
CONSTRAINT user_magazines_pkey PRIMARY KEY (id )
);

CREATE INDEX index_user_magazines_on_user_id
ON user_magazines
USING btree
(user_id );

CREATE INDEX index_user_magazines_on_libelle
ON user_magazines
USING btree
(libelle );

et

-- Table: user_parutions (3.5M lignes)

CREATE TABLE user_parutions
(
id serial NOT NULL,
user_id integer,
user_magazine_id integer,
/* ... /
CONSTRAINT user_parutions_pkey PRIMARY KEY (id )
);

CREATE INDEX index_user_parutions_on_user_id
ON user_parutions
USING btree
(user_id );

CREATE INDEX index_user_parutions_on_user_magazine_id
ON user_parutions
USING btree
(user_magazine_id );

Ca c'est moi qui l'ai changé, j'ai cru comprendre que ça pouvait changer des
choses.

ALTER TABLE user_magazines ALTER COLUMN id SET STATISTICS 1000;
ALTER TABLE user_parutions ALTER COLUMN id SET STATISTICS 1000;

J'ai REINDEXé, VACUUMisé, ANALYZé.

Et j'ai ce plan pour cette très bête requête :

production=> explain analyze select um.*, up.* from user_parutions up join
user_magazines um on (um.id=up.user_magazine_id);

Hash Join (cost=41033.68..388209.57 rows=3523087 width=256) (actual
time=1033.790..57197.418 rows=3523087 loops=1)
Hash Cond: (up.user_magazine_id = um.id)
-> Seq Scan on user_parutions up (cost=0.00..118331.87 rows=3523087
width=158) (actual time=0.010..2334.002 rows=3523087 loops=1)
-> Hash (cost=24967.30..24967.30 rows=571230 width=98) (actual
time=1031.240..1031.240 rows=571230 loops=1)
Buckets: 1024 Batches: 128 Memory Usage: 609kB
-> Seq Scan on user_magazines um (cost=0.00..24967.30 rows=571230
width=98) (actual time=0.006..296.016 rows=571230 loops=1)
Total runtime: 58549.933 ms
(7 lignes)

Je n'arrive pas à savoir si :
* 58 secondes pour 3.5M lignes c'est correct (on trouve toujours que c'est
trop)
* Si le PLAN est correct car je suis étonné de ne pas le voir utiliser
d'index.

Le plan est correct. Là où les bacteries se déposent c'est le "batches" du
hash join (le hash en bas). Ça veut dire qu'il a été obligé de s'y reprendre à
128 fois sur la table user_magazines pour faire la jointure. Ça veut en fait
dire qu'il n'y a pas assez de work_mem pour la requête (je présume que c'est
la valeur par défaut, 1Mo ?).

Pour aller plus vite, il faudrait l'aggrandir, au moins pour cette requête.
Essayez déjà à 50Mo, vu le volume de données à traiter. (faites «set work_mem
to '50MB'; puis réexécutez le explain analyze…).

Attention quand même, si vous mettez 50Mo dans le postgresql.conf, chaque tri,
chaque hash de chaque session peut prendre 50Mo de mémoire. Dons si vous avez
50 sessions en train de faire chacune une requête avec un sort et 2 hash, ça
fait potientiellement 50 x 3 x 50 Mo, soit 4,5Go de ram. Donc à augmenter avec
prudence, et en surveillant l'état du serveur…