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

容量
数据列占用空间大小

[local]:5432pg12@testdb=#SELECTpg_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]:5432pg12@testdb=#createtablet_numeric(idnumeric);CREATETABLE[local]:5432pg12@testdb=#[local]:5432pg12@testdb=#insertintot_numericselect0fromgenerate_series(1,1000000);INSERT01000000[local]:5432pg12@testdb=#insertintot_numericselect1fromgenerate_series(1,1000000);INSERT01000000[local]:5432pg12@testdb=#selectpg_size_pretty(pg_relation_size('t_numeric'));pg_size_pretty----------------69MB(1row)

float

[local]:5432pg12@testdb=#createtablet_float(idint);CREATETABLE[local]:5432pg12@testdb=#[local]:5432pg12@testdb=#insertintot_floatselect0fromgenerate_series(1,1000000);INSERT01000000[local]:5432pg12@testdb=#insertintot_floatselect1fromgenerate_series(1,1000000);INSERT01000000[local]:5432pg12@testdb=#[local]:5432pg12@testdb=#selectpg_size_pretty(pg_relation_size('t_float'));pg_size_pretty----------------69MB(1row)[local]:5432pg12@testdb=#

int

[local]:5432pg12@testdb=#createtablet_int(idint);CREATETABLE[local]:5432pg12@testdb=#[local]:5432pg12@testdb=#insertintot_intselect0fromgenerate_series(1,1000000);INSERT01000000[local]:5432pg12@testdb=#insertintot_intselect1fromgenerate_series(1,1000000);INSERT01000000[local]:5432pg12@testdb=#selectpg_size_pretty(pg_relation_size('t_int'));pg_size_pretty----------------69MB(1row)

smallint

[local]:5432pg12@testdb=#createtablet_smallint(idsmallint);CREATETABLE[local]:5432pg12@testdb=#[local]:5432pg12@testdb=#insertintot_smallintselect0fromgenerate_series(1,1000000);INSERT01000000[local]:5432pg12@testdb=#insertintot_smallintselect1fromgenerate_series(1,1000000);INSERT01000000[local]:5432pg12@testdb=#[local]:5432pg12@testdb=#selectpg_size_pretty(pg_relation_size('t_smallint'));pg_size_pretty----------------69MB(1row)

boolean

[local]:5432pg12@testdb=#createtablet_bool(idboolean);CREATETABLE[local]:5432pg12@testdb=#insertintot_boolselect0::booleanfromgenerate_series(1,1000000);INSERT01000000[local]:5432pg12@testdb=#insertintot_boolselect1::booleanfromgenerate_series(1,1000000);INSERT01000000[local]:5432pg12@testdb=#[local]:5432pg12@testdb=#selectpg_size_pretty(pg_relation_size('t_bool'));pg_size_pretty----------------69MB(1row)

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

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

--禁用并行[local]:5432pg12@testdb=#SETmax_parallel_workers_per_gather=0;SET[local]:5432pg12@testdb=#explain(analyze,verbose,buffers)selectcount(*)fromt_numeric;QUERYPLAN----------------------------------------------------------------------------------------------------------------------------------Aggregate(cost=33850.00..33850.01rows=1width=8)(actualtime=478.196..478.196rows=1loops=1)Output:count(*)Buffers:sharedhit=8850->SeqScanonpublic.t_numeric(cost=0.00..28850.00rows=2000000width=0)(actualtime=0.053..255.949rows=2000000loops=1)Output:idBuffers:sharedhit=8850PlanningTime:0.716msExecutionTime:478.280ms(8rows)[local]:5432pg12@testdb=#explain(analyze,verbose,buffers)selectcount(*)fromt_float;QUERYPLAN--------------------------------------------------------------------------------------------------------------------------------Aggregate(cost=33850.00..33850.01rows=1width=8)(actualtime=421.919..421.919rows=1loops=1)Output:count(*)Buffers:sharedhit=8850->SeqScanonpublic.t_float(cost=0.00..28850.00rows=2000000width=0)(actualtime=0.010..222.624rows=2000000loops=1)Output:idBuffers:sharedhit=8850PlanningTime:0.231msExecutionTime:421.948ms(8rows)[local]:5432pg12@testdb=#explain(analyze,verbose,buffers)selectcount(*)fromt_int;QUERYPLAN------------------------------------------------------------------------------------------------------------------------------Aggregate(cost=33850.00..33850.01rows=1width=8)(actualtime=440.328..440.328rows=1loops=1)Output:count(*)Buffers:sharedhit=8850->SeqScanonpublic.t_int(cost=0.00..28850.00rows=2000000width=0)(actualtime=0.011..236.078rows=2000000loops=1)Output:idBuffers:sharedhit=8850PlanningTime:0.208msExecutionTime:440.359ms(8rows)[local]:5432pg12@testdb=#explain(analyze,verbose,buffers)selectcount(*)fromt_smallint;QUERYPLAN-----------------------------------------------------------------------------------------------------------------------------------Aggregate(cost=33850.00..33850.01rows=1width=8)(actualtime=439.007..439.007rows=1loops=1)Output:count(*)Buffers:sharedhit=8850->SeqScanonpublic.t_smallint(cost=0.00..28850.00rows=2000000width=0)(actualtime=0.043..232.069rows=2000000loops=1)Output:idBuffers:sharedhit=8850PlanningTime:0.553msExecutionTime:439.081ms(8rows)[local]:5432pg12@testdb=#explain(analyze,verbose,buffers)selectcount(*)fromt_bool;QUERYPLAN-------------------------------------------------------------------------------------------------------------------------------Aggregate(cost=33850.00..33850.01rows=1width=8)(actualtime=430.800..430.800rows=1loops=1)Output:count(*)Buffers:sharedhit=8850->SeqScanonpublic.t_bool(cost=0.00..28850.00rows=2000000width=0)(actualtime=0.010..230.333rows=2000000loops=1)Output:idBuffers:sharedhit=8850PlanningTime:0.224msExecutionTime:430.831ms(8rows)[local]:5432pg12@testdb=#

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

添加查询条件，全表扫描

[local]:5432pg12@testdb=#explain(analyze,verbose,buffers)selectcount(*)fromt_numericwhereid='0'::numeric;lain(analyze,verbose,buffers)selectcount(*)fromt_boolwhereid=0::boolean;QUERYPLAN----------------------------------------------------------------------------------------------------------------------------------Aggregate(cost=36358.67..36358.68rows=1width=8)(actualtime=723.356..723.357rows=1loops=1)Output:count(*)Buffers:sharedhit=8850->SeqScanonpublic.t_numeric(cost=0.00..33850.00rows=1003467width=0)(actualtime=0.057..610.907rows=1000000loops=1)Output:idFilter:(t_numeric.id='0'::numeric)RowsRemovedbyFilter:1000000Buffers:sharedhit=8850PlanningTime:1.901msExecutionTime:723.449ms(10rows)[local]:5432pg12@testdb=#explain(analyze,verbose,buffers)selectcount(*)fromt_floatwhereid='0'::numeric;QUERYPLAN------------------------------------------------------------------------------------------------------------------------------Aggregate(cost=38875.00..38875.01rows=1width=8)(actualtime=827.686..827.687rows=1loops=1)Output:count(*)Buffers:sharedhit=8850->SeqScanonpublic.t_float(cost=0.00..38850.00rows=10000width=0)(actualtime=0.015..725.737rows=1000000loops=1)Output:idFilter:((t_float.id)::numeric='0'::numeric)RowsRemovedbyFilter:1000000Buffers:sharedhit=8850PlanningTime:0.234msExecutionTime:827.720ms(10rows)[local]:5432pg12@testdb=#explain(analyze,verbose,buffers)selectcount(*)fromt_intwhereid=0;QUERYPLAN-----------------------------------------------------------------------------------------------------------------------------Aggregate(cost=36329.50..36329.51rows=1width=8)(actualtime=434.067..434.067rows=1loops=1)Output:count(*)Buffers:sharedhit=8850->SeqScanonpublic.t_int(cost=0.00..33850.00rows=991800width=0)(actualtime=0.014..333.883rows=1000000loops=1)Output:idFilter:(t_int.id=0)RowsRemovedbyFilter:1000000Buffers:sharedhit=8850PlanningTime:0.295msExecutionTime:434.101ms(10rows)[local]:5432pg12@testdb=#explain(analyze,verbose,buffers)selectcount(*)fromt_smallintwhereid=0;QUERYPLAN-----------------------------------------------------------------------------------------------------------------------------------Aggregate(cost=36354.50..36354.51rows=1width=8)(actualtime=486.466..486.466rows=1loops=1)Output:count(*)Buffers:sharedhit=8850->SeqScanonpublic.t_smallint(cost=0.00..33850.00rows=1001800width=0)(actualtime=0.053..368.184rows=1000000loops=1)Output:idFilter:(t_smallint.id=0)RowsRemovedbyFilter:1000000Buffers:sharedhit=8850PlanningTime:1.396msExecutionTime:486.554ms(10rows)[local]:5432pg12@testdb=#explain(analyze,verbose,buffers)selectcount(*)fromt_boolwhereid=0::boolean;QUERYPLAN-------------------------------------------------------------------------------------------------------------------------------Aggregate(cost=31356.67..31356.68rows=1width=8)(actualtime=416.510..416.510rows=1loops=1)Output:count(*)Buffers:sharedhit=8850->SeqScanonpublic.t_bool(cost=0.00..28850.00rows=1002667width=0)(actualtime=0.014..316.188rows=1000000loops=1)Output:idFilter:(NOTt_bool.id)RowsRemovedbyFilter:1000000Buffers:sharedhit=8850PlanningTime:0.261msExecutionTime:416.551ms(10rows)[local]:5432pg12@testdb=#

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

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

[local]:5432pg12@testdb=#explain(analyze,verbose,buffers)selectcount(*)fromt_numericwhereid='0'::numeric;QUERYPLAN----------------------------------------------------------------------------------------------------------------------------------------------------------------Aggregate(cost=35541.77..35541.78rows=1width=8)(actualtime=594.984..594.984rows=1loops=1)Output:count(*)Buffers:sharedhit=7160->IndexOnlyScanusingidx_t_numeric_idonpublic.t_numeric(cost=0.43..33033.10rows=1003467width=0)(actualtime=0.269..482.525rows=1000000loops=1)Output:idIndexCond:(t_numeric.id='0'::numeric)HeapFetches:1000000Buffers:sharedhit=7160PlanningTime:1.392msExecutionTime:595.253ms(10rows)[local]:5432pg12@testdb=#explain(analyze,verbose,buffers)selectcount(*)fromt_floatwhereid='0'::numeric;QUERYPLAN-----------------------------------------------------------------------------------------------------------------------------------------------------------Aggregate(cost=70854.43..70854.44rows=1width=8)(actualtime=1337.093..1337.094rows=1loops=1)Output:count(*)Buffers:sharedhit=14317->IndexOnlyScanusingidx_t_float_idonpublic.t_float(cost=0.43..70829.43rows=10000width=0)(actualtime=0.037..1233.730rows=1000000loops=1)Output:idFilter:((t_float.id)::numeric='0'::numeric)RowsRemovedbyFilter:1000000HeapFetches:2000000Buffers:sharedhit=14317PlanningTime:0.293msExecutionTime:1337.168ms(11rows)[local]:5432pg12@testdb=#explain(analyze,verbose,buffers)selectcount(*)fromt_intwhereid=0;QUERYPLAN-------------------------------------------------------------------------------------------------------------------------------------------------------Aggregate(cost=35128.43..35128.44rows=1width=8)(actualtime=526.942..526.943rows=1loops=1)Output:count(*)Buffers:sharedhit=7160->IndexOnlyScanusingidx_t_int_idonpublic.t_int(cost=0.43..32648.93rows=991800width=0)(actualtime=0.035..414.797rows=1000000loops=1)Output:idIndexCond:(t_int.id=0)HeapFetches:1000000Buffers:sharedhit=7160PlanningTime:0.245msExecutionTime:526.979ms(10rows)[local]:5432pg12@testdb=#explain(analyze,verbose,buffers)selectcount(*)fromt_smallintwhereid=0;QUERYPLAN------------------------------------------------------------------------------------------------------------------------------------------------------------------Aggregate(cost=35480.43..35480.44rows=1width=8)(actualtime=551.394..551.394rows=1loops=1)Output:count(*)Buffers:sharedhit=4428read=2735->IndexOnlyScanusingidx_t_smallint_idonpublic.t_smallint(cost=0.43..32975.93rows=1001800width=0)(actualtime=0.459..438.992rows=1000000loops=1)Output:idIndexCond:(t_smallint.id=0)HeapFetches:1000000Buffers:sharedhit=4428read=2735PlanningTime:1.889msExecutionTime:551.499ms(10rows)[local]:5432pg12@testdb=#explain(analyze,verbose,buffers)selectcount(*)fromt_boolwhereid=0::boolean;QUERYPLAN----------------------------------------------------------------------------------------------------------------------------------------------------------Aggregate(cost=35513.77..35513.78rows=1width=8)(actualtime=497.886..497.886rows=1loops=1)Output:count(*)Buffers:sharedhit=7160->IndexOnlyScanusingidx_t_bool_idonpublic.t_bool(cost=0.43..33007.10rows=1002667width=0)(actualtime=0.035..393.653rows=1000000loops=1)Output:idIndexCond:(t_bool.id=false)HeapFetches:1000000Buffers:sharedhit=7160PlanningTime:0.250msExecutionTime:497.922ms(10rows)[local]:5432pg12@testdb=#

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

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

droptablet_big_numeric;createtablet_big_numeric(idnumeric);insertintot_big_numericselect0fromgenerate_series(1,1000000);droptablet_big_float;createtablet_big_float(idint);insertintot_big_floatselect0fromgenerate_series(1,1000000);droptablet_big_int;createtablet_big_int(idint);insertintot_big_intselect0fromgenerate_series(1,1000000);

测试结果

[pg12@localhosttest]$pgbench-C-f./select_numeric.sql--time=120--client=8--jobs=2-dtestdb...transactiontype:./select_numeric.sqlscalingfactor:1querymode:simplenumberofclients:8numberofthreads:2duration:120snumberoftransactionsactuallyprocessed:1254latencyaverage=768.659mstps=10.407739(includingconnectionsestablishing)tps=10.906626(excludingconnectionsestablishing)[pg12@localhosttest]$[pg12@localhosttest]$pgbench-C-f./select_float.sql--time=120--client=8--jobs=2-dtestdb...transactiontype:./select_float.sqlscalingfactor:1querymode:simplenumberofclients:8numberofthreads:2duration:120snumberoftransactionsactuallyprocessed:2167latencyaverage=444.006mstps=18.017778(includingconnectionsestablishing)tps=19.461350(excludingconnectionsestablishing)[pg12@localhosttest]$catselect_float.sql\setidrandom(1,1000000)select*fromt_big_floatwhereid=:id;[pg12@localhosttest]$[pg12@localhosttest]$pgbench-C-f./select_int.sql--time=120--client=8--jobs=2-dtestdb...transactiontype:./select_int.sqlscalingfactor:1querymode:simplenumberofclients:8numberofthreads:2duration:120snumberoftransactionsactuallyprocessed:2184latencyaverage=440.271mstps=18.170626(includingconnectionsestablishing)tps=19.658996(excludingconnectionsestablishing)[pg12@localhosttest]$

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