select version();
version
----------------------------------------------------------------
PostgreSQL 9.6beta1, compiled by Visual C++ build 1800, 64-bit
(1 ligne)
create table ventes_agg(dtv timestamp, mtv smallint);
CREATE TABLE
do
$$
begin
for i in 1..5256000 loop
insert into ventes_agg(dtv,mtv) values (current_timestamp - i * '1 minute'::interval, trunc(random()*100));
end loop;
end$$;
DO
select extract(year from dtv), count(*) from ventes_agg group by extract(year from dtv) order by 1;
date_part | count
-----------+--------
2006 | 307524
2007 | 525600
2008 | 527040
2009 | 525600
2010 | 525600
2011 | 525600
2012 | 527040
2013 | 525600
2014 | 525600
2015 | 525600
2016 | 215196
(11 lignes)
\dt+ ventes_agg
Liste des relations
Schéma | Nom | Type | Propriétaire | Taille | Description
--------+------------+-------+--------------+--------+-------------
public | ventes_agg | table | postgres | 222 MB |
(1 ligne)
explain analyze select avg(mtv) from ventes_agg where dtv between to_date('01/01/2015', 'DD/MM/YYYY') and to_date('01/01/2016','DD/MM/YYYY');
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------------
Finalize Aggregate (cost=56018.29..56018.30 rows=1 width=32) (actual time=3281.515..3281.515 rows=1 loops=1)
-> Gather (cost=56017.86..56018.27 rows=4 width=32) (actual time=3281.487..3281.501 rows=5 loops=1)
Workers Planned: 4
Workers Launched: 4
-> Partial Aggregate (cost=55017.86..55017.87 rows=1 width=32) (actual time=3198.423..3198.423 rows=1 loops=5)
-> Parallel Seq Scan on ventes_agg (cost=0.00..54691.00 rows=130745 width=2) (actual time=345.361..3157.326 rows=105120 loops=5)
Filter: ((dtv >= to_date('01/01/2015'::text, 'DD/MM/YYYY'::text)) AND (dtv <= to_date('01/01/2016'::text, 'DD/MM/YYYY'::text)))
Rows Removed by Filter: 946080
Planning time: 0.476 ms
Execution time: 3301.831 ms
(10 lignes)
set max_parallel_degree=0;
SET
explain analyze select avg(mtv) from ventes_agg where dtv between to_date('01/01/2015', 'DD/MM/YYYY') and to_date('01/01/2016','DD/MM/YYYY');
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=134838.46..134838.47 rows=1 width=32) (actual time=19429.577..19429.578 rows=1 loops=1)
-> Seq Scan on ventes_agg (cost=0.00..133531.00 rows=522981 width=2) (actual time=1306.770..19239.445 rows=525600 loops=1)
Filter: ((dtv >= to_date('01/01/2015'::text, 'DD/MM/YYYY'::text)) AND (dtv <= to_date('01/01/2016'::text, 'DD/MM/YYYY'::text)))
Rows Removed by Filter: 4730400
Planning time: 0.390 ms
Execution time: 19429.696 ms
(6 lignes)
create index ventes_agg_i1 on ventes_agg(dtv);
CREATE INDEX
\di+ ventes_agg_i1
Liste des relations
Schéma | Nom | Type | Propriétaire | Table | Taille | Description
--------+---------------+-------+--------------+------------+--------+-------------
public | ventes_agg_i1 | index | postgres | ventes_agg | 113 MB |
(1 ligne)
select correlation from pg_stats where tablename='ventes_agg' and attname = 'dtv';
correlation
-------------
-1
(1 ligne)
explain analyze select avg(mtv) from ventes_agg where dtv between to_date('01/01/2015', 'DD/MM/YYYY') and to_date('01/01/2016','DD/MM/YYYY');
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=20337.51..20337.52 rows=1 width=32) (actual time=416.829..416.829 rows=1 loops=1)
-> Index Scan using ventes_agg_i1 on ventes_agg (cost=0.44..19030.06 rows=522981 width=2) (actual time=0.056..276.385 rows=525600 loops=1)
Index Cond: ((dtv >= to_date('01/01/2015'::text, 'DD/MM/YYYY'::text)) AND (dtv <= to_date('01/01/2016'::text, 'DD/MM/YYYY'::text)))
Planning time: 0.248 ms
Execution time: 416.918 ms
(5 lignes)
drop index ventes_agg_i1;
DROP INDEX
create index ventes_agg_br1 on ventes_agg using brin(dtv);
CREATE INDEX
\di+ ventes_agg_br1
Liste des relations
Schéma | Nom | Type | Propriétaire | Table | Taille | Description
--------+----------------+-------+--------------+------------+--------+-------------
public | ventes_agg_br1 | index | postgres | ventes_agg | 48 kB |
(1 ligne)
explain analyze select avg(mtv) from ventes_agg where dtv between to_date('01/01/2015', 'DD/MM/YYYY') and to_date('01/01/2016','DD/MM/YYYY');
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=45550.63..45550.64 rows=1 width=32) (actual time=3292.303..3292.303 rows=1 loops=1)
-> Bitmap Heap Scan on ventes_agg (cost=5372.56..44243.18 rows=522981 width=2) (actual time=13.429..3111.615 rows=525600 loops=1)
Recheck Cond: ((dtv >= to_date('01/01/2015'::text, 'DD/MM/YYYY'::text)) AND (dtv <= to_date('01/01/2016'::text, 'DD/MM/YYYY'::text)))
Rows Removed by Index Recheck: 19040
Heap Blocks: lossy=2944
-> Bitmap Index Scan on ventes_agg_br1 (cost=0.00..5241.81 rows=522981 width=0) (actual time=0.890..0.890 rows=29440 loops=1)
Index Cond: ((dtv >= to_date('01/01/2015'::text, 'DD/MM/YYYY'::text)) AND (dtv <= to_date('01/01/2016'::text, 'DD/MM/YYYY'::text)))
Planning time: 0.282 ms
Execution time: 3292.433 ms
(9 lignes)