postgresql对每个表的第一次慢速查询

ruyhziif  于 2023-03-12  发布在  PostgreSQL
关注(0)|答案(1)|浏览(287)

问题

我有一个数据库，有多个大表，每个表有超过5亿条记录，每条记录非常简单，只有5个文本/浮点字段。我对表进行了分区，并准备了一个函数来查询表。但第一次查询总是比后面的查询慢10倍。所有表都是这样。

详情

下面的许多职位在这里，我尝试了以下：
下面是对查询过程的分析，在psql连接后，我做了第一个查询，然后立即使用不同的参数进行第二个查询：

postgres=> EXPLAIN (ANALYZE, BUFFERS) select * from myschema.myfunc(tableID,arg1, arg2) limit 1;
                                                             QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=0.25..0.26 rows=1 width=20) (actual time=4860.718..4860.718 rows=1 loops=1)
   Buffers: shared hit=4479 read=21
   I/O Timings: read=209.969
   ->  Function Scan on get_specific_point_data  (cost=0.25..0.35 rows=10 width=20) (actual time=4860.717..4860.717 rows=1 loops=1)
         Buffers: shared hit=4479 read=21
         I/O Timings: read=209.969
 Planning Time: 0.049 ms
 Execution Time: 4860.824 ms
(8 rows)

postgres=> EXPLAIN (ANALYZE, BUFFERS) select * from myschema.myfunc(tableID,arg3, arg4) limit 1;
                                                            QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=0.25..0.26 rows=1 width=20) (actual time=448.253..448.254 rows=1 loops=1)
   Buffers: shared hit=89 read=19
   I/O Timings: read=436.326
   ->  Function Scan on get_specific_point_data  (cost=0.25..0.35 rows=10 width=20) (actual time=448.252..448.252 rows=1 loops=1)
         Buffers: shared hit=89 read=19
         I/O Timings: read=436.326
 Planning Time: 0.051 ms
 Execution Time: 448.292 ms

配置

postgresql服务器部署在Azure上，配备2个vCores CPU和~ 600 Gb存储。假设它具有4Gb RAM。以下是一些配置：

name            | setting
----------------------------+---------
 autovacuum_work_mem        | -1
 dynamic_shared_memory_type | windows
 maintenance_work_mem       | 65536
 work_mem                   | 4096
 block_size                   | 8192
 effective_cache_size         | 393216  (?? units are not Kb? Azure portal say: "unit is 8kb"?
 log_rotation_size            | 102400
 max_wal_size                 | 1024
 min_parallel_index_scan_size | 64
 min_parallel_table_scan_size | 1024
 min_wal_size                 | 256
 segment_size                 | 131072
 track_activity_query_size    | 1024
 wal_block_size               | 8192
 wal_segment_size             | 16777216

我还打开了Azure共享库中的pg_prewarm插件，但不确定是否需要进一步的步骤/设置来激活它。
欢迎提出任何建议！

更新

根据@jjanes和@Zegarek关于隐藏计划信息的函数的评论，有一些额外的细节。在这里尝试prepare。我再次从新连接重新运行查询，第一次查询慢，第二次快10倍。

PREPARE                                                                              QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Unique  (cost=452763.67..467166.58 rows=200 width=20) (actual time=1.977..2.196 rows=1604 loops=1)
   ->  Sort  (cost=452763.67..459965.12 rows=2880582 width=20) (actual time=1.976..2.031 rows=1604 loops=1)
         Sort Key: values_table_r1_h0.date
         Sort Method: quicksort  Memory: 25kB
         ->  Nested Loop  (cost=273.05..25554.05 rows=2880582 width=20) (actual time=1.000..1.656 rows=1604 loops=1)
               ->  Limit  (cost=272.62..272.62 rows=1 width=12) (actual time=0.955..0.956 rows=1 loops=1)
                     ->  Sort  (cost=272.62..272.64 rows=9 width=12) (actual time=0.952..0.953 rows=1 loops=1)
                           Sort Key: ((abs((id_table.field1 - '96.7'::double precision)) + abs((id_table.field2 - '50.7'::double precision))))
                           Sort Method: quicksort  Memory: 25kB
                           ->  Index Scan using id_table_id2_lat_idx on id_table   (cost=0.43..272.57 rows=9 width=12) (actual time=0.139..0.937 rows=9 loops=1)
                                 Index Cond: ((field1 < '96.7'::double precision) AND (field1 > '96.7'::double precision))
                                 Filter: ((field2 < '50.7'::double precision) AND (field2 > '50.7'::double precision))
               ->  Append  (cost=0.43..9215.18 rows=166333 width=16) (actual time=0.020..0.514 rows=1604 loops=1)
                     ->  Bitmap Heap Scan on values_table_r1_h0  (cost=4.22..14.82 rows=1 width=16) (never executed)
                           Recheck Cond: (id = id_table.id)
                           Filter: (field3 = ANY  ('{6,7,8,9,10}'::integer[]))
                           ->  Bitmap Index Scan on values_table_r1_h0_pt_id_idx  (cost=0.00..4.22 rows=9 width=0) (never executed)
                                 Index Cond: (id = id_table.id)
                ...              
                    ->  Index Scan using values_table_r9_h8_pt_id_idx on values_table_r9_h8  (cost=0.43..77.42 rows=1522 width=16) (actual time=0.015..0.435 rows=1604 loops=1)
                           Index Cond: (pt_id = id_table.pt_id)
                           Filter: (field3 = ANY ('{6,7,8,9,10}'::integer[]))
                           Rows Removed by Filter: 280
...
                     ->  Bitmap Heap Scan on values_table_r10_h9  (cost=4.22..14.82 rows=1 width=16) (never executed)
                           Recheck Cond: (id = id_table.id)
                           Filter: (field3 = ANY  ('{6,7,8,9,10}'::integer[]))
                           ->  Bitmap Index Scan on values_table_r10_h9_pt_id_idx  (cost=0.00..4.22 rows=9 width=0) (never executed)
                                 Index Cond: (id = id_table.id)
 Planning Time: 4199.297 ms
 Execution Time: 4.114 ms
(317 rows)

                                                                                       QUERY PLAN                                                                                       
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Unique  (cost=452745.97..467148.88 rows=200 width=20) (actual time=1.719..2.033 rows=1715 loops=1)
   ->  Sort  (cost=452745.97..459947.42 rows=2880582 width=20) (actual time=1.718..1.772 rows=1715 loops=1)
         Sort Key: values_table_r1_h0.date
         Sort Method: quicksort  Memory: 25kB
         ->  Nested Loop  (cost=255.34..25536.35 rows=2880582 width=20) (actual time=0.728..1.416 rows=1715 loops=1)
               ->  Limit  (cost=254.92..254.92 rows=1 width=12) (actual time=0.693..0.694 rows=1 loops=1)
                     ->  Sort  (cost=254.92..254.94 rows=9 width=12) (actual time=0.692..0.693 rows=1 loops=1)
                           Sort Key: ((abs((id_table.field1 - '97.4'::double precision)) + abs((id_table.field2 - '52.26'::double precision))))
                           Sort Method: quicksort  Memory: 25kB
                           ->  Index Scan using id_table_id2_lat_idx on id_table   (cost=0.43..254.87 rows=9 width=12) (actual time=0.184..0.688 rows=9 loops=1)
                                 Index Cond: ((field1 < '97.6'::double precision) AND (field1 > '97.4'::double precision))
                                 Filter: ((field2 < '52.3'::double precision) AND (field2 > '52.32'::double precision))
              ->  Append  (cost=0.43..9215.18 rows=166333 width=16) (actual time=0.019..0.532 rows=1715 loops=1)
                     ->  Bitmap Heap Scan on values_table_r1_h0  (cost=4.22..14.82 rows=1 width=16) (never executed)
                           Recheck Cond: (id = id_table.id)
                           Filter: (field3 = ANY  ('{6,7,8,9,10}'::integer[]))
                           ->  Bitmap Index Scan on values_table_r1_h0_pt_id_idx  (cost=0.00..4.22 rows=9 width=0) (never executed)
                                 Index Cond: (id = id_table.id)
...
                     ->  Index Scan using values_table_r5_h3_pt_id_idx on values_table_r5_h3  (cost=0.44..87.53 rows=1758 width=16) (actual time=0.016..0.470 rows=1715 loops=1)
                           Index Cond: (pt_id = pts_lake_id3.pt_id)
                           Filter: (month = ANY ('{6,7,8,9,10}'::integer[]))
                           Rows Removed by Filter: 281
...
                     ->  Bitmap Heap Scan on values_table_r10_h9  (cost=4.22..14.82 rows=1 width=16) (never executed)
                           Recheck Cond: (id = id_table.id)
                           Filter: (field3 = ANY  ('{6,7,8,9,10}'::integer[]))
                           ->  Bitmap Index Scan on values_table_r10_h9_pt_id_idx  (cost=0.00..4.22 rows=9 width=0) (never executed)
                                 Index Cond: (id = id_table.id)
 Planning Time: 5.590 ms
 Execution Time: 3.362 ms
(317 rows)

看起来第一次查询的额外时间是Planning Time: 4348.720 ms。一个具有多个查询的连接显示的时间序列为：

Planning Time: 4455.529 ms
 Execution Time: 267.157 ms
 Planning Time: 6.161 ms
 Execution Time: 177.163 ms
 Planning Time: 5.950 ms
 Execution Time: 279.961 ms
 Planning Time: 5.447 ms
 Execution Time: 180.953 ms
 Planning Time: 7.334 ms
 Execution Time: 571.524 ms
 Planning Time: 10.463 ms
 Execution Time: 1108.749 ms

所以我只需要在第一次查询时尝试减少这个计划时间？

postgresql

1条答案

按热度按时间
bbmckpt7

bbmckpt71#

问题似乎出在加载对象的元数据所需的时间上（我猜主要是分区和分区索引），这样它就可以做规划了。我不认为有任何解决方案，只是变通办法。一旦你有了连接，坚持下去，这样下次就不需要重复加载元数据了。如果不方便的话，你可以使用一个外部的连接池，比如pgbouncer来为你保留连接。

赞(0）分享  回复(0）举报  2023-03-12
我来回答

相关问题

    查看更多

    热门标签

    更多
    JavaquerypythonNode开发语言requestUtil数据库Table后端算法LoggerMessageElementParser

    最新问答

    更多
    • 蜀ICP备2022004421号-2 NULL123 https://www.null123.com © All rights reserved
    • 本站内容来源互联网,如果侵犯您的权益请联系我们删除, 联系方式：448109455@qq.com