智慧物流>> pgjsonb_test>> 返回
项目作者： Insolita

项目描述 ：
Test postgres jsonb performance
高级语言： PHP
项目地址: git://github.com/Insolita/pgjsonb_test.git
创建时间： 2021-02-18T12:30:14Z
项目社区：https://github.com/Insolita/pgjsonb_test
开源协议：
下载


Postgres benchmark attribute-value table vs JSONB for store user-defined forms data

[ReadOnly]

Require PHP >=7.4; composer; docker

Usage

  • clone repo
  • run make init for install dependencies and run docker
  • run make migrate for prepare database structure
  • run make seed for fill databases with same fake data
  • run make bench for run test queries across all databases

Results

Legend:

simple - stored in table (field, value)
typed - stored in table (field, value_bool, value_int, value_str, value_date… etc)
json - not indexed jsonb column
json_indx - btree indexed jsonb column

Show used queries

query type pg10 pg11 pg12 pg13
Sort
typed 0.143 0.1435 0.1246 0.1212
simple 0.1391 0.151 0.1229 0.1022
json 0.0982 0.0973 0.0949 0.0933
json_indx 0.1 0.0963 0.0949 0.0928
Multi sort
typed 0.185 0.1856 0.1655 0.1675
simple 0.1971 0.1853 0.1657 0.1382
json 0.1243 0.1148 0.1138 0.1124
json_indx 0.13 0.1156 0.1147 0.1119
Date filter
typed 0.1305 0.1356 0.112 0.1154
simple 0.1492 0.1439 0.1165 0.0931
json 0.055 0.0622 0.051 0.0542
json_indx 0.056 0.0513 0.0515 0.0517
Json filter
typed 0.1286 0.1209 0.1057 0.1073
simple 0.1417 0.1291 0.1094 0.0861
json 0.0152 0.0163 0.014 0.0174
json_indx 0.0186 0.0212 0.015 0.0143
Foreign key inject
typed 0.1748 0.0953 0.0906 0.1419
simple 0.1679 0.1653 0.1471 0.1199
json 0.1197 0.1124 0.111 0.1107
json_indx 0.1169 0.1129 0.1106 0.1094
Group by count
typed 0.146 0.1454 0.1334 0.1273
simple 0.1532 0.1489 0.1293 0.1012
json 0.0178 0.0139 0.0194 0.014
json_indx 0.0173 0.0144 0.0143 0.0142
Filters by bool,json,int sort by int
typed 0.0913 0.0919 0.0816 0.0805
simple 0.142 0.1315 0.1142 0.0993
json 0.0474 0.0399 0.0391 0.0378
json_indx 0.0462 0.04 0.0388 0.0389
Inject fk, filter and sort by date
typed 0.0396 0.034 0.0902 0.0366
simple 0.1656 0.1446 0.1269 0.1139
json 0.0472 0.0398 0.0389 0.0393
json_indx 0.0408 0.0432 0.0469 0.0391
Average from filtered
typed 0.0896 0.0783 0.0717 0.0683
simple 0.1317 0.1196 0.1034 0.0914
json 0.0246 0.0131 0.0133 0.0133
json_indx 0.0285 0.0137 0.0135 0.0132
Filter greater than average value
typed 0.1983 0.1797 0.1604 0.1597
simple 0.2658 0.2457 0.2119 0.1736
json 0.0463 0.0355 0.0351 0.0341
json_indx 0.051 0.0369 0.0353 0.0344