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;