sql - 如何改进这个 Postgres 查询？

Question

我有 sql 查询:

SELECT cast(AVG(SNR) AS integer) AS snr,
       cast(AVG(RSSI) AS integer) AS rts
FROM SESSION
WHERE DATE(associationtime)>DATE(NOW()- INTERVAL '21 DAYS');

它运行缓慢，因为 21 天包含 30 万行。

 Aggregate  (cost=21768.07..21768.09 rows=1 width=8) (actual time=346.794..346.795 rows=1 loops=1)
   ->  Seq Scan on session  (cost=0.00..20095.77 rows=334459 width=8) (actual time=0.014..282.512 rows=345304 loops=1)
         Filter: (date(associationtime) > date((now() - '21 days'::interval)))
         Rows Removed by Filter: 148508
 Total runtime: 346.867 ms

如何改进我的查询？我可以创建索引或其他东西吗？

更新:

associationtime 上的索引没有帮助。

postgres=# CREATE INDEX session_lim_values_idx ON session (associationtime);
CREATE INDEX
postgres=# EXPLAIN (ANALYZE) SELECT cast(AVG(SNR) as integer) as snr, cast(AVG(RSSI) as integer)  as rts FROM session WHERE DATE(associationtime)>DATE(NOW()- INTERVAL '21 DAYS');
                                                      QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------
 Aggregate  (cost=21768.07..21768.09 rows=1 width=8) (actual time=347.654..347.654 rows=1 loops=1)
   ->  Seq Scan on session  (cost=0.00..20095.77 rows=334459 width=8) (actual time=0.014..283.344 rows=345304 loops=1)
         Filter: (date(associationtime) > date((now() - '21 days'::interval)))
         Rows Removed by Filter: 148508
 Total runtime: 347.731 ms

还有DATE(associationtime):

postgres=# CREATE INDEX session_lim_values_idx ON session (DATE(associationtime));
CREATE INDEX
postgres=# EXPLAIN (ANALYZE) SELECT cast(AVG(SNR) as integer) as snr, cast(AVG(RSSI) as integer)  as rts FROM session WHERE DATE(associationtime)>DATE(NOW()- INTERVAL '21 DAYS');
                                                      QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------
 Aggregate  (cost=21768.07..21768.09 rows=1 width=8) (actual time=341.050..341.050 rows=1 loops=1)
   ->  Seq Scan on session  (cost=0.00..20095.77 rows=334459 width=8) (actual time=0.015..278.247 rows=345304 loops=1)
         Filter: (date(associationtime) > date((now() - '21 days'::interval)))
         Rows Removed by Filter: 148508
 Total runtime: 341.129 ms

Answer 1

因为您只关心整天，所以您可能希望将结果缓存在物化 View 中。

CREATE MATERIALIZED VIEW matview_avg_session
AS SELECT cast(AVG(SNR) AS integer) AS snr,
   cast(AVG(RSSI) AS integer) AS rts
   FROM SESSION
   WHERE DATE(associationtime) > DATE(NOW()- INTERVAL '21 DAYS');

然后像这样访问数据:

SELECT * FROM matview_avg_session;

并像这样刷新它(每天自动一次):

REFRESH MATERIALIZED VIEW matview_avg_session;

或者您查看有关如何创建用于刷新它的触发器的答案，但请记住，您不想在每次插入后都执行此操作... Refresh a materialized view automatically using a rule or notify