postgresql SQL查询的顺序和限制需要花费大量时间

fae0ux8s 于 2023-06-22 发布在 PostgreSQL

关注(0)|答案(2)|浏览(192)

我正在查询一个包含两种skuID的约4M数据的表，并使用order by on version，因为对于一个skuID，有大约5k个版本的数据，并使用limit获取最高版本。

查询：

select * FROM table rb 
    WHERE rb.sku_id='' or rb.package_sku_id=''
    order by version desc
limit 1;

通过对查询使用explain，我们发现ORDER BY和LIMIT占用了查询的大部分成本：

Limit  (cost=0.43..5304.64 rows=1 width=861) (actual time=50327.036..50327.041 rows=1 loops=1)
  Buffers: shared hit=361280 read=104302 written=18
  I/O Timings: read=40363.693 write=0.215
  ->  Index Scan Backward using "IDX488yr43nr28a1yml9lb5i7jfv" on referral_benefits rb  (cost=0.43..9552890.48 rows=1801 width=861) (actual time=50327.028..50327.028 rows=1 loops=1)
        Filter: (((sku_id)::text = 'b1d5fa77-fda8-466c-b8b0-a8e60d1f78a5'::text) OR ((package_sku_id)::text = 'b1d5fa77-fda8-466c-b8b0-a8e60d1f78a5'::text))
        Rows Removed by Filter: 1361027
        Buffers: shared hit=361280 read=104302 written=18
        I/O Timings: read=40363.693 write=0.215
Planning Time: 1.121 ms
Execution Time: 50329.843 ms

我们已经在skuId和package_sku_id上添加了索引，但这并没有减少时间。
在这方面需要指导。先谢谢你了

postgresql

来源：https://stackoverflow.com/questions/76406408/sql-query-order-by-and-limit-taking-a-lot-of-time

2条答案

按热度按时间

myzjeezk1#

如果你有两个索引，一个在(sku_id, version)上，一个在(package_sku_id, version)上，那么你可以通过执行以下操作来获得非常快的执行速度：

(select * FROM rb WHERE rb.sku_id='b1d5fa77-fda8-466c-b8b0-a8e60d1f78a5' order by version desc) 
    union all
(select * FROM rb WHERE rb.package_sku_id='b1d5fa77-fda8-466c-b8b0-a8e60d1f78a5' order by version desc)
order by version desc limit 1;

但请注意，如果将LIMIT增加到1以上，则如果同一行满足两个SKU条件，则此查询可能会返回重复的行。

赞(0）回复(0）举报 2023-06-22

txu3uszq2#

PostgreSQL提供了部分索引，即只应用于表的子集的索引。请使用该索引类型，并使其仅包含要查看行。然后DBMS只需要读取该索引，数据就会排序出来：

create index idx on mytable (version desc)
                 where sku_id = '' or package_sku_id = '';

另一个选项是只包含所需顶行的单独表。编写一个触发器来保持更新。

赞(0）回复(0）举报 2023-06-22

我来回答

postgresql SQL查询的顺序和限制需要花费大量时间

2条答案

相关问题

热门标签

最新问答