Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
update tests - to validate a pRest implementation
-- 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 ;
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
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
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
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
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)
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)
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
You can’t perform that action at this time.