postgres=# \timing
Chronométrage activé.
postgres=# create table ventes_agg(id serial, dtv timestamp, mtv integer);
CREATE TABLE
Temps : 7,953 ms
postgres=# insert into ventes_agg(dtv,mtv)
postgres=# select generate_series(date_trunc('second', current_timestamp-interval '1 year'), date_trunc('second', current_timestamp), '1 second'),
postgres=# trunc(random() * 100000 + 1);
INSERT 0 31536001
Temps : 199404,485 ms
postgres=# \d+
Liste des relations
Schéma | Nom | Type | Propriétaire | Taille | Description
--------+-------------------+----------+--------------+------------+-------------
public | ventes_agg | table | postgres | 1570 MB |
public | ventes_agg_id_seq | séquence | postgres | 8192 bytes |
(2 lignes)
postgres=# create index br_dtv on ventes_agg using brin(dtv);
CREATE INDEX
Temps : 14974,848 ms
postgres=# explain select avg(mtv) from ventes_agg where dtv >= to_timestamp('13/03/2015','DD/MM/YYYY') and dtv < to_timestamp('27/03/2015','DD/MM/YYYY');
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=239991.64..239991.65 rows=1 width=4)
-> Bitmap Heap Scan on ventes_agg (cost=12269.85..237007.77 rows=1193546 width=4)
Recheck Cond: ((dtv >= to_timestamp('13/03/2015'::text, 'DD/MM/YYYY'::text)) AND (dtv < to_timestamp('27/03/2015'::text, 'DD/MM/YYYY'::text)))
-> Bitmap Index Scan on br_dtv (cost=0.00..11971.47 rows=1193546 width=0)
Index Cond: ((dtv >= to_timestamp('13/03/2015'::text, 'DD/MM/YYYY'::text)) AND (dtv < to_timestamp('27/03/2015'::text, 'DD/MM/YYYY'::text)))
(5 lignes)
Temps : 3,373 ms
postgres=# select avg(mtv) from ventes_agg where dtv >= to_timestamp('13/03/2015','DD/MM/YYYY') and dtv < to_timestamp('27/03/2015','DD/MM/YYYY');
avg
--------------------
50002.089811507937
(1 ligne)
Temps : 4624,098 ms
postgres=# select avg(mtv) from ventes_agg where dtv >= to_timestamp('13/03/2015','DD/MM/YYYY') and dtv < to_timestamp('27/03/2015','DD/MM/YYYY');
avg
--------------------
50002.089811507937
(1 ligne)
Temps : 4584,642 ms
postgres=# select avg(mtv) from ventes_agg where dtv >= to_timestamp('13/03/2015','DD/MM/YYYY') and dtv < to_timestamp('27/03/2015','DD/MM/YYYY');
avg
--------------------
50002.089811507937
(1 ligne)
Temps : 4494,030 ms
postgres=# update ventes_agg set dtv = dtv - (2*id || ' seconds')::interval;
UPDATE 31536001
Temps : 347917,222 ms
postgres=# select avg(mtv) from ventes_agg where dtv >= to_timestamp('13/03/2014','DD/MM/YYYY') and dtv < to_timestamp('27/03/2014','DD/MM/YYYY');
avg
--------------------
49977.457205687831
(1 ligne)
Temps : 105802,894 ms
postgres=# select avg(mtv) from ventes_agg where dtv >= to_timestamp('13/03/2014','DD/MM/YYYY') and dtv < to_timestamp('27/03/2014','DD/MM/YYYY');
avg
--------------------
49977.457205687831
(1 ligne)
Temps : 105332,640 ms
postgres=# select avg(mtv) from ventes_agg where dtv >= to_timestamp('13/03/2014','DD/MM/YYYY') and dtv < to_timestamp('27/03/2014','DD/MM/YYYY');
avg
--------------------
49977.457205687831
(1 ligne)
Temps : 106016,737 ms
postgres=# reindex index br_dtv;
REINDEX
Temps : 12695,392 ms
postgres=# select avg(mtv) from ventes_agg where dtv >= to_timestamp('13/03/2014','DD/MM/YYYY') and dtv < to_timestamp('27/03/2014','DD/MM/YYYY');
avg
--------------------
49977.457205687831
(1 ligne)
Temps : 4733,356 ms
postgres=# select avg(mtv) from ventes_agg where dtv >= to_timestamp('13/03/2014','DD/MM/YYYY') and dtv < to_timestamp('27/03/2014','DD/MM/YYYY');
avg
--------------------
49977.457205687831
(1 ligne)
Temps : 4495,150 ms
postgres=# select avg(mtv) from ventes_agg where dtv >= to_timestamp('13/03/2014','DD/MM/YYYY') and dtv < to_timestamp('27/03/2014','DD/MM/YYYY');
avg
--------------------
49977.457205687831
(1 ligne)
Temps : 4312,399 ms
postgres=# drop table ventes_agg;
DROP TABLE
Temps : 1599,909 ms
postgres=# create table ventes_agg(id serial, dtv timestamp, mtv integer);
CREATE TABLE
Temps : 9,270 ms
postgres=# insert into ventes_agg(dtv,mtv)
postgres=# select generate_series(date_trunc('second', current_timestamp-interval '1 year'), date_trunc('second', current_timestamp), '1 second'),
postgres=# trunc(random() * 100000 + 1);
INSERT 0 31536001
Temps : 209511,529 ms
postgres=# create index id_dtv on ventes_agg(dtv);
CREATE INDEX
Temps : 73965,872 ms
postgres=# explain select avg(mtv) from ventes_agg where dtv >= to_timestamp('13/03/2015','DD/MM/YYYY') and dtv < to_timestamp('27/03/2015','DD/MM/YYYY');
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=204779.78..204779.79 rows=1 width=4)
-> Bitmap Heap Scan on ventes_agg (cost=3348.79..204385.58 rows=157680 width=4)
Recheck Cond: ((dtv >= to_timestamp('13/03/2015'::text, 'DD/MM/YYYY'::text)) AND (dtv < to_timestamp('27/03/2015'::text, 'DD/MM/YYYY'::text)))
-> Bitmap Index Scan on id_dtv (cost=0.00..3309.37 rows=157680 width=0)
Index Cond: ((dtv >= to_timestamp('13/03/2015'::text, 'DD/MM/YYYY'::text)) AND (dtv < to_timestamp('27/03/2015'::text, 'DD/MM/YYYY'::text)))
(5 lignes)
Temps : 3,240 ms
postgres=# select avg(mtv) from ventes_agg where dtv >= to_timestamp('13/03/2015','DD/MM/YYYY') and dtv < to_timestamp('27/03/2015','DD/MM/YYYY');
avg
--------------------
49986.775383597884
(1 ligne)
Temps : 779,943 ms
postgres=# select avg(mtv) from ventes_agg where dtv >= to_timestamp('13/03/2015','DD/MM/YYYY') and dtv < to_timestamp('27/03/2015','DD/MM/YYYY');
avg
--------------------
49986.775383597884
(1 ligne)
Temps : 828,564 ms
postgres=# select avg(mtv) from ventes_agg where dtv >= to_timestamp('13/03/2015','DD/MM/YYYY') and dtv < to_timestamp('27/03/2015','DD/MM/YYYY');
avg
--------------------
49986.775383597884
(1 ligne)
Temps : 893,620 ms
postgres=# update ventes_agg set dtv = dtv - (2*id || ' seconds')::interval;
UPDATE 31536001
Temps : 506365,702 ms
postgres=# select avg(mtv) from ventes_agg where dtv >= to_timestamp('13/03/2014','DD/MM/YYYY') and dtv < to_timestamp('27/03/2014','DD/MM/YYYY');
avg
--------------------
49994.196351686508
(1 ligne)
Temps : 865,246 ms
postgres=# select avg(mtv) from ventes_agg where dtv >= to_timestamp('13/03/2014','DD/MM/YYYY') and dtv < to_timestamp('27/03/2014','DD/MM/YYYY');
avg
--------------------
49994.196351686508
(1 ligne)
Temps : 820,067 ms
postgres=# select avg(mtv) from ventes_agg where dtv >= to_timestamp('13/03/2014','DD/MM/YYYY') and dtv < to_timestamp('27/03/2014','DD/MM/YYYY');
avg
--------------------
49994.196351686508
(1 ligne)
Temps : 769,410 ms