Forums PostgreSQL.fr

7uc0

Membre

Optimisation [Suite] - Impact order by

Bonjour,

je poursuis la série des best practices pour tenter de mieux comprende le fonctionnement du moteur sur des cas d'ordonnancement. Je m'explique:
la requête suivante  porte sur une table d'à peu près (500.0000 enregistrements), le temps de réponse sans order by est "normal"

explain analyze select broadcast0_.id as id40_ from snsrbroadcast as broadcast0_ where broadcast0_.gateway_id=61172076 limit 20;

QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=0.00..76.16 rows=20 width=8) (actual time=0.050..0.234 rows=20 loops=1)
   ->  Index Scan using idx_broadcast_gateway on snsrbroadcast broadcast0_  (cost=0.00..11192.09 rows=2939 width=8) (actual time=0.049..0.209 rows=20 loops=1)
         Index Cond: (gateway_id = 61172076)
 Total runtime: 0.270 ms
(4 rows)

En revanche, l'ajout d'une contrainte d'ordonnancement rend l'execution beaucoup plus longue.

explain analyze select broadcast0_.id as id40_ from snsrbroadcast as broadcast0_ where broadcast0_.gateway_id=61172076 order by broadcast0_.receptiondate desc limit 20;
                                                                                       QUERY PLAN                                                                                        
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=0.00..593.77 rows=20 width=16) (actual time=106120.505..106131.201 rows=20 loops=1)
   ->  Index Scan Backward using idx_broadcast_receptiondate on snsrbroadcast broadcast0_  (cost=0.00..87254.69 rows=2939 width=16) (actual time=106120.502..106131.169 rows=20 loops=1)
         Filter: (gateway_id = 61172076)
 Total runtime: 106131.253 ms

Ce qui me surprend encore plus, c'est que la bascule en asc conduit à un temps plus long que la requête initiale, mais négligeable par rapport à l'execution en desc

explain analyze select broadcast0_.id as id40_ from snsrbroadcast as broadcast0_ where broadcast0_.gateway_id=61172076 order by broadcast0_.receptiondate asc limit 20;
                                                                                QUERY PLAN                                                                                 
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=0.00..593.77 rows=20 width=16) (actual time=631.043..1650.628 rows=20 loops=1)
   ->  Index Scan using idx_broadcast_receptiondate on snsrbroadcast broadcast0_  (cost=0.00..87254.69 rows=2939 width=16) (actual time=631.041..1650.586 rows=20 loops=1)
         Filter: (gateway_id = 61172076)
 Total runtime: 1650.688 ms

Quelque chose m'échappe sur le comportement du moteur, d'ou ce post. Ah, j'oubliais les caractéristique de la table sollicitée :

               Table "public.snsrbroadcast"
     Column     |            Type             | Modifiers 
----------------+-----------------------------+-----------
 id             | bigint                      | not null
 analysisdate   | timestamp without time zone | 
 analysisresult | character varying(255)      | 
 analysisstatus | character varying(255)      | 
 content        | bytea                       | 
 contentsize    | integer                     | 
 format         | character varying(255)      | 
 fullheader     | boolean                     | 
 receptiondate  | timestamp without time zone | 
 requestid      | character varying(255)      | 
 gateway_id     | bigint                      | 
Indexes:
    "snsrbroadcast_pkey" PRIMARY KEY, btree (id)
    "id_broadcast_result" btree (analysisresult)
    "id_broadcast_status" btree (analysisstatus)
    "idx_broadcast_analysisdate" btree (analysisdate)
    "idx_broadcast_gateway" btree (gateway_id)
    "idx_broadcast_receptiondate" btree (receptiondate)
Foreign-key constraints:
    "fk_broadcast_owner" FOREIGN KEY (gateway_id) REFERENCES snsrdevice(id)
Referenced by:
    TABLE "snsrexport" CONSTRAINT "fk_export_broadcast" FOREIGN KEY (broadcast_id) REFERENCES snsrbroadcast(id)
    TABLE "snsrrecord" CONSTRAINT "fk_record_broadcast" FOREIGN KEY (broadcast_id) REFERENCES snsrbroadcast(id)

-E.

Marc Cousin

Membre

Re : Optimisation [Suite] - Impact order by

La première requête demande au moteur de parcourir toute la table, jusqu'à ce qu'elle ait trouvé 20 ayant de gateway_id demandé.
La seconde demande les 20 plus grandes dates de reception. Il est donc obligé de soit toutes les récupérer et ensuite de les trier, soit les parcourir par date en arrière (en utilisant donc l'index sur receptiondate)
La dernière demande les 20 plus petites. La démarche est à peu près la même qu'avant, sauf qu'il parcourt l'index en marche avant. Il y a 2 raisons pour lesquelles il peut être plus rapide: la lecture en avant de l'index est plus rapide (le système peut correctement faire du read-ahead, et on lit le fichier «dans le sens de rotation du disque», pour simplifier un peu), ou bien tout simplement parce que des données étaient déjà en cache, ou bien qu'il a trouvé les enregistrements plus rapidement (il y aurait plus de densité de gateway_id = 61172076 en début qu'en fin d'index).

Pour aller plus vite (beaucoup plus vite ) là dessus, créez un index multicolonne sur (gateway_id,reception_date). Attention, plus il y a d'index, plus les insertions/mises à jour sont lentes…

---

Marc.

7uc0

Membre

Re : Optimisation [Suite] - Impact order by

Merci pour ce temps de réponse qui me permet de réduire le temps de réponse !