postgresql - 改进 postgresql select 请求执行时间

Question

我正在尝试缩短此选择请求的执行时间，该请求需要 3 秒才能完成。 Mesure_capteur 表有大约 200 万行和 Capteur 400。我为 Capteur.nom_capteur 和 Mesure_Capteur.id_capteur 添加了索引。这样做需要 10 秒钟，但我知道之后要做什么。

SELECT C.nom_capteur, mC.horodateur, mC.valeur_mesure_capteur
FROM Mesure_Capteur mC INNER JOIN Capteur C 
    ON mC.id_capteur = C.id_capteur
WHERE C.nom_capteur = 'ENEEANABatterie005'
    AND mC.horodateur between '2017-10-15 00:00:00' and '2017-10-15 01:00:00'
ORDER BY mC.horodateur DESC

这是我的两个表:

create table Capteur(
    id_capteur int  primary key,
    nom_capteur varchar(180),
    description_capteur varchar(100),
    id_sous_systeme int,
    description_unite varchar(50),
    unite varchar(10),
    seuil_min real,
    seuil_max real,
    FOREIGN KEY (id_sous_systeme) REFERENCES Sous_systeme(id_sous_systeme)
);

-

create table Mesure_capteur(
    id_mesure_capteur  serial PRIMARY KEY,
    valeur_mesure_capteur NUMERIC(20,2),
    id_capteur int,
    FOREIGN KEY (id_capteur) REFERENCES Capteur(id_capteur),
    horodateur abstime
);

下面是解释分析:

'Sort  (cost=352931.30..352931.30 rows=1 width=25) (actual time=3147.592..3147.625 rows=494 loops=1)'
'  Sort Key: mc.horodateur DESC'
'  Sort Method: quicksort  Memory: 51kB'
'  ->  Nested Loop  (cost=3361.03..352931.29 rows=1 width=25) (actual time=1485.653..3147.419 rows=494 loops=1)'
'        ->  Index Scan using capteur_nom_capteur_index on capteur c  (cost=0.27..8.29 rows=1 width=22) (actual time=0.045..0.047 rows=1 loops=1)'
'              Index Cond: ((nom_capteur)::text = 'ENEEANABatterie005'::text)'
'        ->  Bitmap Heap Scan on mesure_capteur mc  (cost=3360.76..352922.99 rows=1 width=11) (actual time=1485.598..3147.304 rows=494 loops=1)'
'              Recheck Cond: (id_capteur = c.id_capteur)'
'              Rows Removed by Index Recheck: 17942069'
'              Filter: ((horodateur >= '2017-10-15 00:00:00+02'::abstime) AND (horodateur <= '2017-10-15 01:00:00+02'::abstime))'
'              Rows Removed by Filter: 181360'
'              Heap Blocks: exact=45030 lossy=99772'
'              ->  Bitmap Index Scan on mesure_capteur_id_capteur_index  (cost=0.00..3360.76 rows=181359 width=0) (actual time=63.333..63.333 rows=181854 loops=1)'
'                    Index Cond: (id_capteur = c.id_capteur)'
'Planning time: 0.367 ms'
'Execution time: 3148.039 ms'

感谢您的帮助。

Answer 1

对于这个查询:

SELECT C.nom_capteur, mC.horodateur, mC.valeur_mesure_capteur
FROM Mesure_Capteur mC INNER JOIN
     Capteur C
     ON mC.id_capteur = C.id_capteur
WHERE C.nom_capteur = 'ENEEANABatterie005' AND
      mC.horodateur between '2017-10-15 00:00:00' and '2017-10-15 01:00:00'
ORDER BY mC.horodateur DESC;

我建议在 Mesure_Capteur(horodateur, id_capteur) 和 Capteur(id_capteur, nom_capteur) 上建立索引。如果 id_capteur 已声明为主键，则不需要后者。