-- Simple SQL script to set up test data for looking at 
-- modinvstor-215, the search problem with instances.

-- This script creates records with random data, and makes two queries that differ only
-- by the search term. One goes pretty fast, the other is way too slow. And
-- will be slower once we get to tens of millions of records.

\echo Create instance table
DROP TABLE IF EXISTS instance;
CREATE TABLE instance ( jsonb JSONB NOT NULL );

\echo Insert random data
SELECT setseed(0);  -- Repeatable random() values 
INSERT INTO instance SELECT json_build_object 
  ('title', generate_series(1, 5000000) || '', 'id', random() || '');

\echo Create indexes
CREATE INDEX title_idx ON instance ((jsonb->>'title'));
CREATE INDEX id_idx_ft ON instance USING GIN ( to_tsvector('simple', ((jsonb->>'id')) ));
\echo

\echo Selecting many records
select * from instance 
  WHERE to_tsvector('simple', instance.jsonb->>'id') @@ to_tsquery('simple','0.5:*') 
  ORDER BY instance.jsonb->>'title'
  LIMIT 30 OFFSET 0;
explain analyze select * from instance     
  WHERE to_tsvector('simple', instance.jsonb->>'id') @@ to_tsquery('simple','0.5:*') 
  ORDER BY instance.jsonb->>'title'
  LIMIT 30 OFFSET 0;
-- This goes fast, as it skips only 299 records, and the index scan gets things in the
-- right order, so we can stop scanning as soon as we have the 30 records we want.

\echo
\echo Selecting just a few records
select * from instance     
  WHERE to_tsvector('simple', instance.jsonb->>'id') @@ to_tsquery('simple','0.5555555:*') 
  ORDER BY instance.jsonb->>'title'
  LIMIT 30 OFFSET 0;
explain analyze select * from instance     
  WHERE to_tsvector('simple', instance.jsonb->>'id') @@ to_tsquery('simple','0.5555555:*') 
  ORDER BY instance.jsonb->>'title'
  LIMIT 30 OFFSET 0;
\echo
-- This takes a long time, because we have to scan the whole index through, hoping to find 
-- more records. The filter removes 4999999 records, leaving only one.