Skip site navigation (1) Skip section navigation (2)

Re: Performances fetchs 3.5M lignes

From: Marc Cousin <cousinmarc(at)gmail(dot)com>
To: pgsql-fr-generale(at)postgresql(dot)org
Cc: "Pierre Y(dot)" <pierre(dot)y(at)gmail(dot)com>
Subject: Re: Performances fetchs 3.5M lignes
Date: 2012-01-20 14:32:01
Message-ID: 2881460.NJ5BiD4Mbo@marco-dalibo (view raw or flat)
Thread:
Lists: pgsql-fr-generale
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…

In response to

pgsql-fr-generale by date

Next:From: Pierre Y.Date: 2012-01-20 17:27:58
Subject: Re: Performances fetchs 3.5M lignes
Previous:From: Cédric VillemainDate: 2012-01-20 14:21:12
Subject: Re: Performances fetchs 3.5M lignes

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group