postgres=# \timing
Chronométrage activé.
postgres=# create table population(id serial, nom_marital text, masse smallint);
CREATE TABLE
Temps : 618,151 ms
postgres=# do
postgres-# $$
postgres$# begin
postgres$# for i in 1..20000000 loop
postgres$# insert into population(nom_marital, masse) values (CASE WHEN random() < 0.1 THEN 'LEFEVRE' ELSE upper(md5(random()::text)) END, trunc(random() * 50 + 50));
postgres$# end loop;
postgres$# end$$;
DO
Temps : 725204,726 ms
postgres=# select count(*) from population where nom_marital='LEFEVRE';
count
---------
2000693
(1 ligne)
Temps : 5848,715 ms
postgres=# \d+
Liste des relations
Schéma | Nom | Type | Propriétaire | Taille | Description
--------+-------------------+----------+--------------+------------+-------------
public | population | table | postgres | 1260 MB |
public | population_id_seq | séquence | postgres | 8192 bytes |
(2 lignes)
postgres=# explain select avg(masse) from population where nom_marital='LEFEVRE';
QUERY PLAN
---------------------------------------------------------------------------
Aggregate (cost=416184.81..416184.82 rows=1 width=2)
-> Seq Scan on population (cost=0.00..411179.85 rows=2001983 width=2)
Filter: (nom_marital = 'LEFEVRE'::text)
(3 lignes)
Temps : 2,668 ms
postgres=# explain select avg(masse) from population where nom_marital='24D8C7EA0335467C90143834F2826884';
QUERY PLAN
--------------------------------------------------------------------------
Aggregate (cost=411180.02..411180.03 rows=1 width=2)
-> Seq Scan on population (cost=0.00..411179.85 rows=68 width=2)
Filter: (nom_marital = '24D8C7EA0335467C90143834F2826884'::text)
(3 lignes)
Temps : 1,950 ms
postgres=# select avg(masse) from population where nom_marital='LEFEVRE';
avg
---------------------
74.4934520188754597
(1 ligne)
Temps : 6451,122 ms
postgres=# select avg(masse) from population where nom_marital='LEFEVRE';
avg
---------------------
74.4934520188754597
(1 ligne)
Temps : 5186,698 ms
postgres=# select avg(masse) from population where nom_marital='LEFEVRE';
avg
---------------------
74.4934520188754597
(1 ligne)
Temps : 5166,795 ms
postgres=# select avg(masse) from population where nom_marital='24D8C7EA0335467C90143834F2826884';
avg
-----
(1 ligne)
Temps : 4774,511 ms
postgres=# select avg(masse) from population where nom_marital='24D8C7EA0335467C90143834F2826884';
avg
-----
(1 ligne)
Temps : 5018,838 ms
postgres=# select avg(masse) from population where nom_marital='24D8C7EA0335467C90143834F2826884';
avg
-----
(1 ligne)
Temps : 4937,004 ms
postgres=# create index br_nmr on population using brin(nom_marital);
CREATE INDEX
Temps : 23754,541 ms
postgres=# \di+
Liste des relations
Schéma | Nom | Type | Propriétaire | Table | Taille | Description
--------+--------+-------+--------------+------------+--------+-------------
public | br_nmr | index | postgres | population | 120 kB |
(1 ligne)
postgres=# explain select avg(masse) from population where nom_marital='LEFEVRE';
QUERY PLAN
---------------------------------------------------------------------------------------
Aggregate (cost=206775.50..206775.51 rows=1 width=2)
-> Bitmap Heap Scan on population (cost=15563.50..201770.50 rows=2002000 width=2)
Recheck Cond: (nom_marital = 'LEFEVRE'::text)
-> Bitmap Index Scan on br_nmr (cost=0.00..15063.00 rows=2002000 width=0)
Index Cond: (nom_marital = 'LEFEVRE'::text)
(5 lignes)
Temps : 2,637 ms
postgres=# explain select avg(masse) from population where nom_marital='24D8C7EA0335467C90143834F2826884';
QUERY PLAN
------------------------------------------------------------------------------------
Aggregate (cost=317.36..317.37 rows=1 width=2)
-> Bitmap Heap Scan on population (cost=48.52..317.18 rows=68 width=2)
Recheck Cond: (nom_marital = '24D8C7EA0335467C90143834F2826884'::text)
-> Bitmap Index Scan on br_nmr (cost=0.00..48.51 rows=68 width=0)
Index Cond: (nom_marital = '24D8C7EA0335467C90143834F2826884'::text)
(5 lignes)
Temps : 2,049 ms
postgres=# select avg(masse) from population where nom_marital='LEFEVRE';
avg
---------------------
74.4934520188754597
(1 ligne)
Temps : 6012,466 ms
postgres=# select avg(masse) from population where nom_marital='LEFEVRE';
avg
---------------------
74.4934520188754597
(1 ligne)
Temps : 5818,870 ms
postgres=# select avg(masse) from population where nom_marital='LEFEVRE';
avg
---------------------
74.4934520188754597
(1 ligne)
Temps : 6259,581 ms
postgres=# select avg(masse) from population where nom_marital='24D8C7EA0335467C90143834F2826884';
avg
-----
(1 ligne)
Temps : 6310,417 ms
postgres=# select avg(masse) from population where nom_marital='24D8C7EA0335467C90143834F2826884';
avg
-----
(1 ligne)
Temps : 5543,842 ms
postgres=# select avg(masse) from population where nom_marital='24D8C7EA0335467C90143834F2826884';
avg
-----
(1 ligne)
Temps : 5384,701 ms
postgres=# drop index br_nmr;
DROP INDEX
Temps : 5,533 ms
postgres=# create index bt_nmr on population(nom_marital);
CREATE INDEX
Temps : 448376,387 ms
postgres=# \di+
Liste des relations
Schéma | Nom | Type | Propriétaire | Table | Taille | Description
--------+--------+-------+--------------+------------+---------+-------------
public | bt_nmr | index | postgres | population | 1057 MB |
(1 ligne)
postgres=# explain select avg(masse) from population where nom_marital='LEFEVRE';
QUERY PLAN
---------------------------------------------------------------------------------------
Aggregate (cost=260880.07..260880.08 rows=1 width=2)
-> Bitmap Heap Scan on population (cost=69668.06..255875.06 rows=2002000 width=2)
Recheck Cond: (nom_marital = 'LEFEVRE'::text)
-> Bitmap Index Scan on bt_nmr (cost=0.00..69167.56 rows=2002000 width=0)
Index Cond: (nom_marital = 'LEFEVRE'::text)
(5 lignes)
Temps : 9,444 ms
postgres=# explain select avg(masse) from population where nom_marital='24D8C7EA0335467C90143834F2826884';
QUERY PLAN
------------------------------------------------------------------------------------
Aggregate (cost=273.92..273.93 rows=1 width=2)
-> Bitmap Heap Scan on population (cost=5.09..273.75 rows=68 width=2)
Recheck Cond: (nom_marital = '24D8C7EA0335467C90143834F2826884'::text)
-> Bitmap Index Scan on bt_nmr (cost=0.00..5.07 rows=68 width=0)
Index Cond: (nom_marital = '24D8C7EA0335467C90143834F2826884'::text)
(5 lignes)
Temps : 2,286 ms
postgres=# select avg(masse) from population where nom_marital='LEFEVRE';
avg
---------------------
74.4934520188754597
(1 ligne)
Temps : 5631,951 ms
postgres=# select avg(masse) from population where nom_marital='LEFEVRE';
avg
---------------------
74.4934520188754597
(1 ligne)
Temps : 6179,884 ms
postgres=# select avg(masse) from population where nom_marital='LEFEVRE';
avg
---------------------
74.4934520188754597
(1 ligne)
Temps : 6021,277 ms
postgres=# select avg(masse) from population where nom_marital='24D8C7EA0335467C90143834F2826884';
avg
-----
(1 ligne)
Temps : 2,192 ms
postgres=# select avg(masse) from population where nom_marital='24D8C7EA0335467C90143834F2826884';
avg
-----
(1 ligne)
Temps : 1,902 ms
postgres=# select avg(masse) from population where nom_marital='24D8C7EA0335467C90143834F2826884';
avg
-----
(1 ligne)
Temps : 2,006 ms