Filtrer sur les dates

      Dans cet article présentant un cas d’utilisation du traqueur, il est apparu qu’une conversion, implicite avec Oracle Database et devenue explicite avec PostgreSQL, posait un problème de performance. En effet l’index présent sur une colonne n’était pas utilisé.
      Parfois, le problème est plus sournois à déterminer.
      Margiono, le scribe du clan des géants, va ici tenter de déterminer la taille moyenne des géants nés en mai 2015. Comme d’habitude le chef du clan, Oumpfor, veut la réponse le plus vite possible.
      Démonstration avec PostgreSQL 10 beta :

select version(); version ----------------------------------------------------------------------------------------------------- PostgreSQL 10beta3 on x86_64-pc-linux-gnu, compiled by gcc (Debian 6.3.0-18) 6.3.0 20170516, 64-bit (1 ligne) create table geants( idg integer generated by default as identity, idgu uuid, dtn timestamp, taille smallint, devise varchar(128), berserk boolean); CREATE TABLE CREATE EXTENSION IF NOT EXISTS "uuid-ossp"; CREATE EXTENSION with recursive serie(i) as (select 5500000 UNION ALL select i - 1 from serie where i > 1000001) insert into geants(idgu, dtn, taille, devise, berserk) select uuid_generate_v4(), current_timestamp - (ceil(i/3) || ' minutes')::interval + (trunc(random() * 100 + 1) || ' days')::interval, 200 + (trunc(random() * 200 + 1)), upper(md5(random()::text)), case when random() < 0.001 then true else false end from serie; INSERT 0 4500000 create index geants_i1 on geants(dtn); CREATE INDEX \d geants Table « public.geants » Colonne | Type | Collationnement | NULL-able | Par défaut ---------+-----------------------------+-----------------+-----------+---------------------------------- idg | integer | | not null | generated by default as identity idgu | uuid | | | dtn | timestamp without time zone | | | taille | smallint | | | devise | character varying(128) | | | berserk | boolean | | | Index : "geants_i1" btree (dtn) analyze geants; ANALYZE \timing Chronométrage activé. -- requete 1 explain select avg(taille) from geants where to_char(dtn, 'MM/YYYY') = '05/2015'; QUERY PLAN ---------------------------------------------------------------------------------------- Finalize Aggregate (cost=84704.55..84704.56 rows=1 width=32) -> Gather (cost=84704.33..84704.54 rows=2 width=32) Workers Planned: 2 -> Partial Aggregate (cost=83704.33..83704.34 rows=1 width=32) -> Parallel Seq Scan on geants (cost=0.00..83680.89 rows=9375 width=2) Filter: (to_char(dtn, 'MM/YYYY'::text) = '05/2015'::text) select avg(taille) from geants where to_char(dtn, 'MM/YYYY') = '05/2015'; avg ---------------------- 300.5692719910785789 (1 ligne) Durée : 4144,292 ms (00:04,144) ... Durée : 3850,802 ms (00:03,851) ... Durée : 4067,351 ms (00:04,067) -- requete 2 explain select avg(taille) from geants where dtn >= to_timestamp('01/05/2015', 'DD/MM/YYYY') and dtn < to_timestamp('01/06/2015', 'DD/MM/YYYY'); QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------------------------------ Aggregate (cost=8159.07..8159.08 rows=1 width=32) -> Index Scan using geants_i1 on geants (cost=0.44..7820.78 rows=135315 width=2) Index Cond: ((dtn >= to_timestamp('01/05/2015'::text, 'DD/MM/YYYY'::text)) AND (dtn < to_timestamp('01/06/2015'::text, 'DD/MM/YYYY'::text))) select avg(taille) from geants where dtn >= to_timestamp('01/05/2015', 'DD/MM/YYYY') and dtn < to_timestamp('01/06/2015', 'DD/MM/YYYY'); avg ---------------------- 300.5692719910785789 (1 ligne) Temps : 212,317 ms ... Temps : 220,057 ms ... Temps : 220,386 ms -- requete 3 explain select avg(taille) from geants where dtn >= timestamp '01/05/2015' and dtn < timestamp '01/06/2015'; QUERY PLAN -------------------------------------------------------------------------------------------------------------------------------------------------- Aggregate (cost=8152.89..8152.90 rows=1 width=32) -> Index Scan using geants_i1 on geants (cost=0.43..7814.73 rows=135262 width=2) Index Cond: ((dtn >= '2015-05-01 00:00:00'::timestamp without time zone) AND (dtn < '2015-06-01 00:00:00'::timestamp without time zone)) select avg(taille) from geants where dtn >= timestamp '01/05/2015' and dtn < timestamp '01/06/2015'; avg ---------------------- 300.5692719910785789 (1 ligne) Temps : 183,920 ms ... Temps : 184,647 ms ... Temps : 169,943 ms

      Une fonction est appliquée sur la colonne dtn dans la clause de filtrage de la 1ère requête. L’index créé directement sur la colonne dtn n’est donc pas utilisé et un balayage complet de la table est effectué en parallèle. Cette syntaxe est à proscrire.
      La 2ème requête n’a pas un problème aussi important. Aucune fonction n’est appliquée sur dtn et l’index peut être utilisé. La fonction de formatage to_timestamp est cependant ici inutile, c’est une fonction ayant pour arguments 2 chaînes de caractères et renvoyant un timestamp with timezone. Les temps de réponse de la requête peuvent encore être améliorés en n’utilisant pas to_timestamp mais directement des valeurs de type timestamp.
      C’est le cas avec la syntaxe de la 3ème requête. Les temps d’exécution sont en moyenne 40ms inférieurs à ceux de la 2ème requête, différence loin d’être négligeable pour une requête durant 200ms environ.

Mise à jour : 23/08/2017