PostgreSQL 改进查询

发布于 2025-01-10 05:28:37 字数 1323 浏览 0 评论 0原文

我有一个查询如下：

SELECT MAX(c."Sequence") 
FROM "Cips" AS c 
WHERE c."StoreNumber" IN (1, 2) 
AND c."DataProvider" in ('MCIP'  , 'SAM')

我还运行 explain analysis 并收到以下答案：

"Aggregate  (cost=43628.91..43628.92 rows=1 width=8) (actual time=81.290..81.292 rows=1 loops=1)"
"  ->  Append  (cost=0.43..43498.29 rows=52248 width=8) (actual time=0.090..75.045 rows=61163 loops=1)"
"        ->  Index Scan using ""a_StoreNumber_DataProvider_idx"" on a c  (cost=0.43..43237.05 rows=52248 width=8) (actual time=0.089..67.541 rows=61163 loops=1)"
"              Index Cond: ((""StoreNumber"" = ANY ('{-1,1}'::integer[])) AND (""DataProvider"" = ANY ('{MCIP,SAM}'::text[])))"
"Planning Time: 0.677 ms"
"Execution Time: 81.366 ms"

我在此表上仅定义了一个索引：

CREATE INDEX "idx_Cip_StoreNumber_Sequence_DataProvider"
    ON public."Cips" USING btree
    ("StoreNumber" ASC NULLS LAST, "Sequence" ASC NULLS LAST, "DataProvider" COLLATE pg_catalog."default" ASC NULLS LAST)
    TABLESPACE pg_default;

我的表行数约为 140 万行。我还尝试对表进行分区，但性能提高了 10%。我的问题是我需要将性能提高 90%，但我确实陷入了困境。

我想知道是否可以提高此查询的性能，或者我应该开始寻找其他方向，例如修改该字段周围的架构以及如何获得最大值？

原文

I have a query as follows:

SELECT MAX(c."Sequence") 
FROM "Cips" AS c 
WHERE c."StoreNumber" IN (1, 2) 
AND c."DataProvider" in ('MCIP'  , 'SAM')

I also run the explain analyse and I receive the following answer:

"Aggregate  (cost=43628.91..43628.92 rows=1 width=8) (actual time=81.290..81.292 rows=1 loops=1)"
"  ->  Append  (cost=0.43..43498.29 rows=52248 width=8) (actual time=0.090..75.045 rows=61163 loops=1)"
"        ->  Index Scan using ""a_StoreNumber_DataProvider_idx"" on a c  (cost=0.43..43237.05 rows=52248 width=8) (actual time=0.089..67.541 rows=61163 loops=1)"
"              Index Cond: ((""StoreNumber"" = ANY ('{-1,1}'::integer[])) AND (""DataProvider"" = ANY ('{MCIP,SAM}'::text[])))"
"Planning Time: 0.677 ms"
"Execution Time: 81.366 ms"

I have only one index defined on this table:

CREATE INDEX "idx_Cip_StoreNumber_Sequence_DataProvider"
    ON public."Cips" USING btree
    ("StoreNumber" ASC NULLS LAST, "Sequence" ASC NULLS LAST, "DataProvider" COLLATE pg_catalog."default" ASC NULLS LAST)
    TABLESPACE pg_default;

My table row count is somewhere around 1.4 mil rows. I also tried to partition the table, but with 10% better performance. My problem is that I need to improve the performance by 90% and I really am stuck.

I was wondering if I can improve the performance of this query or should I start to look in some other direction, for example to modify the architecture around this field and how to we get the max value?

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

负佳期 2025-01-17 05:28:37

尝试不同的索引，其中的列按照您在该查询中需要的方式排序：

CREATE INDEX "idx_Cip_StoreNumber_DataProvider_Sequence"
    ON public."Cips" USING btree
    ("StoreNumber" ASC NULLS LAST, "DataProvider" COLLATE pg_catalog."default" ASC NULLS LAST, "Sequence" DESC NULLS LAST, )
    TABLESPACE pg_default;

序列现在是索引中的最后一列，并且也按降序排序，但最后是 NULL。

这会改善查询计划吗？请使用 EXPLAIN(ANALYZE, VERBOSE, BUFFERS) 来获取完整的计划。

Try a different index, where you have the columns ordered in the way you need them for this query:

CREATE INDEX "idx_Cip_StoreNumber_DataProvider_Sequence"
    ON public."Cips" USING btree
    ("StoreNumber" ASC NULLS LAST, "DataProvider" COLLATE pg_catalog."default" ASC NULLS LAST, "Sequence" DESC NULLS LAST, )
    TABLESPACE pg_default;

Sequence is now the last column in the index, and also sorted descending but NULLS last.

Does this improve the query plan? Please use EXPLAIN(ANALYZE, VERBOSE, BUFFERS) to get the complete plan.

回复收藏 0 原文

~没有更多了~