时间戳

首页 > 解决方案 > 需要减少postgres中的查询优化时间

postgresql - 需要减少postgres中的查询优化时间

问题描述

用例：需要在表中查找特定id的index和totalCount

我有一个表 ann_details 有 6000 万条记录，并根据条件我需要检索行以及该 ID 的索引

询问：

with a as (
    select an.id, row_number() over (partition by created_at) as rn
    from annotation an
    where ( an.layer_id = '47afb169-aed2-4378-ab13-897836275da3' or an.job_id = '' or an.task_id = '') and
     an.category_id in (10019)

) select (select count(1) from a ) as totalCount , rn-1 as index from a where a.id= '47afb169-aed2-4378-ab13-897836275da3_a93f0758-8fe0-4c76-992f-0be17e5618bf_484484101';

输出：

totalCount index
1797124,1791143

执行时间：5 秒 487 毫秒

解释和分析

CTE Scan on a  (cost=872778.54..907545.00 rows=7722 width=16) (actual time=5734.572..5735.989 rows=1 loops=1)
  Filter: ((id)::text = '47afb169-aed2-4378-ab13-897836275da3_a93f0758-8fe0-4c76-992f-0be17e5618bf_484484101'::text)
  Rows Removed by Filter: 1797123
  CTE a
    ->  WindowAgg  (cost=0.68..838031.38 rows=1544318 width=97) (actual time=133.660..3831.998 rows=1797124 loops=1)
          ->  Index Only Scan using test_index_test_2 on annotation an  (cost=0.68..814866.61 rows=1544318 width=89) (actual time=133.647..2660.009 rows=1797124 loops=1)
                Index Cond: (category_id = 10019)
                Filter: (((layer_id)::text = '47afb169-aed2-4378-ab13-897836275da3'::text) OR ((job_id)::text = ''::text) OR ((task_id)::text = ''::text))
                Rows Removed by Filter: 3773007
                Heap Fetches: 101650
  InitPlan 2 (returns $1)
    ->  Aggregate  (cost=34747.15..34747.17 rows=1 width=8) (actual time=2397.391..2397.392 rows=1 loops=1)
          ->  CTE Scan on a a_1  (cost=0.00..30886.36 rows=1544318 width=0) (actual time=0.017..2156.210 rows=1797124 loops=1)
Planning time: 0.487 ms
Execution time: 5771.080 ms

指数：

CREATE INDEX test_index_test_2 ON public.annotation USING btree (category_id,created_at,layer_id,job_id,task_id,id);

从应用程序中，我们将传递 job_id 或 task_id 或 layer_id，其余 2 将作为空传递

需要帮助优化查询以在 2 秒内获得响应

查询计划：https ://explain.depesz.com/s/mXme

标签： postgresqlquery-optimizationquery-performancepostgresql-10

解决方案

推荐阅读