SQLize Online / PHPize Online  /  SQLtest Online

CREATE EXTENSION IF NOT EXISTS citext; RESET ALL; SET max_parallel_workers_per_gather = 0; SET work_mem = '256MB'; DROP TABLE IF EXISTS test_c_collation, test_usual, test_icu, test_insensitive, test_citext; CREATE COLLATION IF NOT EXISTS case_insensitive ( provider = icu, locale = 'und-u-ks-level2', deterministic = false ); CREATE OR REPLACE FUNCTION random_text(length integer) RETURNS text LANGUAGE sql AS $function$ WITH r(n, rn) AS ( SELECT n, (random() * 51)::int FROM generate_series(1, $1) AS g(n) ) SELECT string_agg(chr(ascii('A') + CASE WHEN rn < 26 THEN rn ELSE rn + 6 END), '' ORDER BY n) FROM r; $function$; CREATE UNLOGGED TABLE test_c_collation ( id bigint GENERATED ALWAYS AS IDENTITY, val text NOT NULL COLLATE "C" ); INSERT INTO test_c_collation(val) SELECT random_text(40) FROM generate_series(1, 10000) AS g(n); ALTER TABLE test_c_collation ADD PRIMARY KEY (id); CREATE UNLOGGED TABLE test_usual ( id bigint GENERATED ALWAYS AS IDENTITY, val text NOT NULL ); INSERT INTO test_usual(val) SELECT val FROM test_c_collation; ALTER TABLE test_usual ADD PRIMARY KEY (id); CREATE UNLOGGED TABLE test_icu ( id bigint GENERATED ALWAYS AS IDENTITY, val text NOT NULL COLLATE "ru-x-icu" ); INSERT INTO test_icu(val) SELECT val FROM test_c_collation; ALTER TABLE test_icu ADD PRIMARY KEY (id); CREATE UNLOGGED TABLE test_insensitive ( id bigint GENERATED ALWAYS AS IDENTITY, val text NOT NULL COLLATE "case_insensitive" ); INSERT INTO test_insensitive(val) SELECT val FROM test_c_collation; ALTER TABLE test_insensitive ADD PRIMARY KEY (id); CREATE UNLOGGED TABLE test_citext ( id bigint GENERATED ALWAYS AS IDENTITY, val citext NOT NULL ); INSERT INTO test_citext(val) SELECT val FROM test_c_collation; ALTER TABLE test_citext ADD PRIMARY KEY (id); -------------------------------------------------------------------------------- VACUUM ANALYZE test_c_collation, test_usual, test_icu, test_insensitive, test_citext; -------------------------------------------------------------------------------- SELECT d.datname AS "Name", pg_catalog.pg_encoding_to_char(d.encoding) AS "Encoding", d.datcollate AS "Collate", d.datctype AS "Ctype", d.daticulocale AS "ICU Locale", CASE d.datlocprovider WHEN 'c' THEN 'libc' WHEN 'i' THEN 'icu' END AS "Locale Provider" FROM pg_catalog.pg_database AS d WHERE d.datname = current_database(); -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- DO $do$ BEGIN PERFORM set_config('my.start', clock_timestamp()::text, false); PERFORM * FROM test_c_collation ORDER BY val; END; $do$; SELECT EXTRACT(millisecond FROM clock_timestamp() - current_setting('my.start')::timestamptz) AS milliseconds_elapsed; -------------------------------------------------------------------------------- DO $do$ BEGIN PERFORM set_config('my.start', clock_timestamp()::text, false); PERFORM * FROM test_usual ORDER BY val; END; $do$; SELECT EXTRACT(millisecond FROM clock_timestamp() - current_setting('my.start')::timestamptz) AS milliseconds_elapsed; -------------------------------------------------------------------------------- DO $do$ BEGIN PERFORM set_config('my.start', clock_timestamp()::text, false); PERFORM * FROM test_icu ORDER BY val; END; $do$; SELECT EXTRACT(millisecond FROM clock_timestamp() - current_setting('my.start')::timestamptz) AS milliseconds_elapsed; -------------------------------------------------------------------------------- DO $do$ BEGIN PERFORM set_config('my.start', clock_timestamp()::text, false); PERFORM * FROM test_insensitive ORDER BY val; END; $do$; SELECT EXTRACT(millisecond FROM clock_timestamp() - current_setting('my.start')::timestamptz) AS milliseconds_elapsed; -------------------------------------------------------------------------------- DO $do$ BEGIN PERFORM set_config('my.start', clock_timestamp()::text, false); PERFORM * FROM test_citext ORDER BY val; END; $do$; SELECT EXTRACT(millisecond FROM clock_timestamp() - current_setting('my.start')::timestamptz) AS milliseconds_elapsed; -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- DROP TABLE IF EXISTS test_search_c, test_search_usual, test_search_icu, test_search_insensitive, test_search_citext; CREATE UNLOGGED TABLE test_search_c ( val text NOT NULL COLLATE "C" ); INSERT INTO test_search_c(val) SELECT val FROM test_c_collation LIMIT 5000; INSERT INTO test_search_c(val) SELECT random_text(40) FROM generate_series(1, 5000) AS g(n); CREATE UNLOGGED TABLE test_search_usual ( val text NOT NULL ); INSERT INTO test_search_usual(val) SELECT val FROM test_search_c; CREATE UNLOGGED TABLE test_search_icu ( val text NOT NULL COLLATE "ru-x-icu" ); INSERT INTO test_search_icu(val) SELECT val FROM test_search_c; CREATE UNLOGGED TABLE test_search_insensitive ( val text NOT NULL COLLATE "case_insensitive" ); INSERT INTO test_search_insensitive(val) SELECT val FROM test_search_c; CREATE UNLOGGED TABLE test_search_citext ( val citext NOT NULL ); INSERT INTO test_search_citext(val) SELECT val FROM test_search_c; -------------------------------------------------------------------------------- VACUUM ANALYZE test_search_c, test_search_usual, test_search_icu, test_search_insensitive, test_search_citext; -------------------------------------------------------------------------------- DO $do$ BEGIN PERFORM set_config('my.start', clock_timestamp()::text, false); PERFORM * FROM test_search_c AS s WHERE EXISTS ( SELECT 1 FROM test_c_collation AS t WHERE t.val = s.val ); END; $do$; SELECT EXTRACT(millisecond FROM clock_timestamp() - current_setting('my.start')::timestamptz) AS milliseconds_elapsed; DO $do$ BEGIN PERFORM set_config('my.start', clock_timestamp()::text, false); PERFORM * FROM test_search_usual AS s WHERE EXISTS ( SELECT 1 FROM test_usual AS t WHERE t.val = s.val ); END; $do$; SELECT EXTRACT(millisecond FROM clock_timestamp() - current_setting('my.start')::timestamptz) AS milliseconds_elapsed; DO $do$ BEGIN PERFORM set_config('my.start', clock_timestamp()::text, false); PERFORM * FROM test_search_icu AS s WHERE EXISTS ( SELECT 1 FROM test_icu AS t WHERE t.val = s.val ); END; $do$; SELECT EXTRACT(millisecond FROM clock_timestamp() - current_setting('my.start')::timestamptz) AS milliseconds_elapsed; DO $do$ BEGIN PERFORM set_config('my.start', clock_timestamp()::text, false); PERFORM * FROM test_search_insensitive AS s WHERE EXISTS ( SELECT 1 FROM test_insensitive AS t WHERE t.val = s.val ); END; $do$; SELECT EXTRACT(millisecond FROM clock_timestamp() - current_setting('my.start')::timestamptz) AS milliseconds_elapsed; DO $do$ BEGIN PERFORM set_config('my.start', clock_timestamp()::text, false); PERFORM * FROM test_search_citext AS s WHERE EXISTS ( SELECT 1 FROM test_citext AS t WHERE t.val = s.val ); END; $do$; SELECT EXTRACT(millisecond FROM clock_timestamp() - current_setting('my.start')::timestamptz) AS milliseconds_elapsed; -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- --- The same, but with indexes: CREATE INDEX ON test_c_collation(val); CREATE INDEX ON test_usual(val); CREATE INDEX ON test_icu(val); CREATE INDEX ON test_insensitive(val); CREATE INDEX ON test_citext(val); SET enable_hashjoin = off; SET enable_sort = off; -------------------------------------------------------------------------------- DO $do$ BEGIN PERFORM set_config('my.start', clock_timestamp()::text, false); PERFORM * FROM test_search_c AS s WHERE EXISTS ( SELECT 1 FROM test_c_collation AS t WHERE t.val = s.val ); END; $do$; SELECT EXTRACT(millisecond FROM clock_timestamp() - current_setting('my.start')::timestamptz) AS milliseconds_elapsed; DO $do$ BEGIN PERFORM set_config('my.start', clock_timestamp()::text, false); PERFORM * FROM test_search_usual AS s WHERE EXISTS ( SELECT 1 FROM test_usual AS t WHERE t.val = s.val ); END; $do$; SELECT EXTRACT(millisecond FROM clock_timestamp() - current_setting('my.start')::timestamptz) AS milliseconds_elapsed; DO $do$ BEGIN PERFORM set_config('my.start', clock_timestamp()::text, false); PERFORM * FROM test_search_icu AS s WHERE EXISTS ( SELECT 1 FROM test_icu AS t WHERE t.val = s.val ); END; $do$; SELECT EXTRACT(millisecond FROM clock_timestamp() - current_setting('my.start')::timestamptz) AS milliseconds_elapsed; DO $do$ BEGIN PERFORM set_config('my.start', clock_timestamp()::text, false); PERFORM * FROM test_search_insensitive AS s WHERE EXISTS ( SELECT 1 FROM test_insensitive AS t WHERE t.val = s.val ); END; $do$; SELECT EXTRACT(millisecond FROM clock_timestamp() - current_setting('my.start')::timestamptz) AS milliseconds_elapsed; DO $do$ BEGIN PERFORM set_config('my.start', clock_timestamp()::text, false); PERFORM * FROM test_search_citext AS s WHERE EXISTS ( SELECT 1 FROM test_citext AS t WHERE t.val = s.val ); END; $do$; SELECT EXTRACT(millisecond FROM clock_timestamp() - current_setting('my.start')::timestamptz) AS milliseconds_elapsed;