logo标签列表

Postgres大表慢查询

postgresqljoinbigdataquery-optimizationcovering-index
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)
开始对查询进行清理。 （为什么文本字面值“123kkjk444kjkhj3ddd”重复三次？） - wildplasser
1
我们需要您的表定义来帮助您。请阅读此内容，然后[编辑]您的问题。另外，有一个简短的陈述会很有帮助：有时从查询中反向工程意图是很困难的。 - O. Jones
@wildplasser，我们有一个多租户数据库模式，需要在每个查询和连接中使用租户ID。上面的文本字面值“123kkjk444kjkhj3ddd”用于连接三个不同的表格。对于任何使用SQL查询的人来说，这是显而易见的。 - TechnoBasant
@wildplasser 是的，ID列大多数是主键，而且是的，可以使用coalesce代替case when。然而，在我们的情况下，耗时的部分是在tbl1上进行索引扫描和tbl2上的连接循环中。 - TechnoBasant
为什么您在所有键字段中使用 varchar(32) 字段？为什么没有PK/FK约束？您使用ORM吗？ - wildplasser
显示剩余2条评论
3个回答
3

简而言之：使用多列覆盖索引。

查询清晰度

我喜欢使用统一的格式来编写查询，这样更容易看出正在处理的列和表。出于同样的原因，我删除了你的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时，有时会迫使服务器进行大量的数据重排：对结果集进行排序，然后舍弃其中某些部分。是否可以避免使用ORDER BY或LIMIT，或者两者都避免?

在所按降序排序的列的索引上使用DESC关键字也可能有所帮助。

覆盖索引

这是一个大查询。 但我相信明智地选择多列覆盖索引将有助于加快查询速度。

您通过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 列 property。 因此，这个索引很可能会对你有帮助。

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。当进行JOIN操作时，ON列拥有相同的数据类型可以提高速度。建议修复其中一个表的列以与另一个表匹配。

ON tbl3.originalname = tbl1.name同样存在相同问题。

谢谢您的回复，我们已经尝试了您在这里提出的所有建议和要点。包括使用覆盖索引以及在date_col上使用DESC等。即使是仅索引扫描也没有帮助。 - TechnoBasant
您的计划显示在tbl2上进行了一次相当便宜的PK索引扫描，循环了1460万次。将CTE的条件移动到主查询可能会解决这个问题。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) - O. Jones
1
您将两个不同的事物都别名为tbl2，这肯定不利于计划的可读性。计划过度缩进，需要左右滚动才能看到，这也没有帮助。
为什么您的计划显示(tbl2_1.id)::text = (tbl1.id)::text，而您的查询显示tbl2.uid = tbl1.uid？这是一种错误的匿名化吗？
基本上所有时间都花在了tbl1和tbl2之间的连接上，所以这是您需要优化的地方。如果您消除了对tbl3的连接，那么就可以简化EXPLAIN，使其更容易理解。
您正在访问tbl2 1400万次，但只获取到174行。但我们无法确定索引是否会为每个1400万个输入找到一行，并被过滤掉，或者平均找到0行。也许反转该连接的顺序会更有效，您可以通过创建索引on tbl2 (client, (property->>'num'),uid)来实现这一点。或者也许是"id"而不是"uid"，我不太清楚您真正的查询是什么。
感谢您的建议，我们在tbl2中的属性col是jsonb类型，并且有多个这样的属性，使得在jsonb列上进行索引考虑到内存占用变得困难。除此之外，Postgres对索引元组大小有限制，这不允许在属性列上创建任何索引。 - TechnoBasant
-1

你的第一个查询使用JSON并在JSON结构内部操作过滤器（约束条件）来查找数据：

tbl2.property->>'num'

WHERE谓词的这一部分不是“可搜索的”。因此，回答您的需求的唯一方法是扫描表tbl2中的每一行，然后对于每一行扫描JSON文本流以查找所需的值。

因此，迭代是表的行基数和JSON部分之间的一种交叉乘积。

没有办法优化这样的查询...

每次您将一个具有迭代行为的对象（在您的查询中为JSON）引入可以使用基于集合的算法（索引、并行处理等）检索的数据集中时，结果就是扫描、扫描和扫描...

实际上，JSON无法建立索引。与DB2、Oracle或SQL Server能够在XML上创建专门的索引不同，PostgreSQL不接受JSON索引或XML索引。

3
注意：jsonb可以被索引。https://www.postgresql.org/docs/current/datatype-json.html - wildplasser
不适用于 --> 运算符... 对于所有其他的查找谓词，效率通常都非常低。 - SQLpro
3
col ->> 'num' 可以轻松高效地进行索引。 - Laurenz Albe
网页内容由stack overflow 提供, 点击上面的
可以查看英文原文，
原文链接