个人中心
文章发布
退出
作者热门文章
- html - 出于某种原因，IE8 对我的 Sass 文件中继承的 html5 CSS 不友好？
- JMeter 在响应断言中使用 span 标签的问题
- html - 在 :hover and :active? 上具有不同效果的 CSS 动画
- html - 相对于居中的 html 内容固定的 CSS 重复背景？
25
4
我正在尝试减少下面给出的查询的查询执行时间。它连接 3 个表以从非常大的 Postgres 表中获取数据，我试图在相关表上引入所有必要的索引，但查询仍然花费了太长时间。数据库的总大小约为 2TB。查询:
explain (ANALYZE, COSTS, VERBOSE, BUFFERS)
with au as (
select tbl2.client, tbl2.uid
from tbl2 where tbl2.client = '123kkjk444kjkhj3ddd'
and (tbl2.property->>'num') IN ('1', '2', '3', '31', '12a', '45', '78', '99')
)
SELECT tbl1.id,
CASE WHEN tbl3.displayname IS NOT NULL THEN tbl3.displayname ELSE tbl1.name END AS name,
tbl1.tbl3number, tbl3.originalname as orgtbl3
FROM table_1 tbl1
inner JOIN au tbl2 ON tbl2.client = '123kkjk444kjkhj3ddd' AND tbl2.uid = tbl1.uid
LEFT JOIN tbl3 ON tbl3.client = '123kkjk444kjkhj3ddd' AND tbl3.originalname = tbl1.name
WHERE tbl1.client = '123kkjk444kjkhj3ddd'
AND tbl1.date_col BETWEEN '2021-08-01T05:32:40Z' AND '2021-08-29T05:32:40Z'
ORDER BY tbl1.date_col DESC, tbl1.sid, tbl1.tbl3number
LIMIT 50000;
我正在运行上述查询，但即使在索引扫描之后查询执行也非常慢。我附上了查询计划。查询计划:
-> Limit (cost=7272.83..7272.86 rows=14 width=158) (actual time=40004.140..40055.737 rows=871 loops=1)
Output: tbl1.id, (CASE WHEN (tbl3.displayname IS NOT NULL) THEN tbl3.displayname ELSE tbl1.name END), tbl1.tbl3number, tbl3.originalsc
reenname, tbl1.date_col
Buffers: shared hit=249656881 dirtied=32
-> Sort (cost=7272.83..7272.86 rows=14 width=158) (actual time=40004.139..40055.671 rows=871 loops=1)
Output: tbl1.id, (CASE WHEN (tbl3.displayname IS NOT NULL) THEN tbl3.displayname ELSE tbl1.name END), tbl1.tbl3number, tbl3.orig
inalname, tbl1.date_col
Sort Key: tbl1.date_col DESC, tbl1.id, tbl1.tbl3number
Sort Method: quicksort Memory: 142kB
Buffers: shared hit=249656881 dirtied=32
-> Gather (cost=1001.39..7272.56 rows=14 width=158) (actual time=9147.574..40055.005 rows=871 loops=1)
Output: tbl1.id, (CASE WHEN (tbl3.displayname IS NOT NULL) THEN tbl3.displayname ELSE tbl1.name END), tbl1.tbl3number, scree
n.originalname, tbl1.date_col
Workers Planned: 4
Workers Launched: 4
Buffers: shared hit=249656881 dirtied=32
-> Nested Loop Left Join (cost=1.39..6271.16 rows=4 width=158) (actual time=3890.074..39998.436 rows=174 loops=5)
Output: tbl1.id, CASE WHEN (tbl3.displayname IS NOT NULL) THEN tbl3.displayname ELSE tbl1.name END, tbl1.tbl3number, s
creen.originalname, tbl1.date_col
Inner Unique: true
Buffers: shared hit=249656881 dirtied=32
Worker 0: actual time=1844.246..39996.744 rows=182 loops=1
Buffers: shared hit=49568277 dirtied=5
Worker 1: actual time=3569.032..39997.124 rows=210 loops=1
Buffers: shared hit=49968461 dirtied=10
Worker 2: actual time=2444.911..39997.561 rows=197 loops=1
Buffers: shared hit=49991521 dirtied=2
Worker 3: actual time=2445.013..39998.065 rows=110 loops=1
Buffers: shared hit=49670445 dirtied=10
-> Nested Loop (cost=1.12..6269.94 rows=4 width=610) (actual time=3890.035..39997.924 rows=174 loops=5)
Output: tbl1.id, tbl1.name, tbl1.tbl3number, tbl1.date_col
Inner Unique: true
Buffers: shared hit=249655135 dirtied=32
Worker 0: actual time=1844.200..39996.206 rows=182 loops=1
Buffers: shared hit=49567912 dirtied=5
Worker 1: actual time=3568.980..39996.522 rows=210 loops=1
Buffers: shared hit=49968040 dirtied=10
Worker 2: actual time=2444.872..39996.987 rows=197 loops=1
Buffers: shared hit=49991126 dirtied=2
Worker 3: actual time=2444.965..39997.712 rows=110 loops=1
Buffers: shared hit=49670224 dirtied=10
-> Parallel Index Only Scan using idx_sv_cuf8_110523 on public.table_1_110523 tbl1 (cost=0.69..5692.16 rows=220 width=692) (actual time=0.059..1458.129 rows=2922506 loops=5)
Output: tbl1.client, tbl1.id, tbl1.tbl3number, tbl1.date_col, tbl1.id, tbl1.name
Index Cond: ((tbl1.client = '123kkjk444kjkhj3ddd'::text) AND (tbl1.date_col >= '2021-08-01 05:32:40+00'::timestamp with time zone) AND (tbl1.date_col <= '2021-08-29 05:32:40+00'::timestamp with time zone))
Heap Fetches: 0
Buffers: shared hit=538663
Worker 0: actual time=0.059..1479.907 rows=2912875 loops=1
Buffers: shared hit=107477
Worker 1: actual time=0.100..1475.863 rows=2930306 loops=1
Buffers: shared hit=107817
Worker 2: actual time=0.054..1481.032 rows=2925849 loops=1
Buffers: shared hit=107812
Worker 3: actual time=0.058..1477.443 rows=2897544 loops=1
Buffers: shared hit=107047
-> Index Scan using tbl2_pkey_102328 on public.tbl2_102328 tbl2_1 (cost=0.43..2.63 rows=1 width=25) (actual time=0.013..0.013 rows=0 loops=14612531)
Output: tbl2_1.id
Index Cond: (((tbl2_1.id)::text = (tbl1.id)::text) AND ((tbl2_1.client)::text = '123kkjk444kjkhj3ddd'::text))
Filter: ((tbl2_1.property ->> 'num'::text) = ANY ('{"1","2","3","31","12a","45","78","99"}'::text[]))
Rows Removed by Filter: 1
Buffers: shared hit=249116472 dirtied=32
Worker 0: actual time=0.013..0.013 rows=0 loops=2912875
Buffers: shared hit=49460435 dirtied=5
Worker 1: actual time=0.013..0.013 rows=0 loops=2930306
Buffers: shared hit=49860223 dirtied=10
Worker 2: actual time=0.013..0.013 rows=0 loops=2925849
Buffers: shared hit=49883314 dirtied=2
Worker 3: actual time=0.013..0.013 rows=0 loops=2897544
Buffers: shared hit=49563177 dirtied=10
-> Index Scan using tbl3_unikey_104219 on public.tbl3_104219 tbl3 (cost=0.27..0.30 rows=1 width=52) (actual time=0.002..0.002 rows=0 loops=871)
Output: tbl3.client, tbl3.originalname, tbl3.displayname
Index Cond: (((tbl3.client)::text = '123kkjk444kjkhj3ddd'::text) AND ((tbl3.originalname)::text = (tbl1.name)::text))
Buffers: shared hit=1746
Worker 0: actual time=0.002..0.002 rows=0 loops=182
Buffers: shared hit=365
Worker 1: actual time=0.002..0.002 rows=0 loops=210
Buffers: shared hit=421
Worker 2: actual time=0.002..0.002 rows=0 loops=197
Buffers: shared hit=395
Worker 3: actual time=0.002..0.002 rows=0 loops=110
Buffers: shared hit=221
Planning Time: 0.361 ms
Execution Time: 40056.008 ms
Planning Time: 0.589 ms
Execution Time: 40071.485 ms
(89 rows)
Time: 40072.986 ms (00:40.073)
是否可以进一步优化此查询以减少查询执行时间？预先感谢您的输入。
表定义如下:
Table "public.tbl1"
Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
-------------------+-----------------------------+-----------+----------+---------+----------+--------------+-------------
client | character varying(32) | | not null | | extended | |
sid | character varying(32) | | not null | | extended | |
uid | character varying(32) | | | | extended | |
id | character varying(32) | | | | extended | |
tbl3number | integer | | not null | | plain | |
name | character varying(255) | | | | extended | |
date_col | timestamp without time zone | | | | plain | |
Indexes:
idx_sv_cuf8_110523(client,date_col desc,sid,tbl3number)
Table "public.tbl2"
Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
--------------------------+-----------------------------+-----------+----------+-------------------------+----------+--------------+-------------
id | character varying(32) | | not null | | extended | |
uid | character varying(255) | | | NULL::character varying | extended | |
client | character varying(32) | | not null | | extended | |
property | jsonb | | | | extended | |
Indexes:
"tbl2_pkey" PRIMARY KEY, btree (uid, client)
--
Table "public.tbl3"
Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
--------------------+------------------------+-----------+----------+---------+----------+--------------+-------------
client | character varying(500) | | not null | | extended | |
originalname | character varying(500) | | | | extended | |
displayname | character varying(500) | | | | extended | |
Indexes:
"tbl3_unikey" UNIQUE CONSTRAINT, btree (client, originalname)
最佳答案
tl;博士:Multicolumn covering indexes .
我更喜欢使用严格的查询格式，这样更容易看到正在处理的列和表。出于同样的原因，我删除了您的 CTE 并将其条件移至主查询。我还删除了多个相同的客户端 ID 常量。这是我的重写。
SELECT tbl1.id,
COALESCE(tbl3.displayname, tbl1.name) AS name,
tbl1.tbl3number,
tbl3.originalname as orgtbl3
FROM table_1 tbl1
INNER JOIN tbl2
ON tbl2.client = tbl1.client
AND tbl2.uid = tbl1.uid
AND (tbl2.property->>'num') IN ('1', '2', '3', '31', '12a', '45', '78', '99')
LEFT JOIN tbl3
ON tbl3.client = tbl1.client
AND tbl3.originalname = tbl1.name
WHERE tbl1.client = '123kkjk444kjkhj3ddd'
AND tbl1.date_col BETWEEN '2021-08-01T05:32:40Z' AND '2021-08-29T05:32:40Z'
ORDER BY tbl1.date_col DESC, tbl1.sid, tbl1.tbl3number
LIMIT 50000;
当你 ORDER BY 然后 LIMIT 时，你有时会迫使服务器做大量的数据洗牌:对你的结果集进行排序，然后丢弃其中的一些。您能否避免 ORDER BY 或 LIMIT，或两者兼而有之？
对于按 DESC 排序的列的索引，使用 DESC 关键字也可能有所帮助。
这是一个很大的查询。但我相信 multicolumn covering indexes 的明智选择将有助于加快速度。
您通过对client 的持续比较和对date_col 的范围扫描来过滤tbl。然后使用 uid 并输出 id、name 和 tbl3number。因此，此 BTREE 索引将允许仅索引范围扫描，这通常很快。 (注意 date_col 上的 DESC 关键字。它试图帮助您的 ORDER BY 子句。)
CREATE INDEX CONCURRENTLY tbl1_name_num_lookup
ON tbl1 (client, date_col DESC)
INCLUDE (uid, id, name, tbl3number);
从tbl2，您访问client 和uid，然后使用jsonb 列属性。因此，该索引可能会对您有所帮助。
CREATE INDEX CONCURRENTLY tbl2_name_num_lookup
ON tbl2 (client, uid)
INCLUDE (property);
在 tbl3 中，您可以通过 client 和 originalname 访问它。您输出 displayname。所以这个索引应该有所帮助。
CREATE INDEX CONCURRENTLY tbl3_name_num_lookup
ON tbl3 (client, originalname)
INCLUDE (displayname);
您加入 ON tbl2.uid = tbl1.uid。但是这两列具有不同的数据类型:tbl1 中的 character varying(32) 和 tbl2 中的 255。当 ON 列具有相同的数据类型时，JOIN 操作会更快。考虑修复一个或另一个。
ON tbl3.originalname = tbl1.name 也是如此。
关于postgresql - 大表上的 Postgres 慢查询，我们在Stack Overflow上找到一个类似的问题： https://stackoverflow.com/questions/69191399/
25
4
0
我有三张 table 。表 A 有选项名称(即颜色、尺寸)。表 B 有选项值名称(即蓝色、红色、黑色等)。表C通过将选项名称id和选项名称值id放在一起来建立关系。 我的查询需要显示值和选项的名称，而
在mysql中，如何计算一行中的非空单元格？我只想计算某些列之间的单元格，比如第 3-10 列之间的单元格。不是所有的列...同样，仅在该行中。 最佳答案 如果你想这样做，只能在 sql 中使用名称而
关闭。这个问题需要多问focused 。目前不接受答案。 想要改进此问题吗？更新问题，使其仅关注一个问题 editing this post . 已关闭 7 年前。 Improve this ques
我正在为版本7.6进行Elasticsearch查询 我的查询是这样的: { "query": { "bool": { "should": [ {
关闭。这个问题需要多问focused 。目前不接受答案。 想要改进此问题吗？更新问题，使其仅关注一个问题 editing this post . 已关闭 7 年前。 Improve this ques
是否可以编写一个查询来检查任一子查询(而不是一个子查询)是否正确？ SELECT * FROM employees e WHERE NOT EXISTS (
我找到了很多关于我的问题的答案，但问题没有解决 我有表格，有数据，例如: Data 1 Data 2 Data 3
以下查询返回错误: 查询: SELECT Id, FirstName, LastName, OwnerId, PersonEmail FROM Account WHERE lower(PersonEm
以下查询返回错误: 查询: SELECT Id, FirstName, LastName, OwnerId, PersonEmail FROM Account WHERE lower(PersonEm
我从 EditText 中获取了 String 值。以及提交查询的按钮。 String sql=editQuery.getText().toString();// SELECT * FROM empl
我有一个或多或少有效的查询(关于结果)，但处理大约需要 45 秒。这对于在 GUI 中呈现数据来说肯定太长了。 所以我的需求是找到一个更快/更高效的查询(几毫秒左右会很好)我的数据表大约有 3000
这是我第一次使用 Stack Overflow，所以我希望我以正确的方式提出这个问题。 我有 2 个 SQL 查询，我正在尝试比较和识别缺失值，尽管我无法将 NULL 字段添加到第二个查询中以识别缺失
什么是动态 SQL 查询？何时需要使用动态 SQL 查询？我使用的是 SQL Server 2005。 最佳答案 这里有几篇文章: Introduction to Dynamic SQL Dynami
include "mysql.php"; $query= "SELECT ID,name,displayname,established,summary,searchlink,im
我有一个查询要“转换”为 mysql。这是查询: select top 5 * from (select id, firstName, lastName, sum(fileSize) as To
通过我的研究，我发现至少从 EF 4.1 开始，EF 查询上的 .ToString() 方法将返回要运行的 SQL。事实上，这对我来说非常有用，使用 Entity Framework 5 和 6。 但
我在构造查询来执行以下操作时遇到问题: 按activity_type_id过滤联系人，仅显示最近事件具有所需activity_type_id或为NULL(无事件)的联系人 表格结构如下: 一个联系人可
如何让我输入数据库的信息在输入数据 5 分钟后自行更新？ 假设我有一张 table : +--+--+-----+ |id|ip|count| +--+--+-----+ |
我正在尝试搜索正好是 4 位数字的 ID，我知道我需要使用 LENGTH() 字符串函数，但找不到如何使用它的示例。我正在尝试以下(和其他变体)但它们不起作用。 SELECT max(car_id)
我有一个在 mysql 上运行良好的 sql 查询(查询 + 连接): select sum(pa.price) from user u , purchase pu , pack pa where (
我是一名优秀的程序员,十分优秀！