I've added some functionality recently to be able to search a business by old UUID's, and am going to check the performance of it. So this little snippet will create a million rows in each of two tables and I'll post the results here as I get them (currently it's creating the rows, very slowly, probably because of the 4x uuid generation then indexing on the uuid column and the merged_uuids columns (and the indexes).
CREATE EXTENSION IF NOT EXISTS "uuid-ossp" WITH SCHEMA public;
COMMENT ON EXTENSION "uuid-ossp" IS 'generate universally unique identifiers (UUIDs)';
CREATE TABLE test_record_uuid (
id uuid DEFAULT uuid_generate_v4() NOT NULL,
name character varying(255),
merged_uuids character varying(255)[] DEFAULT '{}'::character varying[]
);
CREATE INDEX index_test_record_uuid_on_merged_uuids ON test_record_uuid USING gin (merged_uuids);
CREATE TABLE test_record_integer (
id integer NOT NULL,
name character varying(255),
merged_ids character varying(255)[] DEFAULT '{}'::character varying[]
);
CREATE INDEX index_test_record_integer_on_merged_ids ON test_record_integer USING gin (merged_ids);
insert into test_record_uuid (name, merged_uuids) (
select 'UuidHaver ' || i as name, ARRAY[uuid_generate_v4(), uuid_generate_v4(), uuid_generate_v4()] as merged_uuids
from (
select i
from generate_series(1,1000000) as i
)
as x );
CREATE INDEX index_test_record_uuid_on_uuid ON test_record_uuid USING btree(id);
insert into test_record_integer (id, name, merged_ids) (
select i, 'IntegerHaver ' || i as name, ARRAY[i + 1000000, i + 2000000, i + 3000000]
from (
select i
from generate_series(1,1000000) as i
)
as x );
CREATE INDEX index_test_record_integer_on_id ON test_record_integer USING btree(id);
explain analyze select * from test_record_integer where id = 500001;
-- QUERY PLAN
-- --------------------------------------------------------------------------------------------------------------------------------------------
-- Bitmap Heap Scan on test_record_integer (cost=95.17..17704.75 rows=5000 width=552) (actual time=0.055..0.056 rows=1 loops=1)
-- Recheck Cond: (id = 500001)
-- -> Bitmap Index Scan on index_test_record_integer_on_id (cost=0.00..93.92 rows=5000 width=0) (actual time=0.045..0.045 rows=1 loops=1)
-- Index Cond: (id = 500001)
-- Total runtime: 0.078 ms
-- (5 rows)
explain analyze select * from test_record_uuid where id = '3b19896e-934f-4365-b99f-a2ce63d39247';
-- QUERY PLAN
-- ---------------------------------------------------------------------------------------------------------------------------------------------------
-- Index Scan using index_test_record_uuid_on_uuid on test_record_uuid (cost=0.42..8.44 rows=1 width=176) (actual time=0.015..0.016 rows=1 loops=1)
-- Index Cond: (id = '3b19896e-934f-4365-b99f-a2ce63d39247'::uuid)
-- Total runtime: 0.036 ms
-- (3 rows)
explain analyze select * from test_record_uuid where 'cc12d33e-52ef-4bbc-83a6-d203a3b5a591' =any(merged_uuids);
-- QUERY PLAN
-- -----------------------------------------------------------------------------------------------------------------------
-- Seq Scan on test_record_uuid (cost=0.00..48685.00 rows=5000 width=176) (actual time=166.517..297.918 rows=1 loops=1)
-- Filter: ('cc12d33e-52ef-4bbc-83a6-d203a3b5a591'::text = ANY ((merged_uuids)::text[]))
-- Rows Removed by Filter: 999999
-- Total runtime: 297.938 ms
-- (4 rows)
explain analyze select * from test_record_integer where '2500001' =any(merged_ids);
-- QUERY PLAN
-- ---------------------------------------------------------------------------------------------------------------------------
-- Seq Scan on test_record_integer (cost=0.00..241421.23 rows=5000 width=552) (actual time=862.243..990.052 rows=1 loops=1)
-- Filter: ('2500001'::text = ANY ((merged_ids)::text[]))
-- Rows Removed by Filter: 1000009
-- Total runtime: 990.069 ms
-- (4 rows)