-
-
Save ancoron/b08ac4b1ceafda2a38ff12030c011385 to your computer and use it in GitHub Desktop.
PostgreSQL UUID extension performance testing
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
| -- FROM: https://github.com/ancoron/pg-uuid-ext | |
| CREATE EXTENSION IF NOT EXISTS uuid_ext; | |
| -- create view to determine bloat later on... | |
| CREATE OR REPLACE VIEW public.view_index_bloat AS | |
| WITH btree_index_atts AS ( | |
| SELECT pg_namespace.nspname, | |
| indexclass.relname AS index_name, | |
| indexclass.reltuples, | |
| indexclass.relpages, | |
| pg_index.indrelid, | |
| pg_index.indexrelid, | |
| indexclass.relam, | |
| tableclass.relname AS tablename, | |
| regexp_split_to_table(pg_index.indkey::text, ' '::text)::smallint AS attnum, | |
| pg_index.indexrelid AS index_oid | |
| FROM pg_index | |
| JOIN pg_class indexclass ON pg_index.indexrelid = indexclass.oid | |
| JOIN pg_class tableclass ON pg_index.indrelid = tableclass.oid | |
| JOIN pg_namespace ON pg_namespace.oid = indexclass.relnamespace | |
| JOIN pg_am ON indexclass.relam = pg_am.oid | |
| WHERE pg_am.amname = 'btree'::name AND indexclass.relpages > 0 AND (pg_namespace.nspname <> ALL (ARRAY['pg_catalog'::name, 'information_schema'::name])) | |
| ), index_item_sizes AS ( | |
| SELECT ind_atts.nspname, | |
| ind_atts.index_name, | |
| ind_atts.reltuples, | |
| ind_atts.relpages, | |
| ind_atts.relam, | |
| ind_atts.indrelid AS table_oid, | |
| ind_atts.index_oid, | |
| current_setting('block_size'::text)::numeric AS bs, | |
| 8 AS maxalign, | |
| 24 AS pagehdr, | |
| CASE | |
| WHEN max(COALESCE(pg_stats.null_frac, 0::real)) = 0::double precision THEN 2 | |
| ELSE 6 | |
| END AS index_tuple_hdr, | |
| sum((1::double precision - COALESCE(pg_stats.null_frac, 0::real)) * COALESCE(pg_stats.avg_width, 1024)::double precision) AS nulldatawidth | |
| FROM pg_attribute | |
| JOIN btree_index_atts ind_atts ON pg_attribute.attrelid = ind_atts.indexrelid AND pg_attribute.attnum = ind_atts.attnum | |
| JOIN pg_stats ON pg_stats.schemaname = ind_atts.nspname AND (pg_stats.tablename = ind_atts.tablename AND pg_stats.attname::text = pg_get_indexdef(pg_attribute.attrelid, pg_attribute.attnum::integer, true) OR pg_stats.tablename = ind_atts.index_name AND pg_stats.attname = pg_attribute.attname) | |
| WHERE pg_attribute.attnum > 0 | |
| GROUP BY ind_atts.nspname, ind_atts.index_name, ind_atts.reltuples, ind_atts.relpages, ind_atts.relam, ind_atts.indrelid, ind_atts.index_oid, (current_setting('block_size'::text)::numeric), 8::integer | |
| ), index_aligned_est AS ( | |
| SELECT index_item_sizes.maxalign, | |
| index_item_sizes.bs, | |
| index_item_sizes.nspname, | |
| index_item_sizes.index_name, | |
| index_item_sizes.reltuples, | |
| index_item_sizes.relpages, | |
| index_item_sizes.relam, | |
| index_item_sizes.table_oid, | |
| index_item_sizes.index_oid, | |
| COALESCE(ceil(index_item_sizes.reltuples * ((6 + index_item_sizes.maxalign - | |
| CASE | |
| WHEN (index_item_sizes.index_tuple_hdr % index_item_sizes.maxalign) = 0 THEN index_item_sizes.maxalign | |
| ELSE index_item_sizes.index_tuple_hdr % index_item_sizes.maxalign | |
| END)::double precision + index_item_sizes.nulldatawidth + index_item_sizes.maxalign::double precision - | |
| CASE | |
| WHEN (index_item_sizes.nulldatawidth::integer % index_item_sizes.maxalign) = 0 THEN index_item_sizes.maxalign | |
| ELSE index_item_sizes.nulldatawidth::integer % index_item_sizes.maxalign | |
| END::double precision)::numeric::double precision / (index_item_sizes.bs - index_item_sizes.pagehdr::numeric)::double precision + 1::double precision), 0::double precision) AS expected | |
| FROM index_item_sizes | |
| ), raw_bloat AS ( | |
| SELECT current_database() AS dbname, | |
| index_aligned_est.nspname, | |
| pg_class.relname AS table_name, | |
| index_aligned_est.index_name, | |
| index_aligned_est.bs * index_aligned_est.relpages::bigint::numeric AS totalbytes, | |
| index_aligned_est.expected, | |
| CASE | |
| WHEN index_aligned_est.relpages::double precision <= index_aligned_est.expected THEN 0::numeric | |
| ELSE index_aligned_est.bs * (index_aligned_est.relpages::double precision - index_aligned_est.expected)::bigint::numeric | |
| END AS wastedbytes, | |
| CASE | |
| WHEN index_aligned_est.relpages::double precision <= index_aligned_est.expected THEN 0::numeric | |
| ELSE index_aligned_est.bs * (index_aligned_est.relpages::double precision - index_aligned_est.expected)::bigint::numeric * 100::numeric / (index_aligned_est.bs * index_aligned_est.relpages::bigint::numeric) | |
| END AS realbloat, | |
| pg_relation_size(index_aligned_est.table_oid::regclass) AS table_bytes, | |
| stat.idx_scan AS index_scans | |
| FROM index_aligned_est | |
| JOIN pg_class ON pg_class.oid = index_aligned_est.table_oid | |
| JOIN pg_stat_user_indexes stat ON index_aligned_est.index_oid = stat.indexrelid | |
| ) | |
| SELECT raw_bloat.dbname AS database_name, | |
| raw_bloat.nspname AS schema_name, | |
| raw_bloat.table_name, | |
| raw_bloat.index_name, | |
| round(raw_bloat.realbloat) AS bloat_pct, | |
| round(raw_bloat.wastedbytes / (1024::double precision ^ 2::double precision)::numeric) AS bloat_mb, | |
| round(raw_bloat.totalbytes / (1024::double precision ^ 2::double precision)::numeric, 3) AS index_mb, | |
| round(raw_bloat.table_bytes::numeric / (1024::double precision ^ 2::double precision)::numeric, 3) AS table_mb, | |
| raw_bloat.index_scans | |
| FROM raw_bloat | |
| ; | |
| \timing | |
| \pset pager | |
| CREATE TABLE uuid_v1 (id uuid PRIMARY KEY USING INDEX TABLESPACE faster) TABLESPACE fast; | |
| CREATE TABLE uuid_serial (id uuid PRIMARY KEY USING INDEX TABLESPACE faster) TABLESPACE fast; | |
| -- additional timestamp index... | |
| CREATE TABLE uuid_v1_timestamp (id uuid PRIMARY KEY USING INDEX TABLESPACE faster) TABLESPACE fast; | |
| CREATE INDEX idx_uuid_v1_timestamp ON uuid_v1_timestamp (uuid_v1_timestamp(id)) TABLESPACE faster; | |
| -- timestamp-only index... | |
| CREATE TABLE uuid_v1_ext (id uuid) TABLESPACE fast; | |
| CREATE UNIQUE INDEX idx_uuid_v1_ext ON uuid_v1_ext (id uuid_timestamp_ops) TABLESPACE faster; | |
| -- FROM: https://github.com/ancoron/java-uuid-serial | |
| -- mvn clean install -Duuids=30000000 -Dinterval_days=30 -Doffset_days=92 -Duuid.historic=true -Duuid.skip.v1=false -Dnodes=9 | |
| -- INSERT... | |
| COPY uuid_v1 FROM '/srv/dev/github/ancoron/uuid-serial/target/uuids.v1.historic.txt' WITH ( FORMAT text ); | |
| COPY uuid_v1_timestamp FROM '/srv/dev/github/ancoron/uuid-serial/target/uuids.v1.historic.txt' WITH ( FORMAT text ); | |
| COPY uuid_serial FROM '/srv/dev/github/ancoron/uuid-serial/target/uuids.serial.historic.txt' WITH ( FORMAT text ); | |
| COPY uuid_v1_ext FROM '/srv/dev/github/ancoron/uuid-serial/target/uuids.v1.historic.txt' WITH ( FORMAT text ); | |
| Table: uuid_v1 Time: 110832.396 ms (01:50.832) | |
| Table: uuid_v1_timestamp Time: 134781.241 ms (02:14.781) | |
| Table: uuid_serial Time: 53478.910 ms (00:53.479) | |
| Table: uuid_v1_ext Time: 52265.123 ms (00:52.265) | |
| ANALYZE uuid_v1; | |
| ANALYZE uuid_v1_timestamp; | |
| ANALYZE uuid_serial; | |
| ANALYZE uuid_v1_ext; | |
| SELECT table_name, concat(bloat_mb, ' MiB (', bloat_pct, ' %)') AS bloat, index_mb, table_mb FROM view_index_bloat WHERE index_name LIKE '%uuid%' ORDER BY index_name DESC; | |
| table_name | bloat | index_mb | table_mb | |
| -------------------+----------------+----------+---------- | |
| uuid_v1_timestamp | 318 MiB (28 %) | 1121.688 | 1266.898 | |
| uuid_v1 | 318 MiB (28 %) | 1121.688 | 1266.898 | |
| uuid_serial | 99 MiB (11 %) | 902.438 | 1266.898 | |
| uuid_v1_ext | 99 MiB (11 %) | 902.438 | 1266.898 | |
| -- FROM: https://github.com/ancoron/java-uuid-serial | |
| -- mvn clean install -Duuids=92000000 -Dinterval_days=92 -Duuid.historic=true -Duuid.skip.v1=false -Dnodes=9 | |
| -- INSERT... | |
| COPY uuid_v1 FROM '/srv/dev/github/ancoron/uuid-serial/target/uuids.v1.historic.txt' WITH ( FORMAT text ); | |
| COPY uuid_v1_timestamp FROM '/srv/dev/github/ancoron/uuid-serial/target/uuids.v1.historic.txt' WITH ( FORMAT text ); | |
| COPY uuid_serial FROM '/srv/dev/github/ancoron/uuid-serial/target/uuids.serial.historic.txt' WITH ( FORMAT text ); | |
| COPY uuid_v1_ext FROM '/srv/dev/github/ancoron/uuid-serial/target/uuids.v1.historic.txt' WITH ( FORMAT text ); | |
| Table: uuid_v1 Time: 452813.140 ms (07:32.813) | |
| Table: uuid_v1_timestamp Time: 586131.623 ms (09:46.132) | |
| Table: uuid_serial Time: 163821.904 ms (02:43.822) | |
| Table: uuid_v1_ext Time: 162702.094 ms (02:42.702) | |
| ANALYZE uuid_v1; | |
| ANALYZE uuid_v1_timestamp; | |
| ANALYZE uuid_serial; | |
| ANALYZE uuid_v1_ext; | |
| SELECT table_name, concat(bloat_mb, ' MiB (', bloat_pct, ' %)') AS bloat, index_mb, table_mb FROM view_index_bloat WHERE index_name LIKE '%uuid%' ORDER BY index_name DESC; | |
| table_name | bloat | index_mb | table_mb | |
| -------------------+-----------------+----------+---------- | |
| uuid_v1_timestamp | 1380 MiB (30 %) | 4647.188 | 5152.031 | |
| uuid_v1 | 1380 MiB (30 %) | 4647.188 | 5152.031 | |
| uuid_serial | 402 MiB (11 %) | 3669.820 | 5152.031 | |
| uuid_v1_ext | 402 MiB (11 %) | 3669.820 | 5152.031 | |
| SET timezone TO 'Zulu'; | |
| -- fetch first UUID via (from uuid-serial project directory): | |
| -- (V1) head -1 target/uuids.v1.historic.txt | |
| -- ...and extract timestamp in psql: SELECT uuid_v1_timestamp('<uuid>'); | |
| -- (serial) head -1 target/uuids.serial.historic.txt | |
| EXPLAIN (ANALYZE, BUFFERS, VERBOSE) DELETE FROM uuid_v1 WHERE uuid_v1_timestamp(id) < '2019-04-04 07:43:11.3776'; | |
| EXPLAIN (ANALYZE, BUFFERS, VERBOSE) DELETE FROM uuid_v1_timestamp WHERE uuid_v1_timestamp(id) < '2019-04-04 07:43:11.3776'; | |
| EXPLAIN (ANALYZE, BUFFERS, VERBOSE) DELETE FROM uuid_serial WHERE id < '1e956ad4-bdf6-1f81-b64c-e03f49ce78f3'; | |
| EXPLAIN (ANALYZE, BUFFERS, VERBOSE) DELETE FROM uuid_v1_ext WHERE id <* '4bdf6f81-56ad-11e9-8258-e03f49ce78f3'; | |
| Table: uuid_v1 Time: 55506.443 ms (00:55.506) | |
| Table: uuid_v1_timestamp Time: 29444.735 ms (00:29.445) | |
| Table: uuid_serial Time: 29549.502 ms (00:29.550) | |
| Table: uuid_v1_ext Time: 56704.766 ms (00:56.705) | |
| ANALYZE uuid_v1; | |
| ANALYZE uuid_v1_timestamp; | |
| ANALYZE uuid_serial; | |
| ANALYZE uuid_v1_ext; | |
| SELECT table_name, concat(bloat_mb, ' MiB (', bloat_pct, ' %)') AS bloat, index_mb, table_mb FROM view_index_bloat WHERE index_name LIKE '%uuid%' ORDER BY index_name DESC; | |
| table_name | bloat | index_mb | table_mb | |
| -------------------+-----------------+----------+---------- | |
| uuid_v1_timestamp | 2309 MiB (48 %) | 4771.461 | 5152.031 | |
| uuid_v1 | 2313 MiB (48 %) | 4771.461 | 5152.031 | |
| uuid_serial | 1211 MiB (33 %) | 3669.820 | 5152.031 | |
| uuid_v1_ext | 1207 MiB (33 %) | 3669.820 | 5152.031 | |
| VACUUM uuid_v1; | |
| VACUUM uuid_v1_timestamp; | |
| VACUUM uuid_serial; | |
| VACUUM uuid_v1_ext; | |
| Table: uuid_v1 Time: 227589.726 ms (03:47.590) | |
| Table: uuid_v1_timestamp Time: 180032.356 ms (03:00.032) | |
| Table: uuid_serial Time: 46443.304 ms (00:46.443) | |
| Table: uuid_v1_ext Time: 77573.628 ms (01:17.574) | |
| SELECT table_name, concat(bloat_mb, ' MiB (', bloat_pct, ' %)') AS bloat, index_mb, table_mb FROM view_index_bloat WHERE index_name LIKE '%uuid%' ORDER BY index_name DESC; | |
| table_name | bloat | index_mb | table_mb | |
| -------------------+-----------------+----------+---------- | |
| uuid_v1_timestamp | 2308 MiB (48 %) | 4771.461 | 5152.031 | |
| uuid_v1 | 2308 MiB (48 %) | 4771.461 | 5152.031 | |
| uuid_serial | 1206 MiB (33 %) | 3669.820 | 5152.031 | |
| uuid_v1_ext | 1206 MiB (33 %) | 3669.820 | 5152.031 | |
| REINDEX TABLE uuid_v1; | |
| REINDEX TABLE uuid_v1_timestamp; | |
| REINDEX TABLE uuid_serial; | |
| REINDEX TABLE uuid_v1_ext; | |
| Table: uuid_v1 Time: 50557.075 ms (00:50.557) | |
| Table: uuid_v1_timestamp Time: 77491.671 ms (01:17.492) | |
| Table: uuid_serial Time: 31734.116 ms (00:31.734) | |
| Table: uuid_v1_ext Time: 39235.986 ms (00:39.236) | |
| DROP TABLE uuid_v1; | |
| DROP TABLE uuid_v1_timestamp; | |
| DROP TABLE uuid_serial; | |
| DROP TABLE uuid_v1_ext; |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment