Vous n'êtes pas identifié(e).
- Contributions : Récentes | Sans réponse
Pages : 1
#1 Optimisation » Question sur les order by » 11/03/2010 11:49:18
- Mekhanik
- Réponses : 2
Bonjour,
Comment fonctionne les order by ?
J'ai fait le test suivant :
J'ai créé une table ordonné avec un start_time par ordre décroissant.
Cette table est non indexée.
Si je prend les 2 premières lignes sans order by j'obtiens le résultat suivant
Day
------------------------
2010-03-10 15:08:48+01
2010-03-10 15:08:30+01
et si je fait la même requete avec order by
Day
------------------------
2010-03-10 23:59:59+01
2010-03-10 23:59:59+01
Mon but est de préordonner ma table en fonction de start_time pour suprrimer le order by dans les requetes.
Les temps de réponse avec ou sans sont en effet très différents.
Mais ce n'est peut-être pas possible de cette façon là ...
Je me suis inspiré d'un autre billet qui conseillais de préordonner les tables notamment par la commander cluster order by pour gagner en perf
#2 Re : Optimisation » Execution d'une requete lente seulement à la première exécution » 03/03/2010 14:45:29
Bonjour je vous remercie pour votre aide mais les différentes solutions n'ont rien donné par contre en divisant la table_primaire en plusieurs tables nous obtenons des satisfaisaites, nous allons nous orienter vers cette solution.
Merci encore
#3 Re : Optimisation » Execution d'une requete lente seulement à la première exécution » 02/03/2010 16:16:01
Voici les résultats du test proposé :
explain analyze
select date_trunc( 'second', TIMESTAMP WITH TIME ZONE 'epoch' + tmp.start_time/1000 * interval '1 second') as "Day",
to_char(TIMESTAMP WITH TIME ZONE 'epoch' + (tmp.stop_time/1000) * interval '1 second', 'HH24:MI:SS') as "->",
tmp.* FROM
((SELECT
a.param_4_integer,
a.param5_integer,
COALESCE(a.champs3_char,'') ,
a.champs_6_bigint,
a.champs_7_bigint,
a.champs_8_bigint,
a.champs_9_bigint,
a.champs_10_bigint as proc_id
from
table_primaire as a
WHERE
a.time_idx= 1267225200
AND a.param_2_integer = 329728
AND a.param_3_integer = 255
AND a.param_4_integer = 3
AND a.param_5_integer = 371
AND a.param_1_integer = 12
order by start_time desc
limit 50)
UNION ALL
(SELECT
a.param_4_integer,
a.param5_integer,
COALESCE(a.champs3_char,'') ,
a.champs_6_bigint,
a.champs_7_bigint,
a.champs_8_bigint,
a.champs_9_bigint,
a.champs_10_bigint as proc_id
from
table_primaire as a
WHERE
a.time_idx= 1267225200
AND a.param_2_integer = 329728
AND a.param_3_integer = 255
AND a.param_4_integer = 3
AND a.param_5_integer = 371
AND a.param_1_integer = 13
order by start_time desc
limit 50)) AS tmp order by start_time desc limit 50;
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ Limit (cost=656.87..656.90 rows=10 width=88) (actual time=54675.783..54675.783 rows=0 loops=1)
-> Sort (cost=656.87..656.90 rows=10 width=88) (actual time=54675.781..54675.781 rows=0 loops=1)
Sort Key: "*SELECT* 1".start_time
Sort Method: quicksort Memory: 17kB
-> Result (cost=0.00..656.71 rows=10 width=88) (actual time=54675.770..54675.770 rows=0 loops=1)
-> Append (cost=0.00..656.46 rows=10 width=88) (actual time=54675.768..54675.768 rows=0 loops=1)
-> Subquery Scan "*SELECT* 1" (cost=0.00..328.27 rows=9 width=88) (actual time=54661.435..54661.435 rows=0 loops=1)
-> Limit (cost=0.00..328.18 rows=9 width=57) (actual time=54661.433..54661.433 rows=0 loops=1)
-> Index Scan using table_primaire__start_time_desc__index on table_primaire a (cost=0.00..328.18 rows=9 width=57) (actual time=54661.430..54661) Index Cond: ((time_idx = 1267225200) AND (param_2_integer = 329728) AND (param_3_integer = 255) AND (param_4_integer = 3) AND (param_5_integer = 371))
Filter: (stack = 12)
-> Subquery Scan "*SELECT* 2" (cost=0.00..328.19 rows=1 width=88) (actual time=14.329..14.329 rows=0 loops=1)
-> Limit (cost=0.00..328.18 rows=1 width=57) (actual time=14.327..14.327 rows=0 loops=1)
-> Index Scan using table_primaire__start_time_desc__index on table_primaire a (cost=0.00..328.18 rows=1 width=57) (actual time=14.324..14.324 r) Index Cond: ((time_idx = 1267225200) AND (type = param_2_integer) AND (subtype = param_3_integer) AND (param_4_integer = 3) AND (param_5_integer = 371))
Filter: (stack = 13)
Total runtime: 54675.899 ms
(17 rows)
Time: 54790.519 ms
#4 Re : Optimisation » Execution d'une requete lente seulement à la première exécution » 02/03/2010 15:01:36
Tout est plus au début du post,
j'ai juste anonymisée les champs mais la requête est exactement celle que j'ai tenté.
La table_primaire grossis de 1 Millions de lignes environs toutes les nuits et ne bougent plus après, les index sont détruits avant insertion et recréés après, un analyze est ensuite lancé
La table_secondaire ne change jamais
#5 Re : Optimisation » Execution d'une requete lente seulement à la première exécution » 02/03/2010 13:54:00
Je viens de remarquer que mon effective_cache_size est configuré à 128 MB.
Puis-je l'augmenter vu ma config ?
Qu'apporte ce paramètre ?
Merci pour toutes vos réponses en tout cas.
#6 Re : Optimisation » Execution d'une requete lente seulement à la première exécution » 02/03/2010 12:38:40
Bonjour,
j'ai fait le test mais cela ne donne qu'une légère amélioration.
Le problème c'est la mise en cache de l'information je pense :
à la première exécution c'est très long
à la deuxième aucun soucis
si je relance une autre requete c'est très long
si je reviens sur la première c'est très long
....
Pensez - vous que l'utilisation d'une vue donnant une table réduite pourrait améliorer les performances ?
Ou carrément la création d'une sous-table ?
Je n'ai pas d'autre piste pour l'instant.
Nous allons bientot passer en 8Giga de ram et peut-être plus est-ce un moyen de gagner en performance dans ce cas ci ?
Où est-ce que le nombre de ligne + la requete ne donneront rien de mieux niveau perf ?
#7 Re : Optimisation » Execution d'une requete lente seulement à la première exécution » 01/03/2010 19:51:06
Du coup j'ai créé un index pour chacune des colonnes, voici le résultat :
Mais du coup, je me dis que la construction d'index n'est peut-être pas optimum.
Dans ce cas chaque index fait quasiment 2 giga.
Limit (cost=277098.81..277098.93 rows=50 width=89) (actual time=215412.684..215412.807 rows=50 loops=1)
-> Sort (cost=277098.81..277099.36 rows=223 width=89) (actual time=215412.681..215412.728 rows=50 loops=1)
Sort Key: a.start_time
Sort Method: top-N heapsort Memory: 23kB
-> Hash Left Join (cost=183558.79..277091.40 rows=223 width=89) (actual time=203689.855..215370.352 rows=14794 loops=1)
Hash Cond: (a.end_code = c.end_code)
-> Bitmap Heap Scan on table_primaire a (cost=183546.31..277070.28 rows=223 width=61) (actual time=203652.352..215159.757 rows=14794 loops=1)
Recheck Cond: ((param_5_integer = 371) AND (param_2_integer = 329728))
Filter: ((time_idx = 1267225200) AND (param_3_integer = 255) AND (param_4_integer = 3) AND (stack = ANY ('{12,13,19,1,2,21,22,32,31}'::integer[])))
-> BitmapAnd (cost=183546.31..183546.31 rows=25360 width=0) (actual time=120291.092..120291.092 rows=0 loops=1)
-> Bitmap Index Scan on table_primaire__param_5_integer__index (cost=0.00..4059.09 rows=248713 width=0) (actual time=81.654..81.654 rows=263916 loops=1)
Index Cond: (param_5_integer = 371)
-> Bitmap Index Scan on d_cms_activity__param_2_integer__index (cost=0.00..179486.86 rows=11026282 width=0) (actual time=120071.308..120071.308 rows=11102682 loo) Index Cond: (param_2_integer = 329728)
-> Hash (cost=7.77..7.77 rows=377 width=36) (actual time=16.658..16.658 rows=377 loops=1)
-> Seq Scan on table_secondaire c (cost=0.00..7.77 rows=377 width=36) (actual time=8.223..16.259 rows=377 loops=1)
Total runtime: 215422.419 ms
#8 Re : Optimisation » Execution d'une requete lente seulement à la première exécution » 01/03/2010 18:56:12
Limit (cost=328.74..328.86 rows=50 width=89) (actual time=66242.212..66242.324 rows=50 loops=1)
-> Sort (cost=328.74..328.90 rows=66 width=89) (actual time=66242.209..66242.248 rows=50 loops=1)
Sort Key: a.start_time
Sort Method: top-N heapsort Memory: 23kB
-> Hash Left Join (cost=12.48..326.74 rows=66 width=89) (actual time=90.535..66191.382 rows=14794 loops=1)
Hash Cond: (a.end_code = c.end_code)
-> Index Scan using table_primaire__start_time_desc__index on table_primaire a (cost=0.00..311.70 rows=66 width=61) (actual time=74.016..65965.591 rows=14794 loop) Index Cond: ((time_idx = 1267225200) AND (param_2_integer = 329728) AND (param_3_integer = 255) AND (param_4_integer = 3) AND (param_5_integer = 371))
Filter: (a.param_1_integer = ANY ('{12,13,19,1,2,21,22,32,31}'::integer[]))
-> Hash (cost=7.77..7.77 rows=377 width=36) (actual time=16.473..16.473 rows=377 loops=1)
-> Seq Scan on table_secondaire c (cost=0.00..7.77 rows=377 width=36) (actual time=7.120..16.068 rows=377 loops=1)
Total runtime: 66242.448 ms
Voilà le résultat avec les valeur de l'ensemble
#9 Re : Optimisation » Execution d'une requete lente seulement à la première exécution » 01/03/2010 17:26:07
Ma version de postgresql est la 8.3.
Le serveur correspondant possède 4Go de ram
#10 Re : Optimisation » Execution d'une requete lente seulement à la première exécution » 01/03/2010 17:17:37
En fait start_time_bigint fait bien parti de l'index :
"table_primaire__start_time_desc__index" btree (time_idx, param_2_integer, param_3_integer, param_4_integer, param_5_integer, start_time_bigint DESC)
#11 Re : Optimisation » Execution d'une requete lente seulement à la première exécution » 01/03/2010 17:15:25
J'ai tenté un reindex, le résultat est identique.
L'index fait 5 129 MB, j'imagine que c'est énorme ...
Je tente l'indexation sur limit.
non param1 n'est pas dans l'index mais j'avais déjà fait le test avant en obtenant des résultats identiques.
#12 Optimisation » Execution d'une requete lente seulement à la première exécution » 01/03/2010 16:04:06
- Mekhanik
- Réponses : 22
Bonjour,
j'ai un soucis avec l'éxécution d'une requete, lors de la première exécution,
le temps d'éxécution est très long puis pour toutes les rééxécutions suivantes, le temps de réponse est instané.
Qu'est ce qui peux provoquer ce genre de comportement ?
Une mise en cache des données ?
Une mauvaise config de postgresql ?
Une mauvaise conscruction des index ?
Une mauvaise conscruction de la requete ?
ma config postgresql :
shared_buffers = 512MB
temp_buffers = 100MB
work_mem = 50MB
maintenance_work_mem = 64MB
max_stack_depth = 6MB
max_fsm_pages = 153600
bgwriter_delay = 400ms
Structure de la table_primaire
Column | Type | Modifiers
--------------------+-----------------------+-----------
time_idx | bigint
param_1 | integer
param_2 | integer
param_3 | integer
champs_8_bigint | bigint
champs_9_bigint | integer
champs_6_bigint | bigint
champs_7_bigint | integer
start_time | bigint
non_use_1 | integer
non_use_2 | integer
stop_time | bigint
param_4 | integer
param_5 | integer
end_code | integer
non_use_3 | integer
non_use_4 | integer
non_use_5 | smallint
non_use_6 | smallint
non_use_7 | integer
non_use_8 | bigint
non_use_9 | integer
non_use_10 | integer
non_use_11 | character varying(6)
non_use_12 | smallint
champs3_char | character varying(24)
champs_10_bigint | bigint
non_use_13 | smallint
non_use_14 | smallint
Indexes:
"table_primaire__otdi__index" btree (champs3_char) WHERE champs3_char::text <> ''::text
"table_primaire__start_time_desc__index" btree (time_idx, param_2_integer, param_3_integer, param_4_integer, param_5_integer, start_time DESC)
"table_primaire__subs_idx__index" btree (champs_8_bigint, champs_9_bigint)
"table_primaire__time_idx__index" btree (time_idx)
Toutes les colonnes de l'index table_primaire__start_time_desc__index ont une valeur de la propriété statistics à 100
Structure de la table_secondaire
Column | Type | Modifiers
----------+-----------------------------+---------
end_code | integer | not null
txt | character varying(255) |
Indexes:
"table_secondaire_pkey" PRIMARY KEY, btree (end_code)
Volume :
table_primaire : 108 127 662 rows
table_secondaire : 377 rows
la requete :
explain analyze select date_trunc( 'second', TIMESTAMP WITH TIME ZONE 'epoch' + start_time_bigint/1000 * interval '1 second') as "Day",
to_char(TIMESTAMP WITH TIME ZONE 'epoch' + (a.stop_time_bigint/1000) * interval '1 second', 'HH24:MI:SS') as "->",
a.param_4_integer,
a.param5_integer,
COALESCE(a.champs3_char,'') ,
champs_4_char,
a.champs_6_bigint,
a.champs_7_bigint,
a.champs_8_bigint,
a.champs_9_bigint,
a.champs_10_bigint as proc_id
from
table_primaire as a
left join table_secondaire as c on ( a.end_code_integer = c.end_code_integer )
WHERE
a.time_idx_bigint= 1266879600
AND a.param_2_integer = 329728
AND a.param_3_integer = 255
AND a.param_4_integer = 3
AND a.param_5_integer = 371
AND a.param_1_integer IN (SELECT temp_param FROM params.table_txt WHERE mode= 'value' )
order by start_time_bigint desc
limit 50;
première exécution :
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=334.10..334.20 rows=38 width=89) (actual time=25025.295..25025.416 rows=50 loops=1)
-> Sort (cost=334.10..334.20 rows=38 width=89) (actual time=25025.290..25025.332 rows=50 loops=1)
Sort Key: a.start_time
Sort Method: top-N heapsort Memory: 23kB
-> Hash Left Join (cost=14.04..333.10 rows=38 width=89) (actual time=33.003..24984.522 rows=14794 loops=1)
Hash Cond: (a.end_code_integer = c.end_code_integer)
-> Hash Join (cost=1.56..319.15 rows=38 width=61) (actual time=24.922..24822.676 rows=14794 loops=1)
Hash Cond: (a.param_1_integer = table_txt.temp_param)
-> Index Scan using table_primaire__start_time_desc__index on table_primaire a (cost=0.00..316.61 rows=155 width=65) (actual time=24.827..24764.690 rows=14794 loops=1)
Index Cond: ((time_idx = 1267225200) AND (param_2_integer = 329728) AND (param_3_integer = 255) AND (param_4_integer = 3) AND (param_5_integer = 371))
-> Hash (cost=1.51..1.51 rows=4 width=4) (actual time=0.073..0.073 rows=9 loops=1)
-> HashAggregate (cost=1.47..1.51 rows=4 width=4) (actual time=0.054..0.062 rows=9 loops=1)
-> Seq Scan on table_txt (cost=0.00..1.45 rows=9 width=4) (actual time=0.014..0.027 rows=9 loops=1)
Filter: ((mode)::text = 'value'::text)
-> Hash (cost=7.77..7.77 rows=377 width=36) (actual time=5.548..5.548 rows=377 loops=1)
-> Seq Scan on table_secondaire c (cost=0.00..7.77 rows=377 width=36) (actual time=0.008..0.359 rows=377 loops=1)
Total runtime: 25025.577 ms
(17 rows)
Time: 25026.833 ms
Deuxième éxécution et exécution suivante:
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=334.10..334.20 rows=38 width=89) (actual time=165.959..166.087 rows=50 loops=1)
-> Sort (cost=334.10..334.20 rows=38 width=89) (actual time=165.957..166.010 rows=50 loops=1)
Sort Key: a.start_time
Sort Method: top-N heapsort Memory: 23kB
-> Hash Left Join (cost=14.04..333.10 rows=38 width=89) (actual time=0.850..143.952 rows=14794 loops=1)
Hash Cond: (a.end_code = c.end_code)
-> Hash Join (cost=1.56..319.15 rows=38 width=61) (actual time=0.098..62.936 rows=14794 loops=1)
Hash Cond: (a.param_1_integer = table_txt.temp_param)
-> Index Scan using table_primaire__start_time_desc__index on table_primaire a (cost=0.00..316.61 rows=155 width=65) (actual time=0.037..28.264 rows=14794 loops=1)
Index Cond: ((time_idx = 1267225200) AND (param_2_integer = 329728) AND (param_3_integer = 255) AND (param_4_integer = 3) AND (param_5_integer = 371))
-> Hash (cost=1.51..1.51 rows=4 width=4) (actual time=0.054..0.054 rows=9 loops=1)
-> HashAggregate (cost=1.47..1.51 rows=4 width=4) (actual time=0.033..0.042 rows=9 loops=1)
-> Seq Scan on table_txt (cost=0.00..1.45 rows=9 width=4) (actual time=0.006..0.018 rows=9 loops=1)
Filter: ((mode)::text = 'value'::text)
-> Hash (cost=7.77..7.77 rows=377 width=36) (actual time=0.732..0.732 rows=377 loops=1)
-> Seq Scan on table_secondaire c (cost=0.00..7.77 rows=377 width=36) (actual time=0.006..0.360 rows=377 loops=1)
Total runtime: 166.223 ms
(17 rows)
Merci d'avance pour vos explications
Pages : 1