names.sql

\timing
\set records 1_000_000
\set repeat 10

CREATE OR REPLACE FUNCTION mk_name (i integer, d integer)
  RETURNS varchar
  AS $$
DECLARE
  separator varchar := '$';
  id integer := i % d;
  names varchar[] := '{"User","Account","Transaction","Payment","Subscription"}';
BEGIN
  RETURN names[i % array_length(names, 1) + 1] || separator || id;
END
$$
LANGUAGE plpgsql;

DROP TABLE names;

CREATE TABLE names (
  id bigserial PRIMARY KEY, name varchar NOT NULL
);

WITH series AS (
  SELECT generate_series(1, :records) s)
INSERT INTO names (name)
SELECT mk_name (s, :records / :repeat)
FROM series;

-- PLAIN --

EXPLAIN ANALYZE VERBOSE SELECT DISTINCT ON (name) name, id FROM names WHERE LOWER(name) LIKE 'pay%'       ORDER BY name, id DESC LIMIT 10;
EXPLAIN ANALYZE VERBOSE SELECT DISTINCT ON (name) name, id FROM names WHERE LOWER(name) LIKE '%pay%'      ORDER BY name, id DESC LIMIT 10;
EXPLAIN ANALYZE VERBOSE SELECT DISTINCT ON (name) name, id FROM names WHERE LOWER(name) LIKE 'kakadudu%'  ORDER BY name, id DESC LIMIT 10;
EXPLAIN ANALYZE VERBOSE SELECT DISTINCT ON (name) name, id FROM names WHERE LOWER(name) LIKE '%kakadudu%' ORDER BY name, id DESC LIMIT 10;

-- BTREE --

CREATE INDEX names_lower_name_btree on names USING BTREE (LOWER(name) varchar_pattern_ops);

EXPLAIN ANALYZE VERBOSE SELECT DISTINCT ON (name) name, id FROM names WHERE LOWER(name) LIKE 'pay%'       ORDER BY name, id DESC LIMIT 10;
EXPLAIN ANALYZE VERBOSE SELECT DISTINCT ON (name) name, id FROM names WHERE LOWER(name) LIKE '%pay%'      ORDER BY name, id DESC LIMIT 10;
EXPLAIN ANALYZE VERBOSE SELECT DISTINCT ON (name) name, id FROM names WHERE LOWER(name) LIKE 'kakadudu%'  ORDER BY name, id DESC LIMIT 10;
EXPLAIN ANALYZE VERBOSE SELECT DISTINCT ON (name) name, id FROM names WHERE LOWER(name) LIKE '%kakadudu%' ORDER BY name, id DESC LIMIT 10;

DROP INDEX IF EXISTS names_lower_name_btree;

-- GIN --

CREATE EXTENSION IF NOT EXISTS pg_trgm;

CREATE INDEX names_lower_name_gin on names USING GIN (LOWER(name) gin_trgm_ops);

EXPLAIN ANALYZE VERBOSE SELECT DISTINCT ON (name) name, id FROM names WHERE LOWER(name) LIKE 'pay%'       ORDER BY name, id DESC LIMIT 10;
EXPLAIN ANALYZE VERBOSE SELECT DISTINCT ON (name) name, id FROM names WHERE LOWER(name) LIKE '%pay%'      ORDER BY name, id DESC LIMIT 10;
EXPLAIN ANALYZE VERBOSE SELECT DISTINCT ON (name) name, id FROM names WHERE LOWER(name) LIKE 'kakadudu%'  ORDER BY name, id DESC LIMIT 10;
EXPLAIN ANALYZE VERBOSE SELECT DISTINCT ON (name) name, id FROM names WHERE LOWER(name) LIKE '%kakadudu%' ORDER BY name, id DESC LIMIT 10;

DROP INDEX IF EXISTS names_lower_name_gin;

-- GIST --

CREATE EXTENSION IF NOT EXISTS pg_trgm;

CREATE INDEX names_lower_name_gist on names USING GIST (LOWER(name) gist_trgm_ops);

EXPLAIN ANALYZE VERBOSE SELECT DISTINCT ON (name) name, id FROM names WHERE LOWER(name) LIKE 'pay%'       ORDER BY name, id DESC LIMIT 10;
EXPLAIN ANALYZE VERBOSE SELECT DISTINCT ON (name) name, id FROM names WHERE LOWER(name) LIKE '%pay%'      ORDER BY name, id DESC LIMIT 10;
EXPLAIN ANALYZE VERBOSE SELECT DISTINCT ON (name) name, id FROM names WHERE LOWER(name) LIKE 'kakadudu%'  ORDER BY name, id DESC LIMIT 10;
EXPLAIN ANALYZE VERBOSE SELECT DISTINCT ON (name) name, id FROM names WHERE LOWER(name) LIKE '%kakadudu%' ORDER BY name, id DESC LIMIT 10;

DROP INDEX IF EXISTS names_lower_name_gist;

-- BTREE + GIN --

CREATE EXTENSION IF NOT EXISTS pg_trgm;

CREATE INDEX names_lower_name_gin   on names USING GIN   (LOWER(name) gin_trgm_ops);
CREATE INDEX names_lower_name_btree on names USING BTREE (LOWER(name) varchar_pattern_ops);

EXPLAIN ANALYZE VERBOSE SELECT DISTINCT ON (name) name, id FROM names WHERE LOWER(name) LIKE 'pay%'       ORDER BY name, id DESC LIMIT 10;
EXPLAIN ANALYZE VERBOSE SELECT DISTINCT ON (name) name, id FROM names WHERE LOWER(name) LIKE '%pay%'      ORDER BY name, id DESC LIMIT 10;
EXPLAIN ANALYZE VERBOSE SELECT DISTINCT ON (name) name, id FROM names WHERE LOWER(name) LIKE 'kakadudu%'  ORDER BY name, id DESC LIMIT 10;
EXPLAIN ANALYZE VERBOSE SELECT DISTINCT ON (name) name, id FROM names WHERE LOWER(name) LIKE '%kakadudu%' ORDER BY name, id DESC LIMIT 10;

DROP INDEX IF EXISTS names_lower_name_gin;
DROP INDEX IF EXISTS names_lower_name_btree;

unique_names.sql

\timing
\set records 100_000_000
\set repeat 1

CREATE OR REPLACE FUNCTION mk_name (i integer, d integer)
  RETURNS varchar
  AS $$
DECLARE
  separator varchar := '$';
  id integer := i % d;
  names varchar[] := '{"User","Account","Transaction","Payment","Subscription"}';
BEGIN
  RETURN names[i % array_length(names, 1) + 1] || separator || id;
END
$$
LANGUAGE plpgsql;

DROP TABLE unique_names;

CREATE TABLE unique_names (
  id bigserial PRIMARY KEY, name varchar NOT NULL
);

WITH series AS (
  SELECT generate_series(1, :records) s)
INSERT INTO unique_names (name)
SELECT mk_name (s, :records / :repeat)
FROM series;

-- -- PLAIN --
-- 
-- EXPLAIN ANALYZE VERBOSE SELECT name FROM unique_names WHERE LOWER(name) LIKE 'pay%'       LIMIT 10;
-- EXPLAIN ANALYZE VERBOSE SELECT name FROM unique_names WHERE LOWER(name) LIKE '%pay%'      LIMIT 10;
-- EXPLAIN ANALYZE VERBOSE SELECT name FROM unique_names WHERE LOWER(name) LIKE 'kakadudu%'  LIMIT 10;
-- EXPLAIN ANALYZE VERBOSE SELECT name FROM unique_names WHERE LOWER(name) LIKE '%kakadudu%' LIMIT 10;
-- 
-- -- BTREE --
-- 
-- CREATE UNIQUE INDEX unique_names_lower_name_btree on unique_names USING BTREE (LOWER(name) varchar_pattern_ops);
-- 
-- EXPLAIN ANALYZE VERBOSE SELECT name FROM unique_names WHERE LOWER(name) LIKE 'pay%'       LIMIT 10;
-- EXPLAIN ANALYZE VERBOSE SELECT name FROM unique_names WHERE LOWER(name) LIKE '%pay%'      LIMIT 10;
-- EXPLAIN ANALYZE VERBOSE SELECT name FROM unique_names WHERE LOWER(name) LIKE 'kakadudu%'  LIMIT 10;
-- EXPLAIN ANALYZE VERBOSE SELECT name FROM unique_names WHERE LOWER(name) LIKE '%kakadudu%' LIMIT 10;
-- 
-- DROP INDEX unique_names_lower_name_btree;
-- 
-- -- GIN --
-- 
-- CREATE EXTENSION IF NOT EXISTS pg_trgm;
-- 
-- CREATE INDEX unique_names_lower_name_gin on unique_names USING GIN (LOWER(name) gin_trgm_ops);
-- 
-- EXPLAIN ANALYZE VERBOSE SELECT name FROM unique_names WHERE LOWER(name) LIKE 'pay%'       LIMIT 10;
-- EXPLAIN ANALYZE VERBOSE SELECT name FROM unique_names WHERE LOWER(name) LIKE '%pay%'      LIMIT 10;
-- EXPLAIN ANALYZE VERBOSE SELECT name FROM unique_names WHERE LOWER(name) LIKE 'kakadudu%'  LIMIT 10;
-- EXPLAIN ANALYZE VERBOSE SELECT name FROM unique_names WHERE LOWER(name) LIKE '%kakadudu%' LIMIT 10;
-- 
-- DROP INDEX unique_names_lower_name_gin;

-- GIN + BTREE --

CREATE EXTENSION IF NOT EXISTS pg_trgm;

CREATE UNIQUE INDEX unique_names_lower_name_btree on unique_names USING BTREE (LOWER(name) varchar_pattern_ops);
CREATE        INDEX unique_names_lower_name_gin   on unique_names USING GIN   (LOWER(name) gin_trgm_ops);

EXPLAIN ANALYZE VERBOSE SELECT name FROM unique_names WHERE LOWER(name) LIKE 'pay%'       LIMIT 10;
EXPLAIN ANALYZE VERBOSE SELECT name FROM unique_names WHERE LOWER(name) LIKE '%pay%'      LIMIT 10;
EXPLAIN ANALYZE VERBOSE SELECT name FROM unique_names WHERE LOWER(name) LIKE 'kakadudu%'  LIMIT 10;
EXPLAIN ANALYZE VERBOSE SELECT name FROM unique_names WHERE LOWER(name) LIKE '%kakadudu%' LIMIT 10;

DROP INDEX unique_names_lower_name_gin;
DROP INDEX unique_names_lower_name_btree;