Last active
May 9, 2018 20:35
-
-
Save sebastianwebber/774f5f62e96fca406b5603eca5d6a296 to your computer and use it in GitHub Desktop.
update tests - to validate a pRest implementation
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| -- tuning used: https://www.pgconfig.org/#/tuning?total_ram=10&max_connections=100&environment_name=WEB&pg_version=10&os_type=Unix&arch=x86-64&drive_type=SSD&share_link=true | |
| -- mbp pro 15", 500gb ssd, 16gb ram, i7 2,9ghz processor | |
| \timing on | |
| \pset null ~ | |
| \set max_rows 1000 | |
| DROP TABLE IF EXISTS load_test; | |
| create table load_test (id serial primary key, name text, birth_date date, age int); | |
| \echo | |
| \echo ->>> before creating data | |
| \dt+ load_test | |
| WITH people AS ( | |
| SELECT | |
| 'Person with ' || x || ' id' as name, | |
| CURRENT_DATE - format('%s days', (300 *random())::int)::INTERVAL- format('%s years', (100 *random())::int)::INTERVAL as birth_date | |
| FROM generate_series(1,:max_rows) as x | |
| ) | |
| insert into load_test (name, birth_date) SELECT * FROM people; | |
| \echo | |
| \echo ->>> after creating data | |
| \dt+ load_test | |
| select * from load_test order by random() limit 5; | |
| \echo | |
| \echo ->>> single update | |
| update load_test | |
| set age = extract(years from age(current_date, birth_date)); | |
| vacuum (full,analyze) load_test; | |
| \echo | |
| \echo ->>> update with temp-table | |
| drop table if exists updated_data; | |
| create temporary table updated_data AS | |
| SELECT id, extract(years from age(current_date, birth_date)) as age FROM load_test; | |
| update load_test | |
| set age = updated_data.age | |
| FROM updated_data | |
| WHERE load_test.id = updated_data.id ; | |
| vacuum (full,analyze) load_test; | |
| \echo | |
| \echo ->>> update with subquery | |
| update load_test | |
| set age = updated_data.age | |
| FROM ( | |
| SELECT id, extract(years from age(current_date, birth_date)) as age FROM load_test | |
| ) AS updated_data | |
| WHERE load_test.id = updated_data.id ; |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| Timing is on. | |
| Null display is "~". | |
| DROP TABLE | |
| Time: 45.236 ms | |
| CREATE TABLE | |
| Time: 8.364 ms | |
| ->>> before creating data | |
| List of relations | |
| Schema | Name | Type | Owner | Size | Description | |
| --------+-----------+-------+-------+------------+------------- | |
| public | load_test | table | seba | 8192 bytes | | |
| (1 row) | |
| INSERT 0 1000 | |
| Time: 7.122 ms | |
| ->>> after creating data | |
| List of relations | |
| Schema | Name | Type | Owner | Size | Description | |
| --------+-----------+-------+-------+-------+------------- | |
| public | load_test | table | seba | 96 kB | | |
| (1 row) | |
| id | name | birth_date | age | |
| -----+--------------------+------------+----- | |
| 145 | Person with 145 id | 1937-01-29 | ~ | |
| 841 | Person with 841 id | 1952-12-20 | ~ | |
| 93 | Person with 93 id | 1926-03-15 | ~ | |
| 512 | Person with 512 id | 1985-04-20 | ~ | |
| 505 | Person with 505 id | 1943-03-01 | ~ | |
| (5 rows) | |
| Time: 0.490 ms | |
| ->>> single update | |
| UPDATE 1000 | |
| Time: 5.058 ms | |
| VACUUM | |
| Time: 34.801 ms | |
| ->>> update with temp-table | |
| psql:teste_prest.sql:39: NOTICE: table "updated_data" does not exist, skipping | |
| DROP TABLE | |
| Time: 0.464 ms | |
| SELECT 1000 | |
| Time: 2.065 ms | |
| UPDATE 1000 | |
| Time: 4.897 ms | |
| VACUUM | |
| Time: 24.140 ms | |
| ->>> update with subquery | |
| UPDATE 1000 | |
| Time: 5.721 ms |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| Timing is on. | |
| Null display is "~". | |
| DROP TABLE | |
| Time: 12.346 ms | |
| CREATE TABLE | |
| Time: 4.458 ms | |
| ->>> before creating data | |
| List of relations | |
| Schema | Name | Type | Owner | Size | Description | |
| --------+-----------+-------+-------+------------+------------- | |
| public | load_test | table | seba | 8192 bytes | | |
| (1 row) | |
| INSERT 0 1000 | |
| Time: 6.284 ms | |
| ->>> after creating data | |
| List of relations | |
| Schema | Name | Type | Owner | Size | Description | |
| --------+-----------+-------+-------+-------+------------- | |
| public | load_test | table | seba | 96 kB | | |
| (1 row) | |
| id | name | birth_date | age | |
| -----+--------------------+------------+----- | |
| 25 | Person with 25 id | 1990-10-16 | ~ | |
| 571 | Person with 571 id | 1923-03-16 | ~ | |
| 292 | Person with 292 id | 2008-04-26 | ~ | |
| 294 | Person with 294 id | 1993-09-03 | ~ | |
| 390 | Person with 390 id | 1940-04-29 | ~ | |
| (5 rows) | |
| Time: 0.366 ms | |
| ->>> single update | |
| UPDATE 1000 | |
| Time: 4.442 ms | |
| VACUUM | |
| Time: 32.872 ms | |
| ->>> update with temp-table | |
| DROP TABLE | |
| Time: 0.679 ms | |
| SELECT 1000 | |
| Time: 1.655 ms | |
| UPDATE 1000 | |
| Time: 4.544 ms | |
| VACUUM | |
| Time: 21.069 ms | |
| ->>> update with subquery | |
| UPDATE 1000 | |
| Time: 4.957 ms |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| Timing is on. | |
| Null display is "~". | |
| DROP TABLE | |
| Time: 5.948 ms | |
| CREATE TABLE | |
| Time: 3.363 ms | |
| ->>> before creating data | |
| List of relations | |
| Schema | Name | Type | Owner | Size | Description | |
| --------+-----------+-------+-------+------------+------------- | |
| public | load_test | table | seba | 8192 bytes | | |
| (1 row) | |
| INSERT 0 10000 | |
| Time: 48.708 ms | |
| ->>> after creating data | |
| List of relations | |
| Schema | Name | Type | Owner | Size | Description | |
| --------+-----------+-------+-------+--------+------------- | |
| public | load_test | table | seba | 624 kB | | |
| (1 row) | |
| id | name | birth_date | age | |
| ------+---------------------+------------+----- | |
| 2861 | Person with 2861 id | 1946-09-24 | ~ | |
| 2405 | Person with 2405 id | 1990-03-26 | ~ | |
| 9185 | Person with 9185 id | 1920-11-30 | ~ | |
| 7505 | Person with 7505 id | 1970-03-18 | ~ | |
| 4708 | Person with 4708 id | 1951-12-20 | ~ | |
| (5 rows) | |
| Time: 2.579 ms | |
| ->>> single update | |
| UPDATE 10000 | |
| Time: 39.009 ms | |
| VACUUM | |
| Time: 174.082 ms | |
| ->>> update with temp-table | |
| DROP TABLE | |
| Time: 0.808 ms | |
| SELECT 10000 | |
| Time: 9.218 ms | |
| UPDATE 10000 | |
| Time: 37.849 ms | |
| VACUUM | |
| Time: 159.390 ms | |
| ->>> update with subquery | |
| UPDATE 10000 | |
| Time: 42.964 ms |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| Timing is on. | |
| Null display is "~". | |
| DROP TABLE | |
| Time: 9.661 ms | |
| CREATE TABLE | |
| Time: 4.378 ms | |
| ->>> before creating data | |
| List of relations | |
| Schema | Name | Type | Owner | Size | Description | |
| --------+-----------+-------+-------+------------+------------- | |
| public | load_test | table | seba | 8192 bytes | | |
| (1 row) | |
| INSERT 0 100000 | |
| Time: 450.151 ms | |
| ->>> after creating data | |
| List of relations | |
| Schema | Name | Type | Owner | Size | Description | |
| --------+-----------+-------+-------+---------+------------- | |
| public | load_test | table | seba | 5920 kB | | |
| (1 row) | |
| id | name | birth_date | age | |
| -------+----------------------+------------+----- | |
| 85300 | Person with 85300 id | 1959-03-07 | ~ | |
| 68834 | Person with 68834 id | 1946-01-07 | ~ | |
| 74976 | Person with 74976 id | 1978-02-01 | ~ | |
| 19366 | Person with 19366 id | 1933-07-17 | ~ | |
| 18487 | Person with 18487 id | 1989-01-07 | ~ | |
| (5 rows) | |
| Time: 21.408 ms | |
| ->>> single update | |
| UPDATE 100000 | |
| Time: 600.120 ms | |
| VACUUM | |
| Time: 631.347 ms | |
| ->>> update with temp-table | |
| DROP TABLE | |
| Time: 0.757 ms | |
| SELECT 100000 | |
| Time: 89.708 ms | |
| UPDATE 100000 | |
| Time: 623.127 ms | |
| VACUUM | |
| Time: 619.942 ms | |
| ->>> update with subquery | |
| UPDATE 100000 | |
| Time: 673.500 ms |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| Timing is on. | |
| Null display is "~". | |
| DROP TABLE | |
| Time: 11.467 ms | |
| CREATE TABLE | |
| Time: 3.641 ms | |
| ->>> before creating data | |
| List of relations | |
| Schema | Name | Type | Owner | Size | Description | |
| --------+-----------+-------+-------+------------+------------- | |
| public | load_test | table | seba | 8192 bytes | | |
| (1 row) | |
| INSERT 0 1000000 | |
| Time: 4295.786 ms (00:04.296) | |
| ->>> after creating data | |
| List of relations | |
| Schema | Name | Type | Owner | Size | Description | |
| --------+-----------+-------+-------+-------+------------- | |
| public | load_test | table | seba | 57 MB | | |
| (1 row) | |
| id | name | birth_date | age | |
| --------+-----------------------+------------+----- | |
| 425674 | Person with 425674 id | 1924-04-05 | ~ | |
| 54160 | Person with 54160 id | 2011-11-03 | ~ | |
| 322174 | Person with 322174 id | 1953-09-28 | ~ | |
| 977196 | Person with 977196 id | 1941-04-20 | ~ | |
| 556145 | Person with 556145 id | 1954-08-20 | ~ | |
| (5 rows) | |
| Time: 211.116 ms | |
| ->>> single update | |
| UPDATE 1000000 | |
| Time: 4205.323 ms (00:04.205) | |
| VACUUM | |
| Time: 1981.804 ms (00:01.982) | |
| ->>> update with temp-table | |
| DROP TABLE | |
| Time: 0.737 ms | |
| SELECT 1000000 | |
| Time: 879.870 ms | |
| UPDATE 1000000 | |
| Time: 5846.397 ms (00:05.846) | |
| VACUUM | |
| Time: 2185.525 ms (00:02.186) | |
| ->>> update with subquery | |
| UPDATE 1000000 | |
| Time: 5578.465 ms (00:05.578) |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| Timing is on. | |
| Null display is "~". | |
| DROP TABLE | |
| Time: 26.548 ms | |
| CREATE TABLE | |
| Time: 3.567 ms | |
| ->>> before creating data | |
| List of relations | |
| Schema | Name | Type | Owner | Size | Description | |
| --------+-----------+-------+-------+------------+------------- | |
| public | load_test | table | seba | 8192 bytes | | |
| (1 row) | |
| INSERT 0 10000000 | |
| Time: 54710.265 ms (00:54.710) | |
| ->>> after creating data | |
| List of relations | |
| Schema | Name | Type | Owner | Size | Description | |
| --------+-----------+-------+-------+--------+------------- | |
| public | load_test | table | seba | 575 MB | | |
| (1 row) | |
| id | name | birth_date | age | |
| ---------+------------------------+------------+----- | |
| 4529787 | Person with 4529787 id | 1968-03-01 | ~ | |
| 6983383 | Person with 6983383 id | 1968-07-31 | ~ | |
| 6210697 | Person with 6210697 id | 1998-04-06 | ~ | |
| 498496 | Person with 498496 id | 1947-12-08 | ~ | |
| 5472883 | Person with 5472883 id | 1972-08-11 | ~ | |
| (5 rows) | |
| Time: 2186.550 ms (00:02.187) | |
| ->>> single update | |
| UPDATE 10000000 | |
| Time: 46211.194 ms (00:46.211) | |
| VACUUM | |
| Time: 15705.139 ms (00:15.705) | |
| ->>> update with temp-table | |
| DROP TABLE | |
| Time: 4.642 ms | |
| SELECT 10000000 | |
| Time: 9825.145 ms (00:09.825) | |
| UPDATE 10000000 | |
| Time: 251449.762 ms (04:11.450) | |
| VACUUM | |
| Time: 18587.828 ms (00:18.588) | |
| ->>> update with subquery | |
| UPDATE 10000000 | |
| Time: 60017.317 ms (01:00.017) |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| Timing is on. | |
| Null display is "~". | |
| DROP TABLE | |
| Time: 1221.571 ms (00:01.222) | |
| CREATE TABLE | |
| Time: 2.995 ms | |
| ->>> before creating data | |
| List of relations | |
| Schema | Name | Type | Owner | Size | Description | |
| --------+-----------+-------+-------+------------+------------- | |
| public | load_test | table | seba | 8192 bytes | | |
| (1 row) | |
| INSERT 0 100000000 | |
| Time: 501389.167 ms (08:21.389) | |
| ->>> after creating data | |
| List of relations | |
| Schema | Name | Type | Owner | Size | Description | |
| --------+-----------+-------+-------+---------+------------- | |
| public | load_test | table | seba | 5746 MB | | |
| (1 row) | |
| id | name | birth_date | age | |
| ----------+-------------------------+------------+----- | |
| 82805364 | Person with 82805364 id | 1981-04-23 | ~ | |
| 19509801 | Person with 19509801 id | 1996-11-04 | ~ | |
| 8486076 | Person with 8486076 id | 1983-03-08 | ~ | |
| 80171667 | Person with 80171667 id | 2011-03-18 | ~ | |
| 70871575 | Person with 70871575 id | 1970-01-25 | ~ | |
| (5 rows) | |
| Time: 31422.636 ms (00:31.423) | |
| ->>> single update | |
| UPDATE 100000000 | |
| Time: 501010.668 ms (08:21.011) | |
| VACUUM | |
| Time: 174177.416 ms (02:54.177) | |
| ->>> update with temp-table | |
| DROP TABLE | |
| Time: 7.085 ms | |
| SELECT 100000000 | |
| Time: 107133.555 ms (01:47.134) | |
| ^CCancel request sent | |
| psql:teste_prest.sql:46: ERROR: canceling statement due to user request | |
| Time: 17714557.924 ms (04:55:14.558) |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment