postgresql 非常慢的postgres sql查询

drkbr07n  于 2023-10-18  发布在  PostgreSQL
关注(0)|答案(1)|浏览(104)

我遇到了一个需要几十分钟的查询问题。

SELECT MAX(ts) 
FROM sensor_values 
WHERE ts >= '2020-09-01' 
  AND ts <= '2023-02-01' 
  AND sensor_id = 3502;

此sensor_id没有数据，因此应返回0行。
我的sensor_values表是一个基于季度的分区表。
定义是

CREATE TABLE IF NOT EXISTS public.sensor_values
(
    ts timestamp with time zone NOT NULL,
    value double precision NOT NULL DEFAULT 'NaN'::double precision,
    sensor_id integer NOT NULL,
    status tridium_status NOT NULL DEFAULT 'unknown'::tridium_status,
    CONSTRAINT sensor_values_ts_sensor_unq UNIQUE (ts, sensor_id),
    CONSTRAINT sensor_values_sensor_id_fkey FOREIGN KEY (sensor_id)
        REFERENCES public.sensors (id) MATCH SIMPLE
        ON UPDATE NO ACTION
        ON DELETE NO ACTION
)

子表示例

CREATE TABLE IF NOT EXISTS public.sensor_values_2020q1
(
    CONSTRAINT sensor_values_2020q1_ts_sensor_unq UNIQUE (ts, sensor_id),
    CONSTRAINT sensor_values_2020q1_ts_check CHECK (ts >= '2020-01-01 00:00:00+00'::timestamp with time zone AND ts < '2020-04-01 00:00:00+00'::timestamp with time zone)
)
    INHERITS (public.sensor_values)
TABLESPACE pg_default;

unique约束似乎是在表上添加索引
下面是一个解释分析的例子
EXPLAIN ANALYZE SELECT MAX（ts）FROM sensor_values WHERE ts >= '2022-05-01' AND ts <= '2023-02-01' AND sensor_id = 3502;

QUERY PLAN
Result  (cost=62.73..62.74 rows=1 width=8) (actual time=993680.100..993680.106 rows=1 loops=1)
  InitPlan 1 (returns $0)
    ->  Limit  (cost=2.44..62.73 rows=1 width=8) (actual time=993680.097..993680.101 rows=0 loops=1)
          ->  Merge Append  (cost=2.44..4665674.90 rows=77386 width=8) (actual time=993680.095..993680.099 rows=0 loops=1)
                Sort Key: sensor_values.ts DESC
                ->  Index Only Scan Backward using sensor_values_ts_sensor_unq on sensor_values sensor_values_1  (cost=0.12..8.15 rows=1 width=8) (actual time=0.007..0.008 rows=0 loops=1)
                      Index Cond: ((ts IS NOT NULL) AND (ts >= '2022-05-01 00:00:00+00'::timestamp with time zone) AND (ts <= '2023-02-01 00:00:00+00'::timestamp with time zone) AND (sensor_id = 3502))
                      Heap Fetches: 0
                ->  Index Only Scan Backward using sensor_values_2022q2_ts_sensor_unq on sensor_values_2022q2 sensor_values_2  (cost=0.56..940388.51 rows=15212 width=8) (actual time=446572.203..446572.203 rows=0 loops=1)
                      Index Cond: ((ts IS NOT NULL) AND (ts >= '2022-05-01 00:00:00+00'::timestamp with time zone) AND (ts <= '2023-02-01 00:00:00+00'::timestamp with time zone) AND (sensor_id = 3502))
                      Heap Fetches: 0
                ->  Index Only Scan Backward using sensor_values_2022q3_ts_sensor_unq on sensor_values_2022q3 sensor_values_3  (cost=0.56..1943951.19 rows=31973 width=8) (actual time=544350.923..544350.923 rows=0 loops=1)
                      Index Cond: ((ts IS NOT NULL) AND (ts >= '2022-05-01 00:00:00+00'::timestamp with time zone) AND (ts <= '2023-02-01 00:00:00+00'::timestamp with time zone) AND (sensor_id = 3502))
                      Heap Fetches: 0
                ->  Index Only Scan Backward using sensor_values_2022q4_ts_sensor_unq on sensor_values_2022q4 sensor_values_4  (cost=0.56..1300948.18 rows=22290 width=8) (actual time=2160.317..2160.317 rows=0 loops=1)
                      Index Cond: ((ts IS NOT NULL) AND (ts >= '2022-05-01 00:00:00+00'::timestamp with time zone) AND (ts <= '2023-02-01 00:00:00+00'::timestamp with time zone) AND (sensor_id = 3502))
                      Heap Fetches: 0
                ->  Index Only Scan Backward using sensor_values_2023q1_ts_sensor_unq on sensor_values_2023q1 sensor_values_5  (cost=0.56..479093.44 rows=7910 width=8) (actual time=596.637..596.637 rows=0 loops=1)
                      Index Cond: ((ts IS NOT NULL) AND (ts >= '2022-05-01 00:00:00+00'::timestamp with time zone) AND (ts <= '2023-02-01 00:00:00+00'::timestamp with time zone) AND (sensor_id = 3502))
                      Heap Fetches: 0
Planning Time: 4.134 ms
Execution Time: 993680.161 ms

你可以看到16分钟。它似乎做索引扫描
WA在top是在50%运行这个。
如果我重新启动postgres并通过从上面花了一段时间的子表中选择重新检查，它是即时的
例如

SELECT MAX(ts) 
FROM sensor_values_2022q2 
WHERE ts >= '2022-05-01' 
  AND ts <= '2023-02-01' 
  AND sensor_id = 3502;

只需要几秒钟。
我已对所有表和VACUUM运行分析
这是Postgres 15，我已经设置了6 GB的共享内存和effective_cache内存。
我还可以在这里检查什么？
谢谢你的任何建议。
在弗兰克斯的建议之后。事情进展很快
SELECT MAX（ts）FROM sensor_values WHERE ts >= '2000-05-01' AND ts <= '2023-02-01' AND sensor_id = 3502;
现在在几分之一秒内完成。
EXPLAIN（ANALYZE，BUFFERS）SELECT MAX（ts）FROM sensor_values WHERE ts >= '2000-05-01' AND ts <= '2023-02-01' AND sensor_id = 3502;
示出

QUERY PLAN
Result  (cost=13.16..13.17 rows=1 width=8) (actual time=0.475..0.479 rows=1 loops=1)
  Buffers: shared hit=94
  InitPlan 1 (returns $0)
    ->  Limit  (cost=13.09..13.16 rows=1 width=8) (actual time=0.472..0.475 rows=0 loops=1)
          Buffers: shared hit=94
          ->  Merge Append  (cost=13.09..41718.45 rows=603739 width=8) (actual time=0.470..0.473 rows=0 loops=1)
                Sort Key: sensor_values.ts DESC
                Buffers: shared hit=94
                ->  Index Only Scan Backward using sensor_values_ts_sensor_unq on sensor_values sensor_values_1  (cost=0.12..8.15 rows=1 width=8) (actual time=0.026..0.026 rows=0 loops=1)
                      Index Cond: ((sensor_id = 3502) AND (ts IS NOT NULL) AND (ts >= '2000-05-01 00:00:00+00'::timestamp with time zone) AND (ts <= '2023-02-01 00:00:00+00'::timestamp with time zone))
                      Heap Fetches: 0
                      Buffers: shared hit=2
                ->  Index Only Scan Backward using sensor_values_2017q1_ts_sensor_unq on sensor_values_2017q1 sensor_values_2  (cost=0.12..8.15 rows=1 width=8) (actual time=0.014..0.014 rows=0 loops=1)
                      Index Cond: ((sensor_id = 3502) AND (ts IS NOT NULL) AND (ts >= '2000-05-01 00:00:00+00'::timestamp with time zone) AND (ts <= '2023-02-01 00:00:00+00'::timestamp with time zone))
                      Heap Fetches: 0
                      Buffers: shared hit=2
                ->  Index Only Scan Backward using sensor_values_2017q2_ts_sensor_unq on sensor_values_2017q2 sensor_values_3  (cost=0.12..8.15 rows=1 width=8) (actual time=0.009..0.009 rows=0 loops=1)
                      Index Cond: ((sensor_id = 3502) AND (ts IS NOT NULL) AND (ts >= '2000-05-01 00:00:00+00'::timestamp with time zone) AND (ts <= '2023-02-01 00:00:00+00'::timestamp with time zone))
                      Heap Fetches: 0
                      Buffers: shared hit=2
                ->  Index Only Scan Backward using sensor_values_2017q3_ts_sensor_unq on sensor_values_2017q3 sensor_values_4  (cost=0.12..8.15 rows=1 width=8) (actual time=0.026..0.026 rows=0 loops=1)
                      Index Cond: ((sensor_id = 3502) AND (ts IS NOT NULL) AND (ts >= '2000-05-01 00:00:00+00'::timestamp with time zone) AND (ts <= '2023-02-01 00:00:00+00'::timestamp with time zone))
                      Heap Fetches: 0
                      Buffers: shared hit=2
                ->  Index Only Scan Backward using sensor_values_2017q4_ts_sensor_unq on sensor_values_2017q4 sensor_values_5  (cost=0.12..8.15 rows=1 width=8) (actual time=0.014..0.014 rows=0 loops=1)
                      Index Cond: ((sensor_id = 3502) AND (ts IS NOT NULL) AND (ts >= '2000-05-01 00:00:00+00'::timestamp with time zone) AND (ts <= '2023-02-01 00:00:00+00'::timestamp with time zone))
                      Heap Fetches: 0
                      Buffers: shared hit=2
                ->  Index Only Scan Backward using sensor_values_2018q1_ts_sensor_unq on sensor_values_2018q1 sensor_values_6  (cost=0.56..1034.86 rows=25532 width=8) (actual time=0.019..0.019 rows=0 loops=1)
                      Index Cond: ((sensor_id = 3502) AND (ts IS NOT NULL) AND (ts >= '2000-05-01 00:00:00+00'::timestamp with time zone) AND (ts <= '2023-02-01 00:00:00+00'::timestamp with time zone))
                      Heap Fetches: 0
                      Buffers: shared hit=4
                ->  Index Only Scan Backward using sensor_values_2018q2_ts_sensor_unq on sensor_values_2018q2 sensor_values_7  (cost=0.56..1012.04 rows=24939 width=8) (actual time=0.018..0.018 rows=0 loops=1)
                      Index Cond: ((sensor_id = 3502) AND (ts IS NOT NULL) AND (ts >= '2000-05-01 00:00:00+00'::timestamp with time zone) AND (ts <= '2023-02-01 00:00:00+00'::timestamp with time zone))
                      Heap Fetches: 0
                      Buffers: shared hit=4
                ->  Index Only Scan Backward using sensor_values_2018q3_ts_sensor_unq on sensor_values_2018q3 sensor_values_8  (cost=0.56..1068.14 rows=26383 width=8) (actual time=0.017..0.017 rows=0 loops=1)
                      Index Cond: ((sensor_id = 3502) AND (ts IS NOT NULL) AND (ts >= '2000-05-01 00:00:00+00'::timestamp with time zone) AND (ts <= '2023-02-01 00:00:00+00'::timestamp with time zone))
                      Heap Fetches: 0
                      Buffers: shared hit=4
                ->  Index Only Scan Backward using sensor_values_2018q4_ts_sensor_unq on sensor_values_2018q4 sensor_values_9  (cost=0.56..823.56 rows=20280 width=8) (actual time=0.020..0.020 rows=0 loops=1)
                      Index Cond: ((sensor_id = 3502) AND (ts IS NOT NULL) AND (ts >= '2000-05-01 00:00:00+00'::timestamp with time zone) AND (ts <= '2023-02-01 00:00:00+00'::timestamp with time zone))
                      Heap Fetches: 0
                      Buffers: shared hit=4
                ->  Index Only Scan Backward using sensor_values_2019q1_ts_sensor_unq on sensor_values_2019q1 sensor_values_10  (cost=0.56..959.59 rows=23641 width=8) (actual time=0.016..0.016 rows=0 loops=1)
                      Index Cond: ((sensor_id = 3502) AND (ts IS NOT NULL) AND (ts >= '2000-05-01 00:00:00+00'::timestamp with time zone) AND (ts <= '2023-02-01 00:00:00+00'::timestamp with time zone))
                      Heap Fetches: 0
                      Buffers: shared hit=4
                ->  Index Only Scan Backward using sensor_values_2019q2_ts_sensor_unq on sensor_values_2019q2 sensor_values_11  (cost=0.56..1012.89 rows=24973 width=8) (actual time=0.020..0.020 rows=0 loops=1)
                      Index Cond: ((sensor_id = 3502) AND (ts IS NOT NULL) AND (ts >= '2000-05-01 00:00:00+00'::timestamp with time zone) AND (ts <= '2023-02-01 00:00:00+00'::timestamp with time zone))
                      Heap Fetches: 0
                      Buffers: shared hit=4
                ->  Index Only Scan Backward using sensor_values_2019q3_ts_sensor_unq on sensor_values_2019q3 sensor_values_12  (cost=0.56..1025.59 rows=25321 width=8) (actual time=0.016..0.016 rows=0 loops=1)
                      Index Cond: ((sensor_id = 3502) AND (ts IS NOT NULL) AND (ts >= '2000-05-01 00:00:00+00'::timestamp with time zone) AND (ts <= '2023-02-01 00:00:00+00'::timestamp with time zone))
                      Heap Fetches: 0
                      Buffers: shared hit=4
                ->  Index Only Scan Backward using sensor_values_2019q4_ts_sensor_unq on sensor_values_2019q4 sensor_values_13  (cost=0.56..1057.36 rows=26112 width=8) (actual time=0.018..0.018 rows=0 loops=1)
                      Index Cond: ((sensor_id = 3502) AND (ts IS NOT NULL) AND (ts >= '2000-05-01 00:00:00+00'::timestamp with time zone) AND (ts <= '2023-02-01 00:00:00+00'::timestamp with time zone))
                      Heap Fetches: 0
                      Buffers: shared hit=4
                ->  Index Only Scan Backward using sensor_values_2020q1_ts_sensor_unq on sensor_values_2020q1 sensor_values_14  (cost=0.56..1048.59 rows=25921 width=8) (actual time=0.030..0.030 rows=0 loops=1)
                      Index Cond: ((sensor_id = 3502) AND (ts IS NOT NULL) AND (ts >= '2000-05-01 00:00:00+00'::timestamp with time zone) AND (ts <= '2023-02-01 00:00:00+00'::timestamp with time zone))
                      Heap Fetches: 0
                      Buffers: shared hit=4
                ->  Index Only Scan Backward using sensor_values_2020q2_ts_sensor_unq on sensor_values_2020q2 sensor_values_15  (cost=0.56..1044.04 rows=25739 width=8) (actual time=0.020..0.020 rows=0 loops=1)
                      Index Cond: ((sensor_id = 3502) AND (ts IS NOT NULL) AND (ts >= '2000-05-01 00:00:00+00'::timestamp with time zone) AND (ts <= '2023-02-01 00:00:00+00'::timestamp with time zone))
                      Heap Fetches: 0
                      Buffers: shared hit=4
                ->  Index Only Scan Backward using sensor_values_2020q3_ts_sensor_unq on sensor_values_2020q3 sensor_values_16  (cost=0.56..1616.09 rows=39981 width=8) (actual time=0.015..0.015 rows=0 loops=1)
                      Index Cond: ((sensor_id = 3502) AND (ts IS NOT NULL) AND (ts >= '2000-05-01 00:00:00+00'::timestamp with time zone) AND (ts <= '2023-02-01 00:00:00+00'::timestamp with time zone))
                      Heap Fetches: 0
                      Buffers: shared hit=4
                ->  Index Only Scan Backward using sensor_values_2020q4_ts_sensor_unq on sensor_values_2020q4 sensor_values_17  (cost=0.56..1717.97 rows=42456 width=8) (actual time=0.018..0.018 rows=0 loops=1)
                      Index Cond: ((sensor_id = 3502) AND (ts IS NOT NULL) AND (ts >= '2000-05-01 00:00:00+00'::timestamp with time zone) AND (ts <= '2023-02-01 00:00:00+00'::timestamp with time zone))
                      Heap Fetches: 0
                      Buffers: shared hit=4
                ->  Index Only Scan Backward using sensor_values_2021q1_ts_sensor_unq on sensor_values_2021q1 sensor_values_18  (cost=0.57..1729.19 rows=42745 width=8) (actual time=0.017..0.017 rows=0 loops=1)
                      Index Cond: ((sensor_id = 3502) AND (ts IS NOT NULL) AND (ts >= '2000-05-01 00:00:00+00'::timestamp with time zone) AND (ts <= '2023-02-01 00:00:00+00'::timestamp with time zone))
                      Heap Fetches: 0
                      Buffers: shared hit=4
                ->  Index Only Scan Backward using sensor_values_2021q2_ts_sensor_unq on sensor_values_2021q2 sensor_values_19  (cost=0.57..1621.57 rows=40040 width=8) (actual time=0.015..0.015 rows=0 loops=1)
                      Index Cond: ((sensor_id = 3502) AND (ts IS NOT NULL) AND (ts >= '2000-05-01 00:00:00+00'::timestamp with time zone) AND (ts <= '2023-02-01 00:00:00+00'::timestamp with time zone))
                      Heap Fetches: 0
                      Buffers: shared hit=4
                ->  Index Only Scan Backward using sensor_values_2021q3_ts_sensor_unq on sensor_values_2021q3 sensor_values_20  (cost=0.57..1620.47 rows=39996 width=8) (actual time=0.018..0.019 rows=0 loops=1)
                      Index Cond: ((sensor_id = 3502) AND (ts IS NOT NULL) AND (ts >= '2000-05-01 00:00:00+00'::timestamp with time zone) AND (ts <= '2023-02-01 00:00:00+00'::timestamp with time zone))
                      Heap Fetches: 0
                      Buffers: shared hit=4
                ->  Index Only Scan Backward using sensor_values_2021q4_ts_sensor_unq on sensor_values_2021q4 sensor_values_21  (cost=0.57..1739.69 rows=43005 width=8) (actual time=0.014..0.014 rows=0 loops=1)
                      Index Cond: ((sensor_id = 3502) AND (ts IS NOT NULL) AND (ts >= '2000-05-01 00:00:00+00'::timestamp with time zone) AND (ts <= '2023-02-01 00:00:00+00'::timestamp with time zone))
                      Heap Fetches: 0
                      Buffers: shared hit=4
                ->  Index Only Scan Backward using sensor_values_2022q1_ts_sensor_unq on sensor_values_2022q1 sensor_values_22  (cost=0.56..883.49 rows=21717 width=8) (actual time=0.018..0.018 rows=0 loops=1)
                      Index Cond: ((sensor_id = 3502) AND (ts IS NOT NULL) AND (ts >= '2000-05-01 00:00:00+00'::timestamp with time zone) AND (ts <= '2023-02-01 00:00:00+00'::timestamp with time zone))
                      Heap Fetches: 0
                      Buffers: shared hit=4
                ->  Index Only Scan Backward using sensor_values_2022q2_ts_sensor_unq on sensor_values_2022q2 sensor_values_23  (cost=0.56..934.06 rows=22780 width=8) (actual time=0.020..0.020 rows=0 loops=1)
                      Index Cond: ((sensor_id = 3502) AND (ts IS NOT NULL) AND (ts >= '2000-05-01 00:00:00+00'::timestamp with time zone) AND (ts <= '2023-02-01 00:00:00+00'::timestamp with time zone))
                      Heap Fetches: 0
                      Buffers: shared hit=4
                ->  Index Only Scan Backward using sensor_values_2022q3_ts_sensor_unq on sensor_values_2022q3 sensor_values_24  (cost=0.56..1295.89 rows=31973 width=8) (actual time=0.016..0.016 rows=0 loops=1)
                      Index Cond: ((sensor_id = 3502) AND (ts IS NOT NULL) AND (ts >= '2000-05-01 00:00:00+00'::timestamp with time zone) AND (ts <= '2023-02-01 00:00:00+00'::timestamp with time zone))
                      Heap Fetches: 0
                      Buffers: shared hit=4
                ->  Index Only Scan Backward using sensor_values_2022q4_ts_sensor_unq on sensor_values_2022q4 sensor_values_25  (cost=0.56..901.81 rows=22290 width=8) (actual time=0.016..0.016 rows=0 loops=1)
                      Index Cond: ((sensor_id = 3502) AND (ts IS NOT NULL) AND (ts >= '2000-05-01 00:00:00+00'::timestamp with time zone) AND (ts <= '2023-02-01 00:00:00+00'::timestamp with time zone))
                      Heap Fetches: 0
                      Buffers: shared hit=4
                ->  Index Only Scan Backward using sensor_values_2023q1_ts_sensor_unq on sensor_values_2023q1 sensor_values_26  (cost=0.56..322.31 rows=7910 width=8) (actual time=0.016..0.016 rows=0 loops=1)
                      Index Cond: ((sensor_id = 3502) AND (ts IS NOT NULL) AND (ts >= '2000-05-01 00:00:00+00'::timestamp with time zone) AND (ts <= '2023-02-01 00:00:00+00'::timestamp with time zone))
                      Heap Fetches: 0
                      Buffers: shared hit=4
Planning:
  Buffers: shared hit=648
Planning Time: 7.898 ms
Execution Time: 0.767 ms

不知道为什么命令加快了你的注意。

postgresql

1条答案

按热度按时间
hmmo2u0o

hmmo2u0o1#

只需改变唯一约束，首先是sensor_id，然后是时间戳。这使得数据库可以首先执行等式，然后搜索最后一个时间戳。它应该是索引中的一个元组。
请分享新的查询计划，以显示有哪些变化以及如何提高性能。

赞(0）分享  回复(0）举报  2023-10-18
我来回答

相关问题

    查看更多

    热门标签

    更多
    JavaquerypythonNode开发语言requestUtil数据库Table后端算法LoggerMessageElementParser

    最新问答

    更多
    • 蜀ICP备2022004421号-2 NULL123 https://www.null123.com © All rights reserved
    • 本站内容来源互联网,如果侵犯您的权益请联系我们删除, 联系方式：448109455@qq.com