技术标签: # postgresql opti table index pgbench pgsql
os: centos 7.8
db: postgresql 13.1
# cat /etc/centos-release
CentOS Linux release 7.8.2003 (Core)
#
# yum list installed |grep -i postgresql
postgresql13.x86_64 13.1-1PGDG.rhel7 @pgdg13
postgresql13-contrib.x86_64 13.1-1PGDG.rhel7 @pgdg13
postgresql13-devel.x86_64 13.1-1PGDG.rhel7 @pgdg13
postgresql13-docs.x86_64 13.1-1PGDG.rhel7 @pgdg13
postgresql13-libs.x86_64 13.1-1PGDG.rhel7 @pgdg13
postgresql13-llvmjit.x86_64 13.1-1PGDG.rhel7 @pgdg13
postgresql13-plperl.x86_64 13.1-1PGDG.rhel7 @pgdg13
postgresql13-plpython3.x86_64 13.1-1PGDG.rhel7 @pgdg13
postgresql13-pltcl.x86_64 13.1-1PGDG.rhel7 @pgdg13
postgresql13-server.x86_64 13.1-1PGDG.rhel7 @pgdg13
postgresql13-test.x86_64 13.1-1PGDG.rhel7 @pgdg13
# yum list installed |grep -i pathman
pg_pathman_13.x86_64 1.5.12-1.rhel7 @pgdg13
# su - postgres
Last login: Fri Dec 18 15:39:51 CST 2020 on pts/0
$ psql
psql (13.1)
Type "help" for help.
postgres=# select name,setting from pg_settings where name like '%partition%';
name | setting
-----------------------------------+---------
enable_partition_pruning | on
enable_partitionwise_aggregate | off
enable_partitionwise_join | off
pg_pathman.enable_auto_partition | on
pg_pathman.enable_partitionfilter | on
pg_pathman.enable_partitionrouter | off
(6 rows)
postgres=# select name,setting from pg_settings where name like 'pg_pathman%' order by name;
name | setting
--------------------------------------+----------
pg_pathman.enable | on
pg_pathman.enable_auto_partition | on
pg_pathman.enable_bounds_cache | on
pg_pathman.enable_partitionfilter | on
pg_pathman.enable_partitionrouter | off
pg_pathman.enable_runtimeappend | on
pg_pathman.enable_runtimemergeappend | on
pg_pathman.insert_into_fdw | postgres
pg_pathman.override_copy | on
(9 rows)
postgres=# create database yewudb;
yewudb=# \c yewudb
yewudb=# create extension pg_pathman;
yewudb=# \dx
List of installed extensions
Name | Version | Schema | Description
------------+---------+------------+----------------------------------
pg_pathman | 1.5 | public | Partitioning tool for PostgreSQL
plpgsql | 1.0 | pg_catalog | PL/pgSQL procedural language
(2 rows)
yewudb=# \timing
64分区
\set aid random(1, 1000000)
BEGIN;
select a.name from tmp_hash64 a where a.id in (:aid);
END;
/usr/pgsql-13/bin/pgbench -M simple -n -P 1 -f /var/lib/pgsql/bench/select64.sql -r -c 200 -j 4 -T 60 yewudb
tps = 11612.727137 (including connections establishing)
tps = 11615.184407 (excluding connections establishing)
statement latencies in milliseconds:
0.001 \set aid random(1, 1000000)
5.175 BEGIN;
5.509 select a.name from tmp_hash64 a where a.id in (:aid);
5.853 END;
128分区
\set aid random(1, 1000000)
BEGIN;
select a.name from tmp_hash128 a where a.id in (:aid);
END;
/usr/pgsql-13/bin/pgbench -M simple -n -P 1 -f /var/lib/pgsql/bench/select128.sql -r -c 200 -j 4 -T 60 yewudb
tps = 18402.839508 (including connections establishing)
tps = 18404.046885 (excluding connections establishing)
statement latencies in milliseconds:
0.001 \set aid random(1, 1000000)
3.264 BEGIN;
3.536 select a.name from tmp_hash128 a where a.id in (:aid);
3.735 END;
256分区
\set aid random(1, 1000000)
BEGIN;
select a.name from tmp_hash256 a where a.id in (:aid);
END;
/usr/pgsql-13/bin/pgbench -M simple -n -P 1 -f /var/lib/pgsql/bench/select256.sql -r -c 200 -j 4 -T 60 yewudb
tps = 17544.427395 (including connections establishing)
tps = 17545.723398 (excluding connections establishing)
statement latencies in milliseconds:
0.001 \set aid random(1, 1000000)
3.375 BEGIN;
3.751 select a.name from tmp_hash256 a where a.id in (:aid);
3.911 END;
512分区
\set aid random(1, 1000000)
BEGIN;
select a.name from tmp_hash512 a where a.id in (:aid);
END;
/usr/pgsql-13/bin/pgbench -M simple -n -P 1 -f /var/lib/pgsql/bench/select512.sql -r -c 200 -j 4 -T 60 yewudb
tps = 17541.912282 (including connections establishing)
tps = 17543.162897 (excluding connections establishing)
statement latencies in milliseconds:
0.001 \set aid random(1, 1000000)
3.323 BEGIN;
3.970 select a.name from tmp_hash512 a where a.id in (:aid);
3.779 END;
1024分区
\set aid random(1, 1000000)
BEGIN;
select a.name from tmp_hash1024 a where a.id in (:aid);
END;
/usr/pgsql-13/bin/pgbench -M simple -n -P 1 -f /var/lib/pgsql/bench/select1024.sql -r -c 200 -j 4 -T 60 yewudb
tps = 10503.688950 (including connections establishing)
tps = 10504.402890 (excluding connections establishing)
statement latencies in milliseconds:
0.001 \set aid random(1, 1000000)
5.366 BEGIN;
7.871 select a.name from tmp_hash1024 a where a.id in (:aid);
5.477 END;
64分区
\set aid random(1, 1000000)
BEGIN;
delete from tmp_hash64 where id in (:aid);
END;
/usr/pgsql-13/bin/pgbench -M simple -n -P 1 -f /var/lib/pgsql/bench/delete64.sql -r -c 200 -j 4 -T 60 yewudb
tps = 4991.522388 (including connections establishing)
tps = 4994.342298 (excluding connections establishing)
statement latencies in milliseconds:
0.001 \set aid random(1, 1000000)
1.350 BEGIN;
13.651 delete from tmp_hash64 where id in (:aid);
23.626 END;
128分区
\set aid random(1, 1000000)
BEGIN;
delete from tmp_hash128 where id in (:aid);
END;
/usr/pgsql-13/bin/pgbench -M simple -n -P 1 -f /var/lib/pgsql/bench/delete128.sql -r -c 200 -j 4 -T 60 yewudb
tps = 7669.556156 (including connections establishing)
tps = 7670.873011 (excluding connections establishing)
statement latencies in milliseconds:
0.001 \set aid random(1, 1000000)
1.414 BEGIN;
10.272 delete from tmp_hash128 where id in (:aid);
13.942 END;
256分区
\set aid random(1, 1000000)
BEGIN;
delete from tmp_hash256 where id in (:aid);
END;
/usr/pgsql-13/bin/pgbench -M simple -n -P 1 -f /var/lib/pgsql/bench/delete256.sql -r -c 200 -j 4 -T 60 yewudb
tps = 6094.133358 (including connections establishing)
tps = 6094.588383 (excluding connections establishing)
statement latencies in milliseconds:
0.001 \set aid random(1, 1000000)
1.467 BEGIN;
12.754 delete from tmp_hash256 where id in (:aid);
18.211 END;
512分区
\set aid random(1, 1000000)
BEGIN;
delete from tmp_hash512 where id in (:aid);
END;
/usr/pgsql-13/bin/pgbench -M simple -n -P 1 -f /var/lib/pgsql/bench/delete512.sql -r -c 200 -j 4 -T 60 yewudb
tps = 8107.010150 (including connections establishing)
tps = 8107.513521 (excluding connections establishing)
statement latencies in milliseconds:
0.001 \set aid random(1, 1000000)
1.643 BEGIN;
11.439 delete from tmp_hash512 where id in (:aid);
11.307 END;
1024分区
\set aid random(1, 1000000)
BEGIN;
delete from tmp_hash1024 where id in (:aid);
END;
/usr/pgsql-13/bin/pgbench -M simple -n -P 1 -f /var/lib/pgsql/bench/delete1024.sql -r -c 200 -j 4 -T 60 yewudb
tps = 5972.929791 (including connections establishing)
tps = 5973.345274 (excluding connections establishing)
statement latencies in milliseconds:
0.001 \set aid random(1, 1000000)
2.096 BEGIN;
17.301 delete from tmp_hash1024 where id in (:aid);
13.748 END;
64分区
\set aid random(1, 1000000)
BEGIN;
insert into tmp_hash64(id,name,insert_timestamp) values(:aid,:aid::text,now());
END;
/usr/pgsql-13/bin/pgbench -M simple -n -P 1 -f /var/lib/pgsql/bench/insert64.sql -r -c 200 -j 4 -T 60 yewudb
tps = 9849.273763 (including connections establishing)
tps = 9851.221707 (excluding connections establishing)
statement latencies in milliseconds:
0.001 \set aid random(1, 1000000)
1.577 BEGIN;
5.589 insert into tmp_hash64(id,name,insert_timestamp) values(:aid,:aid::text,now());
12.704 END;
128分区
\set aid random(1, 1000000)
BEGIN;
insert into tmp_hash128(id,name,insert_timestamp) values(:aid,:aid::text,now());
END;
/usr/pgsql-13/bin/pgbench -M simple -n -P 1 -f /var/lib/pgsql/bench/insert128.sql -r -c 200 -j 4 -T 60 yewudb
tps = 8109.773264 (including connections establishing)
tps = 8110.320924 (excluding connections establishing)
statement latencies in milliseconds:
0.001 \set aid random(1, 1000000)
1.800 BEGIN;
7.030 insert into tmp_hash128(id,name,insert_timestamp) values(:aid,:aid::text,now());
15.480 END;
256分区
\set aid random(1, 1000000)
BEGIN;
insert into tmp_hash256(id,name,insert_timestamp) values(:aid,:aid::text,now());
END;
/usr/pgsql-13/bin/pgbench -M simple -n -P 1 -f /var/lib/pgsql/bench/insert256.sql -r -c 200 -j 4 -T 60 yewudb
tps = 6923.211556 (including connections establishing)
tps = 6923.711604 (excluding connections establishing)
statement latencies in milliseconds:
0.001 \set aid random(1, 1000000)
1.722 BEGIN;
10.255 insert into tmp_hash256(id,name,insert_timestamp) values(:aid,:aid::text,now());
16.483 END;
512分区
\set aid random(1, 1000000)
BEGIN;
insert into tmp_hash512(id,name,insert_timestamp) values(:aid,:aid::text,now());
END;
/usr/pgsql-13/bin/pgbench -M simple -n -P 1 -f /var/lib/pgsql/bench/insert512.sql -r -c 200 -j 4 -T 60 yewudb
tps = 4648.734825 (including connections establishing)
tps = 4649.086852 (excluding connections establishing)
statement latencies in milliseconds:
0.001 \set aid random(1, 1000000)
1.784 BEGIN;
17.957 insert into tmp_hash512(id,name,insert_timestamp) values(:aid,:aid::text,now());
22.725 END;
1024分区
\set aid random(1, 1000000)
BEGIN;
insert into tmp_hash1024(id,name,insert_timestamp) values(:aid,:aid::text,now());
END;
/usr/pgsql-13/bin/pgbench -M simple -n -P 1 -f /var/lib/pgsql/bench/insert1024.sql -r -c 200 -j 4 -T 60 yewudb
tps = 1539.650232 (including connections establishing)
tps = 1539.769618 (excluding connections establishing)
statement latencies in milliseconds:
0.001 \set aid random(1, 1000000)
2.245 BEGIN;
42.197 insert into tmp_hash1024(id,name,insert_timestamp) values(:aid,:aid::text,now());
84.116 END;
64分区
\set aid random(1, 1000000)
BEGIN;
update tmp_hash64 set name=id where id in (:aid);
END;
/usr/pgsql-13/bin/pgbench -M simple -n -P 1 -f /var/lib/pgsql/bench/update64.sql -r -c 200 -j 4 -T 60 yewudb
tps = 10133.327855 (including connections establishing)
tps = 10133.979600 (excluding connections establishing)
statement latencies in milliseconds:
0.001 \set aid random(1, 1000000)
1.851 BEGIN;
5.357 update tmp_hash64 set name=id where id in (:aid);
12.253 END;
128分区
\set aid random(1, 1000000)
BEGIN;
update tmp_hash128 set name=id where id in (:aid);
END;
/usr/pgsql-13/bin/pgbench -M simple -n -P 1 -f /var/lib/pgsql/bench/update128.sql -r -c 200 -j 4 -T 60 yewudb
tps = 7784.243781 (including connections establishing)
tps = 7784.743137 (excluding connections establishing)
statement latencies in milliseconds:
0.001 \set aid random(1, 1000000)
1.806 BEGIN;
6.864 update tmp_hash128 set name=id where id in (:aid);
16.733 END;
256分区
\set aid random(1, 1000000)
BEGIN;
update tmp_hash256 set name=id where id in (:aid);
END;
/usr/pgsql-13/bin/pgbench -M simple -n -P 1 -f /var/lib/pgsql/bench/update256.sql -r -c 200 -j 4 -T 60 yewudb
tps = 2735.077652 (including connections establishing)
tps = 2735.268440 (excluding connections establishing)
statement latencies in milliseconds:
0.001 \set aid random(1, 1000000)
2.057 BEGIN;
11.635 update tmp_hash256 set name=id where id in (:aid);
58.887 END;
512分区
\set aid random(1, 1000000)
BEGIN;
update tmp_hash512 set name=id where id in (:aid);
END;
/usr/pgsql-13/bin/pgbench -M simple -n -P 1 -f /var/lib/pgsql/bench/update512.sql -r -c 200 -j 4 -T 60 yewudb
tps = 9494.414352 (including connections establishing)
tps = 9495.134094 (excluding connections establishing)
statement latencies in milliseconds:
0.001 \set aid random(1, 1000000)
1.997 BEGIN;
7.256 update tmp_hash512 set name=id where id in (:aid);
11.513 END;
1024分区
\set aid random(1, 1000000)
BEGIN;
update tmp_hash1024 set name=id where id in (:aid);
END;
/usr/pgsql-13/bin/pgbench -M simple -n -P 1 -f /var/lib/pgsql/bench/update1024.sql -r -c 200 -j 4 -T 60 yewudb
tps = 3657.653888 (including connections establishing)
tps = 3657.893136 (excluding connections establishing)
statement latencies in milliseconds:
0.001 \set aid random(1, 1000000)
5.194 BEGIN;
17.650 update tmp_hash1024 set name=id where id in (:aid);
31.434 END;
参考:
文章浏览阅读90次。【代码】js-选项卡原理。_选项卡js原理
文章浏览阅读67次。原型模式是一种对象创建型模式,它采用复制原型对象的方法来创建对象的实例。它创建的实例,具有与原型一样的数据结构和值分为深度克隆和浅度克隆。浅度克隆:克隆对象的值类型(基本数据类型),克隆引用类型的地址;深度克隆:克隆对象的值类型,引用类型的对象也复制一份副本。UML图:具体代码:浅度复制:import java.util.List;/*..._prototype 设计模式
文章浏览阅读59次。入选国内首批云计算服务创新发展试点城市的北京、上海、深圳、杭州和无锡起到了很好的示范作用,不仅促进了当地产业的升级换代,而且为国内其他城市发展云计算产业提供了很好的借鉴。据了解,目前国内至少有20个城市确定将云计算作为重点发展的产业。这势必会形成新一轮的云计算基础设施建设的**。由于云计算基础设施建设具有投资规模大,运维成本高,投资回收周期长,地域辐射性强等诸多特点,各地在建...
文章浏览阅读9.4k次,点赞2次,收藏20次。一、功能及目的 在每个STM32的芯片上都有两个管脚BOOT0和BOOT1,这两个管脚在芯片复位时的电平状态决定了芯片复位后从哪个区域开始执行程序。BOOT1=x BOOT0=0 // 从用户闪存启动,这是正常的工作模式。BOOT1=0 BOOT0=1 // 从系统存储器启动,这种模式启动的程序_stm32boot0和boot1作用
文章浏览阅读3.4k次,点赞2次,收藏22次。C语言函数递归调用_c语言函数递归调用
文章浏览阅读410次。明日方舟bilibili服是一款天灾驾到战斗热血的创新二次元废土风塔防手游,精妙的二次元纸片人设计,为宅友们源源不断更新超多的纸片人老婆老公们,玩家将扮演废土正义一方“罗德岛”中的指挥官,与你身边的感染者们并肩作战。与同类塔防手游与众不同的几点,首先你可以在这抽卡轻松获得稀有,同时也可以在战斗体系和敌军走位机制看到不同。明日方舟bilibili服设定:1、起因不明并四处肆虐的天灾,席卷过的土地上出..._明日方舟抽卡模拟器
文章浏览阅读437次。Maven上传Jar到私服报错:ReasonPhrase: Repository version policy: SNAPSHOT does not allow version: xxx_repository version policy snapshot does not all
文章浏览阅读1.2k次。斐波那契数列(Fibonacci Sequence)是由如下形式的一系列数字组成的:0, 1, 1, 2, 3, 5, 8, 13, 21, 34, …上述数字序列中反映出来的规律,就是下一个数字是该数字前面两个紧邻数字的和,具体如下所示:示例:比如上述斐波那契数列中的最后两个数,可以推导出34后面的数为21+34=55下面是一个更长一些的斐波那契数列:0, 1, 1, 2, 3, 5, 8, 13, 21, 34, 55, 89, 144, 233, 377, 610, 987, 1597, 2584,_斐波那契日
文章浏览阅读363次。PHP必会面试题1. 基础篇1. 用 PHP 打印出前一天的时间格式是 2017-12-28 22:21:21? //>>1.当前时间减去一天的时间,然后再格式化echo date('Y-m-d H:i:s',time()-3600*24);//>>2.使用strtotime,可以将任何字符串时间转换成时间戳,仅针对英文echo date('Y-m-d H:i:s',str..._//该层循环用来控制每轮 冒出一个数 需要比较的次数
文章浏览阅读1.3k次,点赞26次,收藏26次。windows下用mingw编译opencv貌似不支持cuda,选cuda会报错,我无法解决,所以没选cuda,下面两种编译方式支持。打开cmake gui程序,在下面两个框中分别输入opencv的源文件和编译目录,build-mingw为你创建的目录,可自定义命名。1、如果已经安装Qt,则Qt自带mingw编译器,从Qt安装目录找到编译器所在目录即可。1、如果已经安装Qt,则Qt自带cmake,从Qt安装目录找到cmake所在目录即可。2、若未安装Qt,则安装Mingw即可,参考我的另外一篇文章。_opencv mingw contrib
文章浏览阅读10w+次,点赞42次,收藏309次。今天给大家推荐5个好用且免费的简历模板网站,简洁美观,非常值得收藏!1、菜鸟图库https://www.sucai999.com/search/word/0_242_0.html?v=NTYxMjky网站主要以设计类素材为主,办公类素材也很多,简历模板大部个偏简约风,各种版式都有,而且经常会更新。最重要的是全部都能免费下载。2、个人简历网https://www.gerenjianli.com/moban/这是一个专门提供简历模板的网站,里面有超多模板个类,找起来非常方便,风格也很多样,无须注册就能免费下载,_hoso模板官网
文章浏览阅读142次。你听说过吗?该计划可让您以推广您的产品并在成功销售时支付佣金。它提供了新的营销渠道,使您的产品呈现在更广泛的受众面前并提高品牌知名度。此外,TikTok Shop联盟可以是一种经济高效的产品或服务营销方式。您只需在有人购买时付费,因此不存在在无效广告上浪费金钱的风险。这些诱人的好处是否足以让您想要开始您的TikTok Shop联盟活动?如果是这样,本指南适合您。_tiktok联盟