-- Read-ahead behaviour on a table larger than one page: streaming, LIMIT, filters, scan order, views.
\set VERBOSITY terse
SET jev.api_url = 'http://127.0.0.1:8765/v1/systemone';
SET jev.api_key = 'test-key';
SET jev.notices = off;
SET jev.batch_size = 20;
SET jev.concurrency = 4;
CREATE TABLE big AS
  SELECT g AS id, 'row ' || g AS label, CASE WHEN g % 100 = 0 THEN 'special' ELSE 'plain' END AS tag
  FROM generate_series(1, 2500) g;
CREATE INDEX big_id ON big (id);
ANALYZE big;
-- A full scan judges every row exactly once, 20 rows per request, whatever the table size
SELECT count(*) AS special FROM big WHERE jev(big, 'the tag is special');
 special 
---------
      25
(1 row)

SELECT (jev_stats()->>'requests')::int AS requests, (jev_stats()->>'rows_evaluated')::int AS rows_evaluated;
 requests | rows_evaluated 
----------+----------------
      125 |           2500
(1 row)

-- LIMIT stops the read-ahead early: only the in-flight window (2 x concurrency requests) is judged
SELECT id FROM big WHERE jev(big, 'the tag is plain') LIMIT 1;
 id 
----
  1
(1 row)

SELECT (jev_stats()->>'rows_evaluated')::int - 2500 BETWEEN 20 AND 160 AS limit_judged_only_a_window;
 limit_judged_only_a_window 
----------------------------
 t
(1 row)

-- Finishing the same condition later reuses those answers: 2500 rows, 125 requests in total
SELECT count(*) AS plain FROM big WHERE jev(big, 'the tag is plain');
 plain 
-------
  2475
(1 row)

SELECT (jev_stats()->>'requests')::int AS requests, (jev_stats()->>'rows_evaluated')::int AS rows_evaluated;
 requests | rows_evaluated 
----------+----------------
      250 |           5000
(1 row)

-- Rows filtered out by cheaper predicates before jev() runs are skipped, not judged
SELECT count(*) AS every_500th FROM big WHERE id % 500 = 0 AND jev(big, 'the label is row');
 every_500th 
-------------
           5
(1 row)

SELECT (jev_stats()->>'rows_evaluated')::int - 5000 <= 800 AS filtered_rows_skipped;
 filtered_rows_skipped 
-----------------------
 t
(1 row)

-- ... and the rest of the table is judged once when a later statement needs it
SELECT count(*) AS all_rows FROM big WHERE jev(big, 'the label is row');
 all_rows 
----------
     2500
(1 row)

SELECT (jev_stats()->>'rows_evaluated')::int AS rows_evaluated;
 rows_evaluated 
----------------
           7500
(1 row)

-- A backward index scan asks for rows in reverse physical order: they are still batched, not judged one by one
SELECT (jev_stats()->>'rows_evaluated')::int AS rows_before, (jev_stats()->>'requests')::int AS requests_before \gset
SET enable_seqscan = off;
SET enable_sort = off;
SELECT id FROM big WHERE jev(big, 'this is a row') ORDER BY id DESC LIMIT 3;
  id  
------
 2500
 2499
 2498
(3 rows)

RESET enable_seqscan;
RESET enable_sort;
SELECT (jev_stats()->>'rows_evaluated')::int - :rows_before AS rows_judged, (jev_stats()->>'requests')::int - :requests_before AS requests;
 rows_judged | requests 
-------------+----------
          21 |        2
(1 row)

-- Views cannot be paged by ctid and are streamed with OFFSET/LIMIT instead
SELECT (jev_stats()->>'rows_evaluated')::int AS rows_before, (jev_stats()->>'requests')::int AS requests_before \gset
CREATE VIEW special_rows AS SELECT * FROM big WHERE tag = 'special';
SELECT count(*) AS via_view FROM special_rows WHERE jev(special_rows, 'the tag is special');
 via_view 
----------
       25
(1 row)

SELECT (jev_stats()->>'rows_evaluated')::int - :rows_before AS rows_judged, (jev_stats()->>'requests')::int - :requests_before AS requests,
       (jev_stats()->>'errors')::int AS errors;
 rows_judged | requests | errors 
-------------+----------+--------
          25 |        2 |      0
(1 row)

-- Idle keep-alive connections are pooled per session
SELECT (jev_stats()->>'connections')::int BETWEEN 1 AND 4 AS pooled_connections;
 pooled_connections 
--------------------
 t
(1 row)