\set QUIET on
SET client_min_messages = WARNING;

CREATE TEMP TABLE dict (
   word text NOT NULL
);

\echo Loading words from dictionary.
COPY dict FROM '/usr/share/dict/words';

ALTER TABLE dict ADD rand DOUBLE PRECISION;
BEGIN; UPDATE dict SET rand = RANDOM(); COMMIT;
CREATE INDEX idx_rand ON dict(rand);

CREATE TEMP TABLE try (
    citext citext,
    text   text
);

\echo Inserting into the table.
INSERT INTO try (citext)
SELECT array_to_string( ARRAY( SELECT word FROM dict WHERE rand >= (SELECT RANDOM() OFFSET 0 limit i) ORDER BY rand ASC LIMIT ceil( random() * 10 ) ), ' ' )
  FROM generate_series(1, 100000) as gen(i);

UPDATE try set text = citext;

\timing
\o /dev/null

\set QUIET off

------------------------------------------------------------------------------
\echo
\echo Test =.

\echo 'SELECT * FROM try WHERE LOWER(text) = LOWER(''food'');'
SELECT * FROM try WHERE LOWER(text) = LOWER('food');

\echo 'SELECT * FROM try WHERE citext = ''food'';'
SELECT * FROM try WHERE citext = 'food';

------------------------------------------------------------------------------
\echo
\echo Test LIKE and ILIKE

\echo 'SELECT * FROM try WHERE LOWER(text) LIKE LOWER(''C%'');'
SELECT * FROM try WHERE LOWER(text) LIKE LOWER('C%');

\echo 'SELECT * FROM try WHERE citext ILIKE ''C%'';'
SELECT * FROM try WHERE citext ILIKE 'C%';

\echo 'SELECT * FROM try WHERE citext LIKE ''C%'';'
SELECT * FROM try WHERE citext LIKE 'C%';

------------------------------------------------------------------------------
\echo
\echo Adding indexes...
\set QUIET on
\timing

CREATE INDEX idx_try_citext ON try(citext);
CREATE INDEX idx_try_text ON try(LOWER(text));

\timing
\set QUIET off

------------------------------------------------------------------------------
\echo
\echo Test =.

\echo 'SELECT * FROM try WHERE LOWER(text) = LOWER(''food'');'
SELECT * FROM try WHERE LOWER(text) = LOWER('food');

\echo 'SELECT * FROM try WHERE citext = ''food'';'
SELECT * FROM try WHERE citext = 'food';

------------------------------------------------------------------------------
\echo
\echo Test LIKE and ILIKE

\echo 'SELECT * FROM try WHERE LOWER(text) LIKE LOWER(''C%'');'
SELECT * FROM try WHERE LOWER(text) LIKE LOWER('C%');

\echo 'SELECT * FROM try WHERE citext ILIKE ''C%'';'
SELECT * FROM try WHERE citext ILIKE 'C%';

\echo 'SELECT * FROM try WHERE citext LIKE ''C%'';'
SELECT * FROM try WHERE citext LIKE 'C%';

