CREATE EXTENSION pg_track_optimizer;

-- Cleanup history of previous tests
SELECT * FROM pg_track_optimizer_reset();

-- Test flushing zero hash table
SELECT * FROM pg_track_optimizer_flush();

CREATE TABLE pto_test(x integer, y integer, z integer);
ANALYZE pto_test;

EXPLAIN (COSTS OFF) SELECT * FROM pto_test WHERE x < 1;
SELECT
  query, avg_error -> 'mean' >= 0., evaluated_nodes, plan_nodes,
  exec_time -> 'mean' > 0., nexecs
FROM pg_track_optimizer()
ORDER BY query COLLATE "C"; -- Nothing to track for plain explain.

EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF, SUMMARY OFF)
SELECT * FROM pto_test WHERE x < 1;
SELECT
  query, avg_error -> 'mean' >= 0., evaluated_nodes, plan_nodes,
  exec_time -> 'mean' > 0., nexecs
FROM pg_track_optimizer()
ORDER BY query; -- Must see it.
-- TODO: Disable storing of queries, involving the extension UI objects

SELECT * FROM pto_test WHERE x < 1;
-- Must see second execution of the query in nexecs (don't mind EXPLAIN)
SELECT
  query, avg_error -> 'mean' >= 0., evaluated_nodes, plan_nodes,
  exec_time -> 'mean' > 0., nexecs
FROM pg_track_optimizer()
ORDER BY query;

/*
 * Tests for parallel workers.
 */

SET max_parallel_workers_per_gather = 4;
SET parallel_setup_cost = 0.0001;
SET parallel_tuple_cost = 0.0000001;
SET min_parallel_table_scan_size = 0;

CREATE TABLE t1 (x numeric) WITH (parallel_workers = 4);
INSERT INTO t1 (x) SELECT random() FROM generate_series(1, 100000);
VACUUM t1;

SET pg_track_optimizer.mode = 'forced';

-- Error must be zero in this case.
-- XXX: Is there a case when number of parallel workers will be less than 4?
EXPLAIN (COSTS OFF, ANALYZE, BUFFERS OFF, TIMING OFF, SUMMARY OFF)
SELECT * FROM t1;

SELECT query,avg_error,rms_error,evaluated_nodes,plan_nodes,nexecs,blks_accessed
FROM pg_track_optimizer() WHERE query LIKE '%FROM t1%';

/*
 * IndexOnlyScan may fetch dead tuples and recheck their state in the heap.
 * That means we touch the disk and need to take into account this fact.
 * Check it here.
 */
CREATE TABLE verify_test (
    id SERIAL PRIMARY KEY,
    val INTEGER
) WITH (autovacuum_enabled = 'off');
CREATE INDEX idx_verify ON verify_test (val);

INSERT INTO verify_test (val)
  SELECT i FROM generate_series(1, 100000) i;
VACUUM ANALYZE verify_test;

-- Dirty some pages
UPDATE verify_test SET val = val + 1 WHERE id % 10 = 0;

SET min_parallel_index_scan_size = 0;
SET enable_seqscan = off;

-- Check if we get parallel index-only scan WITH heap fetches
EXPLAIN (COSTS OFF, ANALYZE, BUFFERS OFF, TIMING OFF, SUMMARY OFF)
SELECT val FROM verify_test;

-- XXX: if we ever implement per-node error printing it would allow to
-- demonstrate how the error grows and show reasoning for the final value in
-- a more understandable manner.
-- So, make this test helpful in the future ...
SELECT query,evaluated_nodes,plan_nodes,nexecs
FROM pg_track_optimizer() WHERE query LIKE '%FROM verify_test%';

-- Test the case when we prepare all the stuff for execution but never actually
-- executed query plan (total time == 0)
BEGIN;
DECLARE foo1 CURSOR WITH HOLD FOR SELECT 1;
SELECT name FROM pg_cursors ORDER BY 1;
CLOSE ALL;
COMMIT;

SELECT * FROM pg_track_optimizer_reset();
DROP EXTENSION pg_track_optimizer;
