| Last Updated | | Ratings | | Unique User Downloads | | Download Rankings |
2023-06-02 (15 days ago)  | | Not yet rated by the users | | Total: 22 This week: 1 | | All time: 10,941 This week: 185 |
|
| Description | | Author insolita
Contributor
|
This package can measure the performance of PostgreSQL JSONB fields.
It implements a tool that measures the performance of several types of operations to manipulate records of tables of a PostgreSQL database with JSONB fields.
The package can executes several types of SQL queries with JSONB table fields and outputs the results in table that appears on a terminal shell console.
Currently, it can execute SQL queries to:
- Sorting results
- Filtering results using conditions for matching records with certain JSONB field values
- Aggregate results
- Get result averages
- Etc.. | |
 |
|
Innovation award
Nominee: 14x |
|
Details
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 |
| |
Applications that use this package |
|
No pages of applications that use this class were specified.
If you know an application of this package, send a message to the author to add a link here.
Download .zip
View files (18)
composer.json
