CREATE EXTENSION pg_track_optimizer;
-- Cleanup history of previous tests
SELECT * FROM pg_track_optimizer_reset();
 pg_track_optimizer_reset 
--------------------------
                        0
(1 row)

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

CREATE TABLE pto_test(x integer, y integer, z integer);
ANALYZE pto_test;
EXPLAIN (COSTS OFF) SELECT * FROM pto_test WHERE x < 1;
      QUERY PLAN      
----------------------
 Seq Scan on pto_test
   Filter: (x < 1)
(2 rows)

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.
                   query                   | ?column? | evaluated_nodes | plan_nodes | ?column? | nexecs 
-------------------------------------------+----------+-----------------+------------+----------+--------
 SELECT * FROM pg_track_optimizer_flush(); | t        |               1 |          1 | t        |      1
 SELECT * FROM pg_track_optimizer_reset(); | t        |               1 |          1 | t        |      1
(2 rows)

EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF, SUMMARY OFF)
SELECT * FROM pto_test WHERE x < 1;
                   QUERY PLAN                    
-------------------------------------------------
 Seq Scan on pto_test (actual rows=0.00 loops=1)
   Filter: (x < 1)
(2 rows)

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.
                              query                               | ?column? | evaluated_nodes | plan_nodes | ?column? | nexecs 
------------------------------------------------------------------+----------+-----------------+------------+----------+--------
 EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF, SUMMARY OFF)           +| t        |               1 |          1 | t        |      1
 SELECT * FROM pto_test WHERE x < 1;                              |          |                 |            |          | 
 SELECT                                                          +| t        |               2 |          2 | t        |      1
   query, avg_error -> 'mean' >= 0., evaluated_nodes, plan_nodes,+|          |                 |            |          | 
   exec_time -> 'mean' > 0., nexecs                              +|          |                 |            |          | 
 FROM pg_track_optimizer()                                       +|          |                 |            |          | 
 ORDER BY query COLLATE "C";                                      |          |                 |            |          | 
 SELECT * FROM pg_track_optimizer_flush();                        | t        |               1 |          1 | t        |      1
 SELECT * FROM pg_track_optimizer_reset();                        | t        |               1 |          1 | t        |      1
(4 rows)

-- TODO: Disable storing of queries, involving the extension UI objects
SELECT * FROM pto_test WHERE x < 1;
 x | y | z 
---+---+---
(0 rows)

-- 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;
                              query                               | ?column? | evaluated_nodes | plan_nodes | ?column? | nexecs 
------------------------------------------------------------------+----------+-----------------+------------+----------+--------
 EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF, SUMMARY OFF)           +| t        |               1 |          1 | t        |      2
 SELECT * FROM pto_test WHERE x < 1;                              |          |                 |            |          | 
 SELECT                                                          +| t        |               2 |          2 | t        |      1
   query, avg_error -> 'mean' >= 0., evaluated_nodes, plan_nodes,+|          |                 |            |          | 
   exec_time -> 'mean' > 0., nexecs                              +|          |                 |            |          | 
 FROM pg_track_optimizer()                                       +|          |                 |            |          | 
 ORDER BY query COLLATE "C";                                      |          |                 |            |          | 
 SELECT                                                          +| t        |               2 |          2 | t        |      1
   query, avg_error -> 'mean' >= 0., evaluated_nodes, plan_nodes,+|          |                 |            |          | 
   exec_time -> 'mean' > 0., nexecs                              +|          |                 |            |          | 
 FROM pg_track_optimizer()                                       +|          |                 |            |          | 
 ORDER BY query;                                                  |          |                 |            |          | 
 SELECT * FROM pg_track_optimizer_flush();                        | t        |               1 |          1 | t        |      1
 SELECT * FROM pg_track_optimizer_reset();                        | t        |               1 |          1 | t        |      1
(5 rows)

/*
 * 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;
                          QUERY PLAN                          
--------------------------------------------------------------
 Gather (actual rows=100000.00 loops=1)
   Workers Planned: 4
   Workers Launched: 4
   ->  Parallel Seq Scan on t1 (actual rows=20000.00 loops=5)
(4 rows)

SELECT query,avg_error,rms_error,evaluated_nodes,plan_nodes,nexecs,blks_accessed
FROM pg_track_optimizer() WHERE query LIKE '%FROM t1%';
                               query                                |               avg_error               |               rms_error               | evaluated_nodes | plan_nodes | nexecs |                blks_accessed                
--------------------------------------------------------------------+---------------------------------------+---------------------------------------+-----------------+------------+--------+---------------------------------------------
 EXPLAIN (COSTS OFF, ANALYZE, BUFFERS OFF, TIMING OFF, SUMMARY OFF)+| (count:1,mean:0,min:0,max:0,stddev:0) | (count:1,mean:0,min:0,max:0,stddev:0) |               2 |          2 |      1 | (count:1,mean:541,min:541,max:541,stddev:0)
 SELECT * FROM t1;                                                  |                                       |                                       |                 |            |        | 
(1 row)

/*
 * 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;
                                          QUERY PLAN                                           
-----------------------------------------------------------------------------------------------
 Gather (actual rows=100000.00 loops=1)
   Workers Planned: 4
   Workers Launched: 4
   ->  Parallel Index Only Scan using idx_verify on verify_test (actual rows=20000.00 loops=5)
         Heap Fetches: 110000
         Index Searches: 1
(6 rows)

-- 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%';
                               query                                | evaluated_nodes | plan_nodes | nexecs 
--------------------------------------------------------------------+-----------------+------------+--------
 EXPLAIN (COSTS OFF, ANALYZE, BUFFERS OFF, TIMING OFF, SUMMARY OFF)+|               2 |          2 |      1
 SELECT val FROM verify_test;                                       |                 |            | 
(1 row)

SELECT * FROM pg_track_optimizer_reset();
 pg_track_optimizer_reset 
--------------------------
                       12
(1 row)

DROP EXTENSION pg_track_optimizer;