BEGIN;
-- bigistore gin access should do index scan using gin;
-- ./spec/istore/istore_key_gin_spec.rb:23;
CREATE EXTENSION istore;
CREATE TABLE t AS
SELECT i, string_agg(j::text || '=>0', ',')::bigistore AS s
FROM
generate_series(0, 100000) AS i,
generate_series(i, i + (i % 10)) AS j
GROUP BY i;
;
CREATE INDEX ON t USING gin(s);;
SET enable_seqscan = 0;;
EXPLAIN (COSTS OFF) SELECT * FROM t WHERE s ? 3;;
             QUERY PLAN             
------------------------------------
 Bitmap Heap Scan on t
   Recheck Cond: (s ? 3)
   ->  Bitmap Index Scan on t_s_idx
         Index Cond: (s ? 3)
(4 rows)

RESET enable_seqscan;;
ROLLBACK;
BEGIN;
-- bigistore gin access should find the matching rows for key 300;
-- ./spec/istore/istore_key_gin_spec.rb:31;
CREATE EXTENSION istore;
CREATE TABLE t AS
SELECT i, string_agg(j::text || '=>0', ',')::bigistore AS s
FROM
generate_series(0, 100000) AS i,
generate_series(i, i + (i % 10)) AS j
GROUP BY i;
;
CREATE INDEX ON t USING gin(s);;
SET enable_seqscan = 0;;
SELECT s FROM t WHERE s ? 300 ORDER BY i;;
                                                           s                                                            
------------------------------------------------------------------------------------------------------------------------
 "295"=>"0", "296"=>"0", "297"=>"0", "298"=>"0", "299"=>"0", "300"=>"0"
 "296"=>"0", "297"=>"0", "298"=>"0", "299"=>"0", "300"=>"0", "301"=>"0", "302"=>"0"
 "297"=>"0", "298"=>"0", "299"=>"0", "300"=>"0", "301"=>"0", "302"=>"0", "303"=>"0", "304"=>"0"
 "298"=>"0", "299"=>"0", "300"=>"0", "301"=>"0", "302"=>"0", "303"=>"0", "304"=>"0", "305"=>"0", "306"=>"0"
 "299"=>"0", "300"=>"0", "301"=>"0", "302"=>"0", "303"=>"0", "304"=>"0", "305"=>"0", "306"=>"0", "307"=>"0", "308"=>"0"
 "300"=>"0"
(6 rows)

RESET enable_seqscan;;
ROLLBACK;
BEGIN;
-- bigistore gin access should find the matching rows for key 600;
-- ./spec/istore/istore_key_gin_spec.rb:40;
CREATE EXTENSION istore;
CREATE TABLE t AS
SELECT i, string_agg(j::text || '=>0', ',')::bigistore AS s
FROM
generate_series(0, 100000) AS i,
generate_series(i, i + (i % 10)) AS j
GROUP BY i;
;
CREATE INDEX ON t USING gin(s);;
SET enable_seqscan = 0;;
SELECT s FROM t WHERE s ? 600 ORDER BY i;;
                                                           s                                                            
------------------------------------------------------------------------------------------------------------------------
 "595"=>"0", "596"=>"0", "597"=>"0", "598"=>"0", "599"=>"0", "600"=>"0"
 "596"=>"0", "597"=>"0", "598"=>"0", "599"=>"0", "600"=>"0", "601"=>"0", "602"=>"0"
 "597"=>"0", "598"=>"0", "599"=>"0", "600"=>"0", "601"=>"0", "602"=>"0", "603"=>"0", "604"=>"0"
 "598"=>"0", "599"=>"0", "600"=>"0", "601"=>"0", "602"=>"0", "603"=>"0", "604"=>"0", "605"=>"0", "606"=>"0"
 "599"=>"0", "600"=>"0", "601"=>"0", "602"=>"0", "603"=>"0", "604"=>"0", "605"=>"0", "606"=>"0", "607"=>"0", "608"=>"0"
 "600"=>"0"
(6 rows)

RESET enable_seqscan;;
ROLLBACK;
BEGIN;
-- bigistore gin access should find the matching rows for key 900;
-- ./spec/istore/istore_key_gin_spec.rb:49;
CREATE EXTENSION istore;
CREATE TABLE t AS
SELECT i, string_agg(j::text || '=>0', ',')::bigistore AS s
FROM
generate_series(0, 100000) AS i,
generate_series(i, i + (i % 10)) AS j
GROUP BY i;
;
CREATE INDEX ON t USING gin(s);;
SET enable_seqscan = 0;;
SELECT s FROM t WHERE s ? 900 ORDER BY i;;
                                                           s                                                            
------------------------------------------------------------------------------------------------------------------------
 "895"=>"0", "896"=>"0", "897"=>"0", "898"=>"0", "899"=>"0", "900"=>"0"
 "896"=>"0", "897"=>"0", "898"=>"0", "899"=>"0", "900"=>"0", "901"=>"0", "902"=>"0"
 "897"=>"0", "898"=>"0", "899"=>"0", "900"=>"0", "901"=>"0", "902"=>"0", "903"=>"0", "904"=>"0"
 "898"=>"0", "899"=>"0", "900"=>"0", "901"=>"0", "902"=>"0", "903"=>"0", "904"=>"0", "905"=>"0", "906"=>"0"
 "899"=>"0", "900"=>"0", "901"=>"0", "902"=>"0", "903"=>"0", "904"=>"0", "905"=>"0", "906"=>"0", "907"=>"0", "908"=>"0"
 "900"=>"0"
(6 rows)

RESET enable_seqscan;;
ROLLBACK;
BEGIN;
-- istore gin access should do index scan using gin;
-- ./spec/istore/istore_key_gin_spec.rb:23;
CREATE EXTENSION istore;
CREATE TABLE t AS
SELECT i, string_agg(j::text || '=>0', ',')::istore AS s
FROM
generate_series(0, 100000) AS i,
generate_series(i, i + (i % 10)) AS j
GROUP BY i;
;
CREATE INDEX ON t USING gin(s);;
SET enable_seqscan = 0;;
EXPLAIN (COSTS OFF) SELECT * FROM t WHERE s ? 3;;
             QUERY PLAN             
------------------------------------
 Bitmap Heap Scan on t
   Recheck Cond: (s ? 3)
   ->  Bitmap Index Scan on t_s_idx
         Index Cond: (s ? 3)
(4 rows)

RESET enable_seqscan;;
ROLLBACK;
BEGIN;
-- istore gin access should find the matching rows for key 300;
-- ./spec/istore/istore_key_gin_spec.rb:31;
CREATE EXTENSION istore;
CREATE TABLE t AS
SELECT i, string_agg(j::text || '=>0', ',')::istore AS s
FROM
generate_series(0, 100000) AS i,
generate_series(i, i + (i % 10)) AS j
GROUP BY i;
;
CREATE INDEX ON t USING gin(s);;
SET enable_seqscan = 0;;
SELECT s FROM t WHERE s ? 300 ORDER BY i;;
                                                           s                                                            
------------------------------------------------------------------------------------------------------------------------
 "295"=>"0", "296"=>"0", "297"=>"0", "298"=>"0", "299"=>"0", "300"=>"0"
 "296"=>"0", "297"=>"0", "298"=>"0", "299"=>"0", "300"=>"0", "301"=>"0", "302"=>"0"
 "297"=>"0", "298"=>"0", "299"=>"0", "300"=>"0", "301"=>"0", "302"=>"0", "303"=>"0", "304"=>"0"
 "298"=>"0", "299"=>"0", "300"=>"0", "301"=>"0", "302"=>"0", "303"=>"0", "304"=>"0", "305"=>"0", "306"=>"0"
 "299"=>"0", "300"=>"0", "301"=>"0", "302"=>"0", "303"=>"0", "304"=>"0", "305"=>"0", "306"=>"0", "307"=>"0", "308"=>"0"
 "300"=>"0"
(6 rows)

RESET enable_seqscan;;
ROLLBACK;
BEGIN;
-- istore gin access should find the matching rows for key 600;
-- ./spec/istore/istore_key_gin_spec.rb:40;
CREATE EXTENSION istore;
CREATE TABLE t AS
SELECT i, string_agg(j::text || '=>0', ',')::istore AS s
FROM
generate_series(0, 100000) AS i,
generate_series(i, i + (i % 10)) AS j
GROUP BY i;
;
CREATE INDEX ON t USING gin(s);;
SET enable_seqscan = 0;;
SELECT s FROM t WHERE s ? 600 ORDER BY i;;
                                                           s                                                            
------------------------------------------------------------------------------------------------------------------------
 "595"=>"0", "596"=>"0", "597"=>"0", "598"=>"0", "599"=>"0", "600"=>"0"
 "596"=>"0", "597"=>"0", "598"=>"0", "599"=>"0", "600"=>"0", "601"=>"0", "602"=>"0"
 "597"=>"0", "598"=>"0", "599"=>"0", "600"=>"0", "601"=>"0", "602"=>"0", "603"=>"0", "604"=>"0"
 "598"=>"0", "599"=>"0", "600"=>"0", "601"=>"0", "602"=>"0", "603"=>"0", "604"=>"0", "605"=>"0", "606"=>"0"
 "599"=>"0", "600"=>"0", "601"=>"0", "602"=>"0", "603"=>"0", "604"=>"0", "605"=>"0", "606"=>"0", "607"=>"0", "608"=>"0"
 "600"=>"0"
(6 rows)

RESET enable_seqscan;;
ROLLBACK;
BEGIN;
-- istore gin access should find the matching rows for key 900;
-- ./spec/istore/istore_key_gin_spec.rb:49;
CREATE EXTENSION istore;
CREATE TABLE t AS
SELECT i, string_agg(j::text || '=>0', ',')::istore AS s
FROM
generate_series(0, 100000) AS i,
generate_series(i, i + (i % 10)) AS j
GROUP BY i;
;
CREATE INDEX ON t USING gin(s);;
SET enable_seqscan = 0;;
SELECT s FROM t WHERE s ? 900 ORDER BY i;;
                                                           s                                                            
------------------------------------------------------------------------------------------------------------------------
 "895"=>"0", "896"=>"0", "897"=>"0", "898"=>"0", "899"=>"0", "900"=>"0"
 "896"=>"0", "897"=>"0", "898"=>"0", "899"=>"0", "900"=>"0", "901"=>"0", "902"=>"0"
 "897"=>"0", "898"=>"0", "899"=>"0", "900"=>"0", "901"=>"0", "902"=>"0", "903"=>"0", "904"=>"0"
 "898"=>"0", "899"=>"0", "900"=>"0", "901"=>"0", "902"=>"0", "903"=>"0", "904"=>"0", "905"=>"0", "906"=>"0"
 "899"=>"0", "900"=>"0", "901"=>"0", "902"=>"0", "903"=>"0", "904"=>"0", "905"=>"0", "906"=>"0", "907"=>"0", "908"=>"0"
 "900"=>"0"
(6 rows)

RESET enable_seqscan;;
ROLLBACK;