CREATE SCHEMA "PGQS";
CREATE EXTENSION pg_qualstats WITH SCHEMA "PGQS";
-- Make sure that installcheck won't find previous data
SELECT "PGQS".pg_qualstats_reset();
 pg_qualstats_reset 
--------------------
 
(1 row)

-- Make sure sure we'll see at least one qual
SET pg_qualstats.sample_rate = 1;
CREATE TABLE pgqs AS SELECT id, 'a'::text val FROM generate_series(1, 100) id;
SELECT COUNT(*) FROM pgqs WHERE id = 1;
 count 
-------
     1
(1 row)

SELECT lrelid::regclass::text, lattnum, occurences, execution_count,
    nbfiltered, constvalue, eval_type
FROM "PGQS".pg_qualstats;
 lrelid | lattnum | occurences | execution_count | nbfiltered | constvalue | eval_type 
--------+---------+------------+-----------------+------------+------------+-----------
 pgqs   |       1 |          1 |             100 |         99 | 1::integer | f
(1 row)

SELECT COUNT(*) > 0 FROM "PGQS".pg_qualstats;
 ?column? 
----------
 t
(1 row)

SELECT COUNT(*) > 0 FROM "PGQS".pg_qualstats();
 ?column? 
----------
 t
(1 row)

SELECT COUNT(*) > 0 FROM "PGQS".pg_qualstats_example_queries();
 ?column? 
----------
 t
(1 row)

SELECT "PGQS".pg_qualstats_reset();
 pg_qualstats_reset 
--------------------
 
(1 row)

SELECT COUNT(*) FROM "PGQS".pg_qualstats();
 count 
-------
     0
(1 row)

-- OpExpr sanity checks
-- subquery_var operator const, shouldn't be tracked
SELECT * FROM (SELECT * FROM pgqs LIMIT 0) pgqs WHERE pgqs.id = 0;
 id | val 
----+-----
(0 rows)

SELECT COUNT(*) FROM "PGQS".pg_qualstats();
 count 
-------
     0
(1 row)

-- const non_commutable_operator var, should be tracked, var found on RHS
SELECT * FROM pgqs WHERE 'meh' ~ val;
 id | val 
----+-----
(0 rows)

SELECT lrelid::regclass, lattnum, rrelid::regclass, rattnum FROM "PGQS".pg_qualstats();
 lrelid | lattnum | rrelid | rattnum 
--------+---------+--------+---------
        |         | pgqs   |       2
(1 row)

SELECT "PGQS".pg_qualstats_reset();
 pg_qualstats_reset 
--------------------
 
(1 row)

-- opexpr operator var and commuted, shouldn't be tracked
SELECT * FROM pgqs WHERE id % 2 = 3;
 id | val 
----+-----
(0 rows)

SELECT * FROM pgqs WHERE 3 = id % 2;
 id | val 
----+-----
(0 rows)

SELECT COUNT(*) FROM "PGQS".pg_qualstats();
 count 
-------
     0
(1 row)

-- same query with handled commuted qual, which should be found as identical
SELECT * FROM pgqs WHERE id = 0;
 id | val 
----+-----
(0 rows)

SELECT * FROM pgqs WHERE 0 = id;
 id | val 
----+-----
(0 rows)

SELECT lrelid::regclass, lattnum, rrelid::regclass, rattnum, sum(occurences)
FROM "PGQS".pg_qualstats()
GROUP by 1, 2, 3, 4;
 lrelid | lattnum | rrelid | rattnum | sum 
--------+---------+--------+---------+-----
 pgqs   |       1 |        |         |   2
(1 row)

SELECT COUNT(DISTINCT qualnodeid) FROM "PGQS".pg_qualstats();
 count 
-------
     1
(1 row)

-- (unique)qualid behavior
SELECT "PGQS".pg_qualstats_reset();
 pg_qualstats_reset 
--------------------
 
(1 row)

-- There should be one group of 2 AND-ed quals, and 1 qual alone
SELECT COUNT(*) FROM pgqs WHERE (id = 1) OR (id > 10 AND id < 20);
 count 
-------
    10
(1 row)

SELECT CASE WHEN qualid IS NULL THEN 'OR-ed' ELSE 'AND-ed' END kind, COUNT(*)
FROM "PGQS".pg_qualstats() GROUP BY 1 ORDER BY 2 DESC;
  kind  | count 
--------+-------
 AND-ed |     2
 OR-ed  |     1
(2 rows)

----------------
-- index advisor
----------------
-- check that empty arrays are returned rather than NULL values
SELECT "PGQS".pg_qualstats_reset();
 pg_qualstats_reset 
--------------------
 
(1 row)

SELECT * FROM "PGQS".pg_qualstats_index_advisor(50);
      pg_qualstats_index_advisor      
--------------------------------------
 {"indexes" : [], "unoptimised" : []}
(1 row)

-- Test some naive scenario
CREATE TABLE adv (id1 integer, id2 integer, id3 integer, val text);
INSERT INTO adv SELECT i, i, i, 'line ' || i from generate_series(1, 1000) i;
SELECT "PGQS".pg_qualstats_reset();
 pg_qualstats_reset 
--------------------
 
(1 row)

SELECT * FROM adv WHERE id1 < 0;
 id1 | id2 | id3 | val 
-----+-----+-----+-----
(0 rows)

SELECT count(*) FROM adv WHERE id1 < 500;
 count 
-------
   499
(1 row)

SELECT * FROM adv WHERE val = 'meh';
 id1 | id2 | id3 | val 
-----+-----+-----+-----
(0 rows)

SELECT * FROM adv WHERE id1 = 0 and val = 'meh';
 id1 | id2 | id3 | val 
-----+-----+-----+-----
(0 rows)

SELECT * FROM adv WHERE id1 = 1 and val = 'meh';
 id1 | id2 | id3 | val 
-----+-----+-----+-----
(0 rows)

SELECT * FROM adv WHERE id1 = 1 and id2 = 2 AND val = 'meh';
 id1 | id2 | id3 | val 
-----+-----+-----+-----
(0 rows)

SELECT * FROM adv WHERE id1 = 6 and id2 = 6 AND id3 = 6 AND val = 'meh';
 id1 | id2 | id3 | val 
-----+-----+-----+-----
(0 rows)

SELECT COUNT(*) FROM pgqs WHERE id = 1;
 count 
-------
     1
(1 row)

-- non optimisable statements
SELECT * FROM adv WHERE val ILIKE 'moh';
 id1 | id2 | id3 | val 
-----+-----+-----+-----
(0 rows)

SELECT count(*) FROM adv WHERE val ILIKE 'moh';
 count 
-------
     0
(1 row)

SELECT * FROM adv WHERE val LIKE 'moh';
 id1 | id2 | id3 | val 
-----+-----+-----+-----
(0 rows)

-- check the results
SELECT v->'ddl' AS v
  FROM json_array_elements(
    "PGQS".pg_qualstats_index_advisor(50)->'indexes') v
  ORDER BY v::text COLLATE "C";
                               v                               
---------------------------------------------------------------
 "CREATE INDEX ON public.adv USING btree (id1)"
 "CREATE INDEX ON public.adv USING btree (val, id1, id2, id3)"
 "CREATE INDEX ON public.pgqs USING btree (id)"
(3 rows)

SELECT v->'qual' AS v
  FROM json_array_elements(
    "PGQS".pg_qualstats_index_advisor(50)->'unoptimised') v
  ORDER BY v::text COLLATE "C";
        v        
-----------------
 "adv.val ~~ ?"
 "adv.val ~~* ?"
(2 rows)

-- check quals on removed table
DROP TABLE pgqs;
SELECT v->'ddl' AS v
  FROM json_array_elements(
    "PGQS".pg_qualstats_index_advisor(50)->'indexes') v
  ORDER BY v::text COLLATE "C";
                               v                               
---------------------------------------------------------------
 "CREATE INDEX ON public.adv USING btree (id1)"
 "CREATE INDEX ON public.adv USING btree (val, id1, id2, id3)"
(2 rows)