我的编程空间，编程开发者的网络收藏夹
学习永远不晚
位置：首页-资讯-数据库

PostgreSQL中不同数据类型对查询性能的影响有哪些

2024-04-02 19:55

短信预约 -IT技能 免费直播动态提醒
省份

北京

  • 北京
  • 上海
  • 天津
  • 重庆
  • 河北
  • 山东
  • 辽宁
  • 黑龙江
  • 吉林
  • 甘肃
  • 青海
  • 河南
  • 江苏
  • 湖北
  • 湖南
  • 江西
  • 浙江
  • 广东
  • 云南
  • 福建
  • 海南
  • 山西
  • 四川
  • 陕西
  • 贵州
  • 安徽
  • 广西
  • 内蒙
  • 西藏
  • 新疆
  • 宁夏
  • 兵团
手机号
获取短信验证
立即预约

请填写图片验证码后获取短信验证码

看不清楚，换张图片

免费获取短信验证码

PostgreSQL中不同数据类型对查询性能的影响有哪些

本篇内容主要讲解“PostgreSQL中不同数据类型对查询性能的影响有哪些”，感兴趣的朋友不妨来看看。本文介绍的方法操作简单快捷，实用性强。下面就让小编来带大家学习“PostgreSQL中不同数据类型对查询性能的影响有哪些”吧!

容量
数据列占用空间大小

[local]:5432 pg12@testdb=# SELECT pg_column_size(SMALLINT '1'),pg_column_size(INT4 '1'), pg_column_size(NUMERIC(6,0) '1'),pg_column_size(FLOAT '1');
 pg_column_size | pg_column_size | pg_column_size | pg_column_size 
----------------+----------------+----------------+----------------
              2 |              4 |              8 |              8

创建数据表，0和1的数据值各插入100w行，查看数据表的占用空间大小。
numeric

[local]:5432 pg12@testdb=# create table t_numeric(id numeric);
CREATE TABLE
[local]:5432 pg12@testdb=# 
[local]:5432 pg12@testdb=# insert into t_numeric select 0 from generate_series(1,1000000);
INSERT 0 1000000
[local]:5432 pg12@testdb=# insert into t_numeric select 1 from generate_series(1,1000000);
INSERT 0 1000000
[local]:5432 pg12@testdb=# select pg_size_pretty(pg_relation_size('t_numeric'));
 pg_size_pretty 
----------------
 69 MB
(1 row)

float

[local]:5432 pg12@testdb=# create table t_float(id int);
CREATE TABLE
[local]:5432 pg12@testdb=# 
[local]:5432 pg12@testdb=# insert into t_float select 0 from generate_series(1,1000000);
INSERT 0 1000000
[local]:5432 pg12@testdb=# insert into t_float select 1 from generate_series(1,1000000);
INSERT 0 1000000
[local]:5432 pg12@testdb=# 
[local]:5432 pg12@testdb=# select pg_size_pretty(pg_relation_size('t_float'));
 pg_size_pretty 
----------------
 69 MB
(1 row)
[local]:5432 pg12@testdb=#

int

[local]:5432 pg12@testdb=# create table t_int(id int);
CREATE TABLE
[local]:5432 pg12@testdb=# 
[local]:5432 pg12@testdb=# insert into t_int select 0 from generate_series(1,1000000);
INSERT 0 1000000
[local]:5432 pg12@testdb=# insert into t_int select 1 from generate_series(1,1000000);
INSERT 0 1000000
[local]:5432 pg12@testdb=# select pg_size_pretty(pg_relation_size('t_int'));
 pg_size_pretty 
----------------
 69 MB
(1 row)

smallint

[local]:5432 pg12@testdb=# create table t_smallint(id smallint);
CREATE TABLE
[local]:5432 pg12@testdb=# 
[local]:5432 pg12@testdb=# insert into t_smallint select 0 from generate_series(1,1000000);
INSERT 0 1000000
[local]:5432 pg12@testdb=# insert into t_smallint select 1 from generate_series(1,1000000);
INSERT 0 1000000
[local]:5432 pg12@testdb=# 
[local]:5432 pg12@testdb=# select pg_size_pretty(pg_relation_size('t_smallint'));
 pg_size_pretty 
----------------
 69 MB
(1 row)

boolean

[local]:5432 pg12@testdb=# create table t_bool(id boolean);
CREATE TABLE
[local]:5432 pg12@testdb=# insert into t_bool select 0::boolean from generate_series(1,1000000);
INSERT 0 1000000
[local]:5432 pg12@testdb=# insert into t_bool select 1::boolean from generate_series(1,1000000);
INSERT 0 1000000
[local]:5432 pg12@testdb=# 
[local]:5432 pg12@testdb=# select pg_size_pretty(pg_relation_size('t_bool'));
 pg_size_pretty 
----------------
 69 MB
(1 row)

可以看到，四种数据类型占用的空间都是69 MB。

查询性能
不加条件，全表扫描

-- 禁用并行
[local]:5432 pg12@testdb=# SET max_parallel_workers_per_gather = 0;
SET
[local]:5432 pg12@testdb=# explain (analyze,verbose,buffers) select count(*) from t_numeric;
                                                            QUERY PLAN                                                            
----------------------------------------------------------------------------------------------------------------------------------
 Aggregate  (cost=33850.00..33850.01 rows=1 width=8) (actual time=478.196..478.196 rows=1 loops=1)
   Output: count(*)
   Buffers: shared hit=8850
   ->  Seq Scan on public.t_numeric  (cost=0.00..28850.00 rows=2000000 width=0) (actual time=0.053..255.949 rows=2000000 loops=1)
         Output: id
         Buffers: shared hit=8850
 Planning Time: 0.716 ms
 Execution Time: 478.280 ms
(8 rows)
[local]:5432 pg12@testdb=# explain (analyze,verbose,buffers) select count(*) from t_float;
                                                           QUERY PLAN                                                           
--------------------------------------------------------------------------------------------------------------------------------
 Aggregate  (cost=33850.00..33850.01 rows=1 width=8) (actual time=421.919..421.919 rows=1 loops=1)
   Output: count(*)
   Buffers: shared hit=8850
   ->  Seq Scan on public.t_float  (cost=0.00..28850.00 rows=2000000 width=0) (actual time=0.010..222.624 rows=2000000 loops=1)
         Output: id
         Buffers: shared hit=8850
 Planning Time: 0.231 ms
 Execution Time: 421.948 ms
(8 rows)
[local]:5432 pg12@testdb=# explain (analyze,verbose,buffers) select count(*) from t_int;
                                                          QUERY PLAN                                                          
------------------------------------------------------------------------------------------------------------------------------
 Aggregate  (cost=33850.00..33850.01 rows=1 width=8) (actual time=440.328..440.328 rows=1 loops=1)
   Output: count(*)
   Buffers: shared hit=8850
   ->  Seq Scan on public.t_int  (cost=0.00..28850.00 rows=2000000 width=0) (actual time=0.011..236.078 rows=2000000 loops=1)
         Output: id
         Buffers: shared hit=8850
 Planning Time: 0.208 ms
 Execution Time: 440.359 ms
(8 rows)
[local]:5432 pg12@testdb=#  explain (analyze,verbose,buffers) select count(*) from t_smallint;
                                                            QUERY PLAN                                                             
-----------------------------------------------------------------------------------------------------------------------------------
 Aggregate  (cost=33850.00..33850.01 rows=1 width=8) (actual time=439.007..439.007 rows=1 loops=1)
   Output: count(*)
   Buffers: shared hit=8850
   ->  Seq Scan on public.t_smallint  (cost=0.00..28850.00 rows=2000000 width=0) (actual time=0.043..232.069 rows=2000000 loops=1)
         Output: id
         Buffers: shared hit=8850
 Planning Time: 0.553 ms
 Execution Time: 439.081 ms
(8 rows)
[local]:5432 pg12@testdb=# explain (analyze,verbose,buffers) select count(*) from t_bool;
                                                          QUERY PLAN                                                           
-------------------------------------------------------------------------------------------------------------------------------
 Aggregate  (cost=33850.00..33850.01 rows=1 width=8) (actual time=430.800..430.800 rows=1 loops=1)
   Output: count(*)
   Buffers: shared hit=8850
   ->  Seq Scan on public.t_bool  (cost=0.00..28850.00 rows=2000000 width=0) (actual time=0.010..230.333 rows=2000000 loops=1)
         Output: id
         Buffers: shared hit=8850
 Planning Time: 0.224 ms
 Execution Time: 430.831 ms
(8 rows)
[local]:5432 pg12@testdb=#

不带条件全表扫描，时间相差不大，执行时长最大的是numeric类型。

添加查询条件，全表扫描

[local]:5432 pg12@testdb=# explain (analyze,verbose,buffers) select count(*) from t_numeric where id = '0'::numeric;
lain (analyze,verbose,buffers) select count(*) from t_bool where id = 0::boolean;
                                                            QUERY PLAN                                                            
----------------------------------------------------------------------------------------------------------------------------------
 Aggregate  (cost=36358.67..36358.68 rows=1 width=8) (actual time=723.356..723.357 rows=1 loops=1)
   Output: count(*)
   Buffers: shared hit=8850
   ->  Seq Scan on public.t_numeric  (cost=0.00..33850.00 rows=1003467 width=0) (actual time=0.057..610.907 rows=1000000 loops=1)
         Output: id
         Filter: (t_numeric.id = '0'::numeric)
         Rows Removed by Filter: 1000000
         Buffers: shared hit=8850
 Planning Time: 1.901 ms
 Execution Time: 723.449 ms
(10 rows)
[local]:5432 pg12@testdb=# explain (analyze,verbose,buffers) select count(*) from t_float where id = '0'::numeric;
                                                          QUERY PLAN                                                          
------------------------------------------------------------------------------------------------------------------------------
 Aggregate  (cost=38875.00..38875.01 rows=1 width=8) (actual time=827.686..827.687 rows=1 loops=1)
   Output: count(*)
   Buffers: shared hit=8850
   ->  Seq Scan on public.t_float  (cost=0.00..38850.00 rows=10000 width=0) (actual time=0.015..725.737 rows=1000000 loops=1)
         Output: id
         Filter: ((t_float.id)::numeric = '0'::numeric)
         Rows Removed by Filter: 1000000
         Buffers: shared hit=8850
 Planning Time: 0.234 ms
 Execution Time: 827.720 ms
(10 rows)
[local]:5432 pg12@testdb=# explain (analyze,verbose,buffers) select count(*) from t_int where id = 0;
                                                         QUERY PLAN                                                          
-----------------------------------------------------------------------------------------------------------------------------
 Aggregate  (cost=36329.50..36329.51 rows=1 width=8) (actual time=434.067..434.067 rows=1 loops=1)
   Output: count(*)
   Buffers: shared hit=8850
   ->  Seq Scan on public.t_int  (cost=0.00..33850.00 rows=991800 width=0) (actual time=0.014..333.883 rows=1000000 loops=1)
         Output: id
         Filter: (t_int.id = 0)
         Rows Removed by Filter: 1000000
         Buffers: shared hit=8850
 Planning Time: 0.295 ms
 Execution Time: 434.101 ms
(10 rows)
[local]:5432 pg12@testdb=# explain (analyze,verbose,buffers) select count(*) from t_smallint where id = 0;
                                                            QUERY PLAN                                                             
-----------------------------------------------------------------------------------------------------------------------------------
 Aggregate  (cost=36354.50..36354.51 rows=1 width=8) (actual time=486.466..486.466 rows=1 loops=1)
   Output: count(*)
   Buffers: shared hit=8850
   ->  Seq Scan on public.t_smallint  (cost=0.00..33850.00 rows=1001800 width=0) (actual time=0.053..368.184 rows=1000000 loops=1)
         Output: id
         Filter: (t_smallint.id = 0)
         Rows Removed by Filter: 1000000
         Buffers: shared hit=8850
 Planning Time: 1.396 ms
 Execution Time: 486.554 ms
(10 rows)
[local]:5432 pg12@testdb=# explain (analyze,verbose,buffers) select count(*) from t_bool where id = 0::boolean;
                                                          QUERY PLAN                                                           
-------------------------------------------------------------------------------------------------------------------------------
 Aggregate  (cost=31356.67..31356.68 rows=1 width=8) (actual time=416.510..416.510 rows=1 loops=1)
   Output: count(*)
   Buffers: shared hit=8850
   ->  Seq Scan on public.t_bool  (cost=0.00..28850.00 rows=1002667 width=0) (actual time=0.014..316.188 rows=1000000 loops=1)
         Output: id
         Filter: (NOT t_bool.id)
         Rows Removed by Filter: 1000000
         Buffers: shared hit=8850
 Planning Time: 0.261 ms
 Execution Time: 416.551 ms
(10 rows)
[local]:5432 pg12@testdb=#

存在查询条件的情况下，由于解析表达式的代价不同（bool < int < numeric < float），因此时间相差较大，时长最大的是float类型，时间接近bool类型的2倍。

创建索引，全索引扫描
禁用全表扫描，使用全索引扫描

[local]:5432 pg12@testdb=# explain (analyze,verbose,buffers) select count(*) from t_numeric where id = '0'::numeric;
                                                                           QUERY PLAN                                                                           
----------------------------------------------------------------------------------------------------------------------------------------------------------------
 Aggregate  (cost=35541.77..35541.78 rows=1 width=8) (actual time=594.984..594.984 rows=1 loops=1)
   Output: count(*)
   Buffers: shared hit=7160
   ->  Index Only Scan using idx_t_numeric_id on public.t_numeric  (cost=0.43..33033.10 rows=1003467 width=0) (actual time=0.269..482.525 rows=1000000 loops=1)
         Output: id
         Index Cond: (t_numeric.id = '0'::numeric)
         Heap Fetches: 1000000
         Buffers: shared hit=7160
 Planning Time: 1.392 ms
 Execution Time: 595.253 ms
(10 rows)
[local]:5432 pg12@testdb=# explain (analyze,verbose,buffers) select count(*) from t_float where id = '0'::numeric;
                                                                        QUERY PLAN                                                                         
-----------------------------------------------------------------------------------------------------------------------------------------------------------
 Aggregate  (cost=70854.43..70854.44 rows=1 width=8) (actual time=1337.093..1337.094 rows=1 loops=1)
   Output: count(*)
   Buffers: shared hit=14317
   ->  Index Only Scan using idx_t_float_id on public.t_float  (cost=0.43..70829.43 rows=10000 width=0) (actual time=0.037..1233.730 rows=1000000 loops=1)
         Output: id
         Filter: ((t_float.id)::numeric = '0'::numeric)
         Rows Removed by Filter: 1000000
         Heap Fetches: 2000000
         Buffers: shared hit=14317
 Planning Time: 0.293 ms
 Execution Time: 1337.168 ms
(11 rows)
[local]:5432 pg12@testdb=# explain (analyze,verbose,buffers) select count(*) from t_int where id = 0;
                                                                      QUERY PLAN                                                                       
-------------------------------------------------------------------------------------------------------------------------------------------------------
 Aggregate  (cost=35128.43..35128.44 rows=1 width=8) (actual time=526.942..526.943 rows=1 loops=1)
   Output: count(*)
   Buffers: shared hit=7160
   ->  Index Only Scan using idx_t_int_id on public.t_int  (cost=0.43..32648.93 rows=991800 width=0) (actual time=0.035..414.797 rows=1000000 loops=1)
         Output: id
         Index Cond: (t_int.id = 0)
         Heap Fetches: 1000000
         Buffers: shared hit=7160
 Planning Time: 0.245 ms
 Execution Time: 526.979 ms
(10 rows)
[local]:5432 pg12@testdb=# explain (analyze,verbose,buffers) select count(*) from t_smallint where id = 0;
                                                                            QUERY PLAN                                                                            
------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Aggregate  (cost=35480.43..35480.44 rows=1 width=8) (actual time=551.394..551.394 rows=1 loops=1)
   Output: count(*)
   Buffers: shared hit=4428 read=2735
   ->  Index Only Scan using idx_t_smallint_id on public.t_smallint  (cost=0.43..32975.93 rows=1001800 width=0) (actual time=0.459..438.992 rows=1000000 loops=1)
         Output: id
         Index Cond: (t_smallint.id = 0)
         Heap Fetches: 1000000
         Buffers: shared hit=4428 read=2735
 Planning Time: 1.889 ms
 Execution Time: 551.499 ms
(10 rows)
[local]:5432 pg12@testdb=# explain (analyze,verbose,buffers) select count(*) from t_bool where id = 0::boolean;
                                                                        QUERY PLAN                                                                        
----------------------------------------------------------------------------------------------------------------------------------------------------------
 Aggregate  (cost=35513.77..35513.78 rows=1 width=8) (actual time=497.886..497.886 rows=1 loops=1)
   Output: count(*)
   Buffers: shared hit=7160
   ->  Index Only Scan using idx_t_bool_id on public.t_bool  (cost=0.43..33007.10 rows=1002667 width=0) (actual time=0.035..393.653 rows=1000000 loops=1)
         Output: id
         Index Cond: (t_bool.id = false)
         Heap Fetches: 1000000
         Buffers: shared hit=7160
 Planning Time: 0.250 ms
 Execution Time: 497.922 ms
(10 rows)
[local]:5432 pg12@testdb=#

走全索引扫描，执行时长最长的仍是float类型，其他三种类型则相差不大，numeric的性能相较全表扫描有明显提升（595ms vs 723ms）。

压力测试
使用pgbench进行压力测试，numeric/float/int三种类型，各插入100w数据

drop table t_big_numeric;
create table t_big_numeric(id numeric);
insert into t_big_numeric select 0 from generate_series(1,1000000);
drop table t_big_float;
create table t_big_float(id int);
insert into t_big_float select 0 from generate_series(1,1000000);
drop table t_big_int;
create table t_big_int(id int);
insert into t_big_int select 0 from generate_series(1,1000000);

测试结果

[pg12@localhost test]$ pgbench -C -f ./select_numeric.sql --time=120 --client=8 --jobs=2 -d testdb
...
transaction type: ./select_numeric.sql
scaling factor: 1
query mode: simple
number of clients: 8
number of threads: 2
duration: 120 s
number of transactions actually processed: 1254
latency average = 768.659 ms
tps = 10.407739 (including connections establishing)
tps = 10.906626 (excluding connections establishing)
[pg12@localhost test]$ 
[pg12@localhost test]$ pgbench -C -f ./select_float.sql --time=120 --client=8 --jobs=2 -d testdb
...
transaction type: ./select_float.sql
scaling factor: 1
query mode: simple
number of clients: 8
number of threads: 2
duration: 120 s
number of transactions actually processed: 2167
latency average = 444.006 ms
tps = 18.017778 (including connections establishing)
tps = 19.461350 (excluding connections establishing)
[pg12@localhost test]$ cat select_float.sql 
\set id random(1,1000000)
select * from t_big_float where id = :id; 
[pg12@localhost test]$ 
[pg12@localhost test]$ pgbench -C -f ./select_int.sql --time=120 --client=8 --jobs=2 -d testdb
...
transaction type: ./select_int.sql
scaling factor: 1
query mode: simple
number of clients: 8
number of threads: 2
duration: 120 s
number of transactions actually processed: 2184
latency average = 440.271 ms
tps = 18.170626 (including connections establishing)
tps = 19.658996 (excluding connections establishing)
[pg12@localhost test]$

到此，相信大家对“PostgreSQL中不同数据类型对查询性能的影响有哪些”有了更深的了解，不妨来实际操作一番吧！这里是亿速云网站，更多相关内容可以进入相关频道进行查询，关注我们，继续学习！

免责声明：

① 本站未注明“稿件来源”的信息均来自网络整理。其文字、图片和音视频稿件的所属权归原作者所有。本站收集整理出于非商业性的教育和科研之目的，并不意味着本站赞同其观点或证实其内容的真实性。仅作为临时的测试数据，供内部测试之用。本站并未授权任何人以任何方式主动获取本站任何信息。

② 本站未注明“稿件来源”的临时测试数据将在测试完成后最终做删除处理。有问题或投稿请发送至: 邮箱/279061341@qq.com QQ/279061341

阅读原文内容投诉

PostgreSQL中不同数据类型对查询性能的影响有哪些

下载Word文档到电脑，方便收藏和打印～

下载Word文档

相关文章

猜你喜欢

PostgreSQL中不同数据类型对查询性能的影响有哪些

2024-04-02

Go语言中的数据类型对程序的性能有何影响？

2023-08-18

GO编程中的数据类型选择对性能的影响

2023-09-20

mysql中影响数据库性能的因素有哪些

2024-04-02

Go语言中数据类型的选择对同步数组操作有何影响？

2023-10-11

ASP 中响应数组的数据类型如何影响其在不同浏览器中的兼容性？

2023-09-10

异步编程中Java数据类型的选择对Linux系统有哪些影响？

2023-09-16

ASP IDE有哪些适用于不同数据类型的功能？

2023-10-08

GO语言中的缓存与数据类型的选择对性能的影响是怎样的？

GO语言中的缓存与数据类型的选择对性能的影响是怎样的？
2023-11-13

热门标签

Linux(148)PHP(127)Java(102)正则表达式(101)JavaScript(69)最佳实践(67)jQuery(44)MySQL(39)Docker(37)C语言(36)性能优化(34)Python(34)XML(28)string(27)第三方库(23)回调函数(23)ZIP(22)数组(22)可扩展性(22)字符串比较(21)find(20)RPM(20)Go(20)grep(19)ASP.NETCore(19)XML解析器(19)事件(19)事件处理程序(19)StringBuilder(18)Nginx(18)

编程热搜

查看更多

编程资源站

  • 资料下载
  • 历年试题
2021年下半年软考高级信息系统项目管理师高频考点精选资料
2021下半年软考高级信息系统技术知识点记忆口诀精选资料
2021下半年软考《信息系统项目管理师》考试真题及答案精选资料
2021下半年软考高级考试备考攻略精选资料
2021年软考高级《信息系统项目管理师》巩固练习题汇总精选资料
2021下半年软考高级信息系统项目管理师30个易考知识点汇总精选资料
2021下半年软考高级知识点这样记，还担心记不住吗精选资料
2021年下半年软考高级考试重点汇总精选资料
2021下半年软考高级信息系统项目管理师计算公式汇总精选资料
2021年下半年软考高级《信息系统项目管理师》模拟试题精选资料
查看更多
信息系统项目管理师选择题每日一练（2024）历年试题
2023年下半年信息系统项目管理师综合知识真题演练历年试题
查看更多

目录

本网页已闲置超过3分钟，按键盘任意键或点击空白处，即可回到网页
最新资讯更多
    友情链接编程网

    反馈

    我要
    反馈