CREATE SCHEMA explain;

SET SESSION search_path = 'explain';

CREATE EXTENSION orioledb;

----

-- EXPLAIN (ANALYZE TRUE, BUFFERS TRUE) test

----

-- Generate pseudo-random string in deterministic way

CREATE FUNCTION generate_string(seed integer, length integer) RETURNS text

AS $$

SELECT substr(string_agg(

substr(encode(sha256(seed::text::bytea || '_' || i::text::bytea), 'hex'), 1, 21),

''), 1, length)

FROM generate_series(1, (length + 20) / 21) i; $$

LANGUAGE SQL;

-- Wrapper function, which converts result of SQL query to the text

CREATE OR REPLACE FUNCTION query_to_text(sql TEXT, out result text)

RETURNS SETOF TEXT AS $$

BEGIN

FOR result IN EXECUTE sql LOOP

RETURN NEXT;

END LOOP;

END $$

LANGUAGE plpgsql;

-- table: primary index + TOAST

CREATE TABLE IF NOT EXISTS o_explain (

key integer NOT NULL,

val text,

PRIMARY KEY(key)

) USING orioledb;

-- Add byteain to pg_proc cache to keep ANALYZE BUFFERS consistent during runs

SELECT query_to_text($$ SELECT 'FOO'::bytea; $$);

query_to_text

---------------

\x464f4f

(1 row)

SELECT regexp_replace(t, '[\d\.]+', 'x', 'g')

FROM query_to_text($$ EXPLAIN (ANALYZE TRUE, BUFFERS TRUE)

INSERT INTO o_explain (

SELECT id, generate_string(1, 3000)

FROM generate_series(4501, 4700, 1) id

); $$) as t;

regexp_replace

---------------------------------------------------------------------------------------------------

Insert on o_explain (cost=x rows=x width=x) (actual time=x rows=x loops=x)

Buffers: shared hit=x

-> Function Scan on generate_series id (cost=x rows=x width=x) (actual time=x rows=x loops=x)

Buffers: shared hit=x

Planning:

Buffers: shared hit=x

Planning Time: x ms

Execution Time: x ms

(8 rows)

-- just explain analyze without buffers

SELECT regexp_replace(t, '[\d\.]+', 'x', 'g')

FROM query_to_text($$ EXPLAIN (ANALYZE TRUE)

SELECT count(*)

FROM o_explain; $$) as t;

regexp_replace

-------------------------------------------------------------------------------------

Aggregate (cost=x rows=x width=x) (actual time=x rows=x loops=x)

-> Seq Scan on o_explain (cost=x rows=x width=x) (actual time=x rows=x loops=x)

Planning Time: x ms

Execution Time: x ms

(4 rows)

SELECT regexp_replace(t, '[\d\.]+', 'x', 'g')

FROM query_to_text($$ EXPLAIN ANALYZE

SELECT count(*)

FROM o_explain; $$) as t;

regexp_replace

-------------------------------------------------------------------------------------

Aggregate (cost=x rows=x width=x) (actual time=x rows=x loops=x)

-> Seq Scan on o_explain (cost=x rows=x width=x) (actual time=x rows=x loops=x)

Planning Time: x ms

Execution Time: x ms

(4 rows)

-- just explain buffers without analyze, fails

EXPLAIN (BUFFERS TRUE) SELECT count(*) FROM o_explain;

QUERY PLAN

------------------------------------------------------------------

Aggregate (cost=21.64..21.65 rows=1 width=8)

-> Seq Scan on o_explain (cost=0.00..19.31 rows=931 width=0)

(2 rows)

-- test lowecase letters

SELECT regexp_replace(t, '[\d\.]+', 'x', 'g')

FROM query_to_text($$ EXPLAIN (analyze TRUE, buffers TRUE)

SELECT count(*)

FROM o_explain; $$) as t;

regexp_replace

-------------------------------------------------------------------------------------

Aggregate (cost=x rows=x width=x) (actual time=x rows=x loops=x)

-> Seq Scan on o_explain (cost=x rows=x width=x) (actual time=x rows=x loops=x)

Planning Time: x ms

Execution Time: x ms

(4 rows)

-- does not use TOAST tree (does not fetch TOASTed values)

SELECT regexp_replace(t, '[\d\.]+', 'x', 'g')

FROM query_to_text($$ EXPLAIN (ANALYZE TRUE, BUFFERS TRUE)

SELECT count(*)

FROM o_explain; $$) as t;

regexp_replace

-------------------------------------------------------------------------------------

Aggregate (cost=x rows=x width=x) (actual time=x rows=x loops=x)

-> Seq Scan on o_explain (cost=x rows=x width=x) (actual time=x rows=x loops=x)

Planning Time: x ms

Execution Time: x ms

(4 rows)

-- Add text_ops to pg_amop cache to keep ANALYZE BUFFERS consistent during runs

SELECT * FROM o_explain WHERE val = 'A' ORDER BY val LIMIT 1;

key | val

-----+-----

(0 rows)

-- uses TOAST to fetch values

SELECT regexp_replace(t, '[\d\.]+', 'x', 'g')

FROM query_to_text($$ EXPLAIN (ANALYZE TRUE, BUFFERS TRUE)

SELECT *

FROM o_explain ORDER BY val; $$) as t;

regexp_replace

-------------------------------------------------------------------------------------

Sort (cost=x rows=x width=x) (actual time=x rows=x loops=x)

Sort Key: val

Sort Method: quicksort Memory: xkB

Buffers: shared hit=x

-> Seq Scan on o_explain (cost=x rows=x width=x) (actual time=x rows=x loops=x)

Planning:

Buffers: shared hit=x

Planning Time: x ms

Execution Time: x ms

(9 rows)

-- table: primary index + secondary index without TOAST

DROP TABLE o_explain;

CREATE TABLE o_explain (

key integer NOT NULL,

val integer NOT NULL,

PRIMARY KEY(key)

) USING orioledb;

CREATE INDEX o_explain_sec_non_val ON o_explain (val);

SELECT regexp_replace(t, '[\d\.]+', 'x', 'g')

FROM query_to_text($$ EXPLAIN (ANALYZE TRUE, BUFFERS TRUE)

INSERT INTO o_explain (

SELECT id, id + 1

FROM generate_series(1, 5000, 1) id

); $$) as t;

regexp_replace

---------------------------------------------------------------------------------------------------

Insert on o_explain (cost=x rows=x width=x) (actual time=x rows=x loops=x)

Buffers: shared hit=x

-> Function Scan on generate_series id (cost=x rows=x width=x) (actual time=x rows=x loops=x)

Planning Time: x ms

Execution Time: x ms

(5 rows)

-- use secondary index for scan

SELECT regexp_replace(t, '[\d\.]+', 'x', 'g')

FROM query_to_text($$ EXPLAIN (ANALYZE TRUE, BUFFERS TRUE)

SELECT *

FROM o_explain ORDER BY val; $$) as t;

regexp_replace

------------------------------------------------------------------------------------------------------------------

Index Only Scan using o_explain_sec_non_val on o_explain (cost=x rows=x width=x) (actual time=x rows=x loops=x)

Heap Fetches: x

Planning:

Buffers: shared hit=x

Planning Time: x ms

Execution Time: x ms

(6 rows)

-- do not use secondary index

SELECT regexp_replace(t, '[\d\.]+', 'x', 'g')

FROM query_to_text($$ EXPLAIN (ANALYZE TRUE, BUFFERS TRUE)

SELECT *

FROM o_explain; $$) as t;

regexp_replace

-------------------------------------------------------------------------------

Seq Scan on o_explain (cost=x rows=x width=x) (actual time=x rows=x loops=x)

Planning Time: x ms

Execution Time: x ms

(3 rows)

DROP TABLE o_explain;

CREATE TABLE o_explain (

key integer NOT NULL,

val1 integer NOT NULL,

val2 integer NOT NULL

) USING orioledb;

CREATE INDEX o_explain_sec_non_val2 ON o_explain (val2);

INSERT INTO o_explain (SELECT id, id + 1, id + 2 FROM generate_series(1, 1000, 1) id);

ANALYZE o_explain;

SELECT SUM(key) FROM o_explain WHERE val2 > 0;

sum

--------

500500

(1 row)

SELECT SUM(val1) FROM o_explain WHERE val2 > 0;

sum

--------

501500

(1 row)

SELECT SUM(val2) FROM o_explain WHERE val2 > 0; -- check sum

sum

--------

502500

(1 row)

SET enable_seqscan = off;

-- uses only secondary index

SELECT regexp_replace(t, '[\d\.]+', 'x', 'g')

FROM query_to_text($$ EXPLAIN (ANALYZE TRUE, BUFFERS TRUE)

SELECT SUM(val2) FROM o_explain WHERE val2 > 0

AND val2 < 1000; $$) as t;

regexp_replace

-------------------------------------------------------------------------------------------------------------------------

Aggregate (cost=x rows=x width=x) (actual time=x rows=x loops=x)

-> Index Only Scan using o_explain_sec_non_valx on o_explain (cost=x rows=x width=x) (actual time=x rows=x loops=x)

Index Cond: ((valx > x) AND (valx < x))

Heap Fetches: x

Planning Time: x ms

Execution Time: x ms

(6 rows)

-- TODO: Make this work again if this is important (uses scan index now)

-- uses only secondary index - primary index is ctid index stored in secondary index

SELECT regexp_replace(t, '[\d\.]+', 'x', 'g')

FROM query_to_text($$ EXPLAIN (ANALYZE TRUE, BUFFERS TRUE)

SELECT ctid FROM o_explain WHERE val2 > 0

AND val2 < 1000; $$) as t;

regexp_replace

--------------------------------------------------------------------------------------------------------------

Index Scan using o_explain_sec_non_valx on o_explain (cost=x rows=x width=x) (actual time=x rows=x loops=x)

Index Cond: ((valx > x) AND (valx < x))

Planning Time: x ms

Execution Time: x ms

(4 rows)

-- uses primary and secondary index

SELECT regexp_replace(t, '[\d\.]+', 'x', 'g')

FROM query_to_text($$ EXPLAIN (ANALYZE TRUE, BUFFERS TRUE)

SELECT key FROM o_explain WHERE val2 > 0

AND val2 < 1000; $$) as t;

regexp_replace

--------------------------------------------------------------------------------------------------------------

Index Scan using o_explain_sec_non_valx on o_explain (cost=x rows=x width=x) (actual time=x rows=x loops=x)

Index Cond: ((valx > x) AND (valx < x))

Planning Time: x ms

Execution Time: x ms

(4 rows)

-- uses primary and secondary index

SELECT regexp_replace(t, '[\d\.]+', 'x', 'g')

FROM query_to_text($$ EXPLAIN (ANALYZE TRUE, BUFFERS TRUE)

SELECT key, val1 FROM o_explain WHERE val2 > 0

AND val2 < 1000; $$) as t;

regexp_replace

--------------------------------------------------------------------------------------------------------------

Index Scan using o_explain_sec_non_valx on o_explain (cost=x rows=x width=x) (actual time=x rows=x loops=x)

Index Cond: ((valx > x) AND (valx < x))

Planning Time: x ms

Execution Time: x ms

(4 rows)

-- uses primary and secondary index

SELECT regexp_replace(t, '[\d\.]+', 'x', 'g')

FROM query_to_text($$ EXPLAIN (ANALYZE TRUE, BUFFERS TRUE)

SELECT key, val2 FROM o_explain WHERE val2 > 0

AND val2 < 1000; $$) as t;

regexp_replace

--------------------------------------------------------------------------------------------------------------

Index Scan using o_explain_sec_non_valx on o_explain (cost=x rows=x width=x) (actual time=x rows=x loops=x)

Index Cond: ((valx > x) AND (valx < x))

Planning Time: x ms

Execution Time: x ms

(4 rows)

-- uses primary and secondary index

SELECT regexp_replace(t, '[\d\.]+', 'x', 'g')

FROM query_to_text($$ EXPLAIN (ANALYZE TRUE, BUFFERS TRUE)

SELECT val1 FROM o_explain WHERE val2 > 0; $$) as t;

regexp_replace

--------------------------------------------------------------------------------------------------------------

Index Scan using o_explain_sec_non_valx on o_explain (cost=x rows=x width=x) (actual time=x rows=x loops=x)

Index Cond: (valx > x)

Planning Time: x ms

Execution Time: x ms

(4 rows)

-- uses primary and secondary index

SELECT regexp_replace(t, '[\d\.]+', 'x', 'g')

FROM query_to_text($$ EXPLAIN (ANALYZE TRUE, BUFFERS TRUE)

SELECT val1, val2 FROM o_explain WHERE val2 > 0

AND val2 < 1000; $$) as t;

regexp_replace

--------------------------------------------------------------------------------------------------------------

Index Scan using o_explain_sec_non_valx on o_explain (cost=x rows=x width=x) (actual time=x rows=x loops=x)

Index Cond: ((valx > x) AND (valx < x))

Planning Time: x ms

Execution Time: x ms

(4 rows)

-- uses only secondary index for fetching values from secondary index

SELECT regexp_replace(t, '[\d\.]+', 'x', 'g')

FROM query_to_text($$ EXPLAIN (ANALYZE TRUE, BUFFERS TRUE)

SELECT val2 FROM o_explain WHERE val2 > 0

AND val2 < 1000; $$) as t;

regexp_replace

-------------------------------------------------------------------------------------------------------------------

Index Only Scan using o_explain_sec_non_valx on o_explain (cost=x rows=x width=x) (actual time=x rows=x loops=x)

Index Cond: ((valx > x) AND (valx < x))

Heap Fetches: x

Planning Time: x ms

Execution Time: x ms

(5 rows)

SELECT * FROM o_explain WHERE val2 BETWEEN 1 AND 10;

key | val1 | val2

-----+------+------

1 | 2 | 3

2 | 3 | 4

3 | 4 | 5

4 | 5 | 6

5 | 6 | 7

6 | 7 | 8

7 | 8 | 9

8 | 9 | 10

(8 rows)

RESET enable_seqscan;

DROP TABLE o_explain;

CREATE TABLE o_explain (

key integer NOT NULL,

val1 integer NOT NULL,

val2 integer NOT NULL,

PRIMARY KEY(key)

) USING orioledb;

CREATE INDEX o_explain_sec_non_val2 ON o_explain (val2);

INSERT INTO o_explain (SELECT id, id + 1, id + 2 FROM generate_series(1, 1000, 1) id);

ANALYZE o_explain;

SELECT SUM(key) FROM o_explain WHERE val2 > 0;

sum

--------

500500

(1 row)

SELECT SUM(val1) FROM o_explain WHERE val2 > 0;

sum

--------

501500

(1 row)

SELECT SUM(val2) FROM o_explain WHERE val2 > 0; -- check sum

sum

--------

502500

(1 row)

-- uses only secondary index

SELECT regexp_replace(t, '[\d\.]+', 'x', 'g')

FROM query_to_text($$ EXPLAIN (ANALYZE TRUE, BUFFERS TRUE)

SELECT SUM(s.val2) FROM (

SELECT val2 FROM o_explain

WHERE val2 > 0 AND val2 < 1000

ORDER BY val2

) s; $$) as t;

regexp_replace

-------------------------------------------------------------------------------------------------------------------------

Aggregate (cost=x rows=x width=x) (actual time=x rows=x loops=x)

-> Index Only Scan using o_explain_sec_non_valx on o_explain (cost=x rows=x width=x) (actual time=x rows=x loops=x)

Index Cond: ((valx > x) AND (valx < x))

Heap Fetches: x

Planning Time: x ms

Execution Time: x ms

(6 rows)

-- uses secondary index, primary key is stored in secondary index

SELECT regexp_replace(t, '[\d\.]+', 'x', 'g')

FROM query_to_text($$ EXPLAIN (ANALYZE TRUE, BUFFERS TRUE)

SELECT key FROM o_explain WHERE val2 > 0

AND val2 < 1000 ORDER BY val2; $$) as t;

regexp_replace

-------------------------------------------------------------------------------------------------------------------

Index Only Scan using o_explain_sec_non_valx on o_explain (cost=x rows=x width=x) (actual time=x rows=x loops=x)

Index Cond: ((valx > x) AND (valx < x))

Heap Fetches: x

Planning Time: x ms

Execution Time: x ms

(5 rows)

-- uses primary and secondary index

SELECT regexp_replace(t, '[\d\.]+', 'x', 'g')

FROM query_to_text($$ EXPLAIN (ANALYZE TRUE, BUFFERS TRUE)

SELECT key, val1 FROM o_explain WHERE val2 > 0

AND val2 < 1000 ORDER BY val2; $$) as t;

regexp_replace

--------------------------------------------------------------------------------------------------------------

Index Scan using o_explain_sec_non_valx on o_explain (cost=x rows=x width=x) (actual time=x rows=x loops=x)

Index Cond: ((valx > x) AND (valx < x))

Planning Time: x ms

Execution Time: x ms

(4 rows)

-- uses only secondary index for fetching secondary index value and primary key

SELECT regexp_replace(t, '[\d\.]+', 'x', 'g')

FROM query_to_text($$ EXPLAIN (ANALYZE TRUE, BUFFERS TRUE)

SELECT key, val2 FROM o_explain WHERE val2 > 0

AND val2 < 1000 ORDER BY val2; $$) as t;

regexp_replace

-------------------------------------------------------------------------------------------------------------------

Index Only Scan using o_explain_sec_non_valx on o_explain (cost=x rows=x width=x) (actual time=x rows=x loops=x)

Index Cond: ((valx > x) AND (valx < x))

Heap Fetches: x

Planning Time: x ms

Execution Time: x ms

(5 rows)

-- uses primary and secondary index

SELECT regexp_replace(t, '[\d\.]+', 'x', 'g')

FROM query_to_text($$ EXPLAIN (ANALYZE TRUE, BUFFERS TRUE)

SELECT val1 FROM o_explain WHERE val2 > 0

AND val2 < 1000 ORDER BY val2; $$) as t;

regexp_replace

--------------------------------------------------------------------------------------------------------------

Index Scan using o_explain_sec_non_valx on o_explain (cost=x rows=x width=x) (actual time=x rows=x loops=x)

Index Cond: ((valx > x) AND (valx < x))

Planning Time: x ms

Execution Time: x ms

(4 rows)

-- uses primary and secondary index

SELECT regexp_replace(t, '[\d\.]+', 'x', 'g')

FROM query_to_text($$ EXPLAIN (ANALYZE TRUE, BUFFERS TRUE)

SELECT val1, val2 FROM o_explain WHERE val2 > 0

AND val2 < 1000 ORDER BY val2; $$) as t;

regexp_replace

--------------------------------------------------------------------------------------------------------------

Index Scan using o_explain_sec_non_valx on o_explain (cost=x rows=x width=x) (actual time=x rows=x loops=x)

Index Cond: ((valx > x) AND (valx < x))

Planning Time: x ms

Execution Time: x ms

(4 rows)

-- uses only secondary index for fetching values from secondary index

SELECT regexp_replace(t, '[\d\.]+', 'x', 'g')

FROM query_to_text($$ EXPLAIN (ANALYZE TRUE, BUFFERS TRUE)

SELECT val2 FROM o_explain WHERE val2 > 0

AND val2 < 1000 ORDER BY val2; $$) as t;

regexp_replace

-------------------------------------------------------------------------------------------------------------------

Index Only Scan using o_explain_sec_non_valx on o_explain (cost=x rows=x width=x) (actual time=x rows=x loops=x)

Index Cond: ((valx > x) AND (valx < x))

Heap Fetches: x

Planning Time: x ms

Execution Time: x ms

(5 rows)

SELECT * FROM o_explain WHERE val2 BETWEEN 1 AND 10;

key | val1 | val2

-----+------+------

1 | 2 | 3

2 | 3 | 4

3 | 4 | 5

4 | 5 | 6

5 | 6 | 7

6 | 7 | 8

7 | 8 | 9

8 | 9 | 10

(8 rows)

DROP TABLE o_explain;

---

-- Test for TOAST values update

---

CREATE TABLE o_explain (

key integer NOT NULL,

t text NOT NULL,

val integer NOT NULL,

PRIMARY KEY(key)

) USING orioledb;

INSERT INTO o_explain (SELECT id, generate_string(1, 3000), id FROM generate_series(1, 200, 1) id);

-- do not use TOAST index for this queries (UPDATE TOAST with same values)

SELECT regexp_replace(t, '[\d\.]+', 'x', 'g')

FROM query_to_text($$ EXPLAIN (ANALYZE TRUE, BUFFERS TRUE)

UPDATE o_explain SET val = val + 1; $$) as t;

regexp_replace

-------------------------------------------------------------------------------------

Update on o_explain (cost=x rows=x width=x) (actual time=x rows=x loops=x)

-> Seq Scan on o_explain (cost=x rows=x width=x) (actual time=x rows=x loops=x)

Planning:

Buffers: shared hit=x

Planning Time: x ms

Execution Time: x ms

(6 rows)

SELECT regexp_replace(t, '[\d\.]+', 'x', 'g')

FROM query_to_text($$ EXPLAIN (ANALYZE TRUE, BUFFERS TRUE)

INSERT INTO o_explain

(SELECT id, generate_string(1, 3000), id

FROM generate_series(1, 100, 1) id)

ON CONFLICT (key) DO UPDATE

SET val = o_explain.val + 1; $$) as t;

regexp_replace

---------------------------------------------------------------------------------------------------

Insert on o_explain (cost=x rows=x width=x) (actual time=x rows=x loops=x)

Conflict Resolution: UPDATE

Conflict Arbiter Indexes: o_explain_pkey

Tuples Inserted: x

Conflicting Tuples: x

-> Function Scan on generate_series id (cost=x rows=x width=x) (actual time=x rows=x loops=x)

Planning Time: x ms

Execution Time: x ms

(8 rows)

-- UPDATE TOAST with equal values (only TOAST reads for compare values)

SELECT regexp_replace(t, '[\d\.]+', 'x', 'g')

FROM query_to_text($$ EXPLAIN (ANALYZE TRUE, BUFFERS TRUE)

INSERT INTO o_explain

(SELECT id, generate_string(1, 3000), id

FROM generate_series(1, 100, 1) id)

ON CONFLICT (key) DO UPDATE

SET val = o_explain.val + 1, t = EXCLUDED.t; $$) as t;

regexp_replace

---------------------------------------------------------------------------------------------------

Insert on o_explain (cost=x rows=x width=x) (actual time=x rows=x loops=x)

Conflict Resolution: UPDATE

Conflict Arbiter Indexes: o_explain_pkey

Tuples Inserted: x

Conflicting Tuples: x

-> Function Scan on generate_series id (cost=x rows=x width=x) (actual time=x rows=x loops=x)

Planning Time: x ms

Execution Time: x ms

(8 rows)

CREATE TABLE o_test_explain_verbose_rowid (

val_1 int,

val_2 int

) USING orioledb;

INSERT INTO o_test_explain_verbose_rowid VALUES (1, 1);

EXPLAIN (VERBOSE, COSTS OFF)

UPDATE o_test_explain_verbose_rowid SET val_2 = 10;

QUERY PLAN

--------------------------------------------------------

Update on explain.o_test_explain_verbose_rowid

-> Seq Scan on explain.o_test_explain_verbose_rowid

Output: 10, rowid

(3 rows)

CREATE TABLE o_explain_formats (

key int PRIMARY KEY,

val int UNIQUE

) USING orioledb;

INSERT INTO o_explain_formats (SELECT v, v FROM generate_series(1, 10) v);

-- Bitmap Heap Scan all formats with all posible fields BEGIN

SET enable_indexscan = OFF;

SELECT regexp_replace(t, '[\d\.]+', 'x', 'g')

FROM query_to_text($$ EXPLAIN (FORMAT TEXT, ANALYZE, BUFFERS)

SELECT * FROM o_explain_formats

WHERE val > 5 ORDER BY val; $$) as t;

regexp_replace

--------------------------------------------------------------------------------------------------------------------

Sort (cost=x rows=x width=x) (actual time=x rows=x loops=x)

Sort Key: val

Sort Method: quicksort Memory: xkB

-> Custom Scan (o_scan) on o_explain_formats (cost=x rows=x width=x) (actual time=x rows=x loops=x)

Bitmap heap scan

Recheck Cond: (val > x)

-> Bitmap Index Scan on o_explain_formats_val_key (cost=x rows=x width=x) (actual time=x rows=x loops=x)

Index Cond: (val > x)

Secondary index (o_explain_formats_val_key) pages: read=x

Primary pages: read=x

Planning:

Buffers: shared hit=x

Planning Time: x ms

Execution Time: x ms

(14 rows)

SELECT regexp_replace(t, '[\d\.]+', 'x', 'g')

FROM query_to_text($$ EXPLAIN (FORMAT YAML, ANALYZE, BUFFERS)

SELECT * FROM o_explain_formats

WHERE val > 5 ORDER BY val; $$) as t;

regexp_replace

-------------------------------------------------------

- Plan: +

Node Type: "Sort" +

Parallel Aware: false +

Async Capable: false +

Startup Cost: x +

Total Cost: x +

Plan Rows: x +

Plan Width: x +

Actual Startup Time: x +

Actual Total Time: x +

Actual Rows: x +

Actual Loops: x +

Sort Key: +

- "val" +

Sort Method: "quicksort" +

Sort Space Used: x +

Sort Space Type: "Memory" +

Shared Hit Blocks: x +

Shared Read Blocks: x +

Shared Dirtied Blocks: x +

Shared Written Blocks: x +

Local Hit Blocks: x +

Local Read Blocks: x +

Local Dirtied Blocks: x +

Local Written Blocks: x +

Temp Read Blocks: x +

Temp Written Blocks: x +

Plans: +

- Node Type: "Custom Scan" +

Parent Relationship: "Outer" +

Custom Plan Provider: "o_scan" +

Parallel Aware: false +

Async Capable: false +

Relation Name: "o_explain_formats" +

Alias: "o_explain_formats" +

Startup Cost: x +

Total Cost: x +

Plan Rows: x +

Plan Width: x +

Actual Startup Time: x +

Actual Total Time: x +

Actual Rows: x +

Actual Loops: x +

Custom Scan Subtype: "Bitmap Heap Scan" +

Recheck Cond: "(val > x)" +

Rows Removed by Index Recheck: x +

Plans: +

- Node Type: "Bitmap Index Scan" +

Parent Relationship: "Outer" +

Parallel Aware: false +

Async Capable: false +

Index Name: "o_explain_formats_val_key" +

Startup Cost: x +

Total Cost: x +

Plan Rows: x +

Plan Width: x +

Actual Startup Time: x +

Actual Total Time: x +

Actual Rows: x +

Actual Loops: x +

Index Cond: "(val > x)" +

Shared Hit Blocks: x +

Shared Read Blocks: x +

Shared Dirtied Blocks: x +

Shared Written Blocks: x +

Local Hit Blocks: x +

Local Read Blocks: x +

Local Dirtied Blocks: x +

Local Written Blocks: x +

Temp Read Blocks: x +

Temp Written Blocks: x +

Secondary Index Pages: +

Index Name: "o_explain_formats_val_key"+

Read: x +

Primary Pages: +

Read: x +

Shared Hit Blocks: x +

Shared Read Blocks: x +

Shared Dirtied Blocks: x +

Shared Written Blocks: x +

Local Hit Blocks: x +

Local Read Blocks: x +

Local Dirtied Blocks: x +

Local Written Blocks: x +

Temp Read Blocks: x +

Temp Written Blocks: x +

Planning: +

Shared Hit Blocks: x +

Shared Read Blocks: x +

Shared Dirtied Blocks: x +

Shared Written Blocks: x +

Local Hit Blocks: x +

Local Read Blocks: x +

Local Dirtied Blocks: x +

Local Written Blocks: x +

Temp Read Blocks: x +

Temp Written Blocks: x +

Planning Time: x +

Triggers: +

Execution Time: x

(1 row)

SELECT regexp_replace(t, '[\d\.]+', 'x', 'g')

FROM query_to_text($$ EXPLAIN (FORMAT JSON, ANALYZE, BUFFERS)

SELECT * FROM o_explain_formats

WHERE val > 5 ORDER BY val; $$) as t;

regexp_replace

------------------------------------------------------------

[ +

{ +

"Plan": { +

"Node Type": "Sort", +

"Parallel Aware": false, +

"Async Capable": false, +

"Startup Cost": x, +

"Total Cost": x, +

"Plan Rows": x, +

"Plan Width": x, +

"Actual Startup Time": x, +

"Actual Total Time": x, +

"Actual Rows": x, +

"Actual Loops": x, +

"Sort Key": ["val"], +

"Sort Method": "quicksort", +

"Sort Space Used": x, +

"Sort Space Type": "Memory", +

"Shared Hit Blocks": x, +

"Shared Read Blocks": x, +

"Shared Dirtied Blocks": x, +

"Shared Written Blocks": x, +

"Local Hit Blocks": x, +

"Local Read Blocks": x, +

"Local Dirtied Blocks": x, +

"Local Written Blocks": x, +

"Temp Read Blocks": x, +

"Temp Written Blocks": x, +

"Plans": [ +

{ +

"Node Type": "Custom Scan", +

"Parent Relationship": "Outer", +

"Custom Plan Provider": "o_scan", +

"Parallel Aware": false, +

"Async Capable": false, +

"Relation Name": "o_explain_formats", +

"Alias": "o_explain_formats", +

"Startup Cost": x, +

"Total Cost": x, +

"Plan Rows": x, +

"Plan Width": x, +

"Actual Startup Time": x, +

"Actual Total Time": x, +

"Actual Rows": x, +

"Actual Loops": x, +

"Custom Scan Subtype": "Bitmap Heap Scan", +

"Recheck Cond": "(val > x)", +

"Rows Removed by Index Recheck": x, +

"Plans": [ +

{ +

"Node Type": "Bitmap Index Scan", +

"Parent Relationship": "Outer", +

"Parallel Aware": false, +

"Async Capable": false, +

"Index Name": "o_explain_formats_val_key", +

"Startup Cost": x, +

"Total Cost": x, +

"Plan Rows": x, +

"Plan Width": x, +

"Actual Startup Time": x, +

"Actual Total Time": x, +

"Actual Rows": x, +

"Actual Loops": x, +

"Index Cond": "(val > x)", +

"Shared Hit Blocks": x, +

"Shared Read Blocks": x, +

"Shared Dirtied Blocks": x, +

"Shared Written Blocks": x, +

"Local Hit Blocks": x, +

"Local Read Blocks": x, +

"Local Dirtied Blocks": x, +

"Local Written Blocks": x, +

"Temp Read Blocks": x, +

"Temp Written Blocks": x, +

"Secondary Index Pages": { +

"Index Name": "o_explain_formats_val_key",+

"Read": x +

} +

} +

], +

"Primary Pages": { +

"Read": x +

}, +

"Shared Hit Blocks": x, +

"Shared Read Blocks": x, +

"Shared Dirtied Blocks": x, +

"Shared Written Blocks": x, +

"Local Hit Blocks": x, +

"Local Read Blocks": x, +

"Local Dirtied Blocks": x, +

"Local Written Blocks": x, +

"Temp Read Blocks": x, +

"Temp Written Blocks": x +

} +

] +

}, +

"Planning": { +

"Shared Hit Blocks": x, +

"Shared Read Blocks": x, +

"Shared Dirtied Blocks": x, +

"Shared Written Blocks": x, +

"Local Hit Blocks": x, +

"Local Read Blocks": x, +

"Local Dirtied Blocks": x, +

"Local Written Blocks": x, +

"Temp Read Blocks": x, +

"Temp Written Blocks": x +

}, +

"Planning Time": x, +

"Triggers": [ +

], +

"Execution Time": x +

} +

]

(1 row)

SELECT regexp_replace(t, '[\d\.]+', 'x', 'g')

FROM query_to_text($$ EXPLAIN (FORMAT XML, ANALYZE, BUFFERS)

SELECT * FROM o_explain_formats

WHERE val > 5 ORDER BY val; $$) as t;

regexp_replace

----------------------------------------------------------------------------

<explain xmlns="http://wwwxpostgresqlxorg/x/explain"> +

<Query> +

<Plan> +

<Node-Type>Sort</Node-Type> +

<Parallel-Aware>false</Parallel-Aware> +

<Async-Capable>false</Async-Capable> +

<Startup-Cost>x</Startup-Cost> +

<Total-Cost>x</Total-Cost> +

<Plan-Rows>x</Plan-Rows> +

<Plan-Width>x</Plan-Width> +

<Actual-Startup-Time>x</Actual-Startup-Time> +

<Actual-Total-Time>x</Actual-Total-Time> +

<Actual-Rows>x</Actual-Rows> +

<Actual-Loops>x</Actual-Loops> +

<Sort-Key> +

<Item>val</Item> +

</Sort-Key> +

<Sort-Method>quicksort</Sort-Method> +

<Sort-Space-Used>x</Sort-Space-Used> +

<Sort-Space-Type>Memory</Sort-Space-Type> +

<Shared-Hit-Blocks>x</Shared-Hit-Blocks> +

<Shared-Read-Blocks>x</Shared-Read-Blocks> +

<Shared-Dirtied-Blocks>x</Shared-Dirtied-Blocks> +

<Shared-Written-Blocks>x</Shared-Written-Blocks> +

<Local-Hit-Blocks>x</Local-Hit-Blocks> +

<Local-Read-Blocks>x</Local-Read-Blocks> +

<Local-Dirtied-Blocks>x</Local-Dirtied-Blocks> +

<Local-Written-Blocks>x</Local-Written-Blocks> +

<Temp-Read-Blocks>x</Temp-Read-Blocks> +

<Temp-Written-Blocks>x</Temp-Written-Blocks> +

<Plans> +

<Plan> +

<Node-Type>Custom Scan</Node-Type> +

<Parent-Relationship>Outer</Parent-Relationship> +

<Custom-Plan-Provider>o_scan</Custom-Plan-Provider> +

<Parallel-Aware>false</Parallel-Aware> +

<Async-Capable>false</Async-Capable> +

<Relation-Name>o_explain_formats</Relation-Name> +

<Alias>o_explain_formats</Alias> +

<Startup-Cost>x</Startup-Cost> +

<Total-Cost>x</Total-Cost> +

<Plan-Rows>x</Plan-Rows> +

<Plan-Width>x</Plan-Width> +

<Actual-Startup-Time>x</Actual-Startup-Time> +

<Actual-Total-Time>x</Actual-Total-Time> +

<Actual-Rows>x</Actual-Rows> +

<Actual-Loops>x</Actual-Loops> +

<Custom-Scan-Subtype>Bitmap Heap Scan</Custom-Scan-Subtype> +

<Recheck-Cond>(val &gt; x)</Recheck-Cond> +

<Rows-Removed-by-Index-Recheck>x</Rows-Removed-by-Index-Recheck>+

<Plans> +

<Plan> +

<Node-Type>Bitmap Index Scan</Node-Type> +

<Parent-Relationship>Outer</Parent-Relationship> +

<Parallel-Aware>false</Parallel-Aware> +

<Async-Capable>false</Async-Capable> +

<Index-Name>o_explain_formats_val_key</Index-Name> +

<Startup-Cost>x</Startup-Cost> +

<Total-Cost>x</Total-Cost> +

<Plan-Rows>x</Plan-Rows> +

<Plan-Width>x</Plan-Width> +

<Actual-Startup-Time>x</Actual-Startup-Time> +

<Actual-Total-Time>x</Actual-Total-Time> +

<Actual-Rows>x</Actual-Rows> +

<Actual-Loops>x</Actual-Loops> +

<Index-Cond>(val &gt; x)</Index-Cond> +

<Shared-Hit-Blocks>x</Shared-Hit-Blocks> +

<Shared-Read-Blocks>x</Shared-Read-Blocks> +

<Shared-Dirtied-Blocks>x</Shared-Dirtied-Blocks> +

<Shared-Written-Blocks>x</Shared-Written-Blocks> +

<Local-Hit-Blocks>x</Local-Hit-Blocks> +

<Local-Read-Blocks>x</Local-Read-Blocks> +

<Local-Dirtied-Blocks>x</Local-Dirtied-Blocks> +

<Local-Written-Blocks>x</Local-Written-Blocks> +

<Temp-Read-Blocks>x</Temp-Read-Blocks> +

<Temp-Written-Blocks>x</Temp-Written-Blocks> +

<Secondary-Index-Pages> +

<Index-Name>o_explain_formats_val_key</Index-Name> +

<Read>x</Read> +

</Secondary-Index-Pages> +

</Plan> +

</Plans> +

<Primary-Pages> +

<Read>x</Read> +

</Primary-Pages> +

<Shared-Hit-Blocks>x</Shared-Hit-Blocks> +

<Shared-Read-Blocks>x</Shared-Read-Blocks> +

<Shared-Dirtied-Blocks>x</Shared-Dirtied-Blocks> +

<Shared-Written-Blocks>x</Shared-Written-Blocks> +

<Local-Hit-Blocks>x</Local-Hit-Blocks> +

<Local-Read-Blocks>x</Local-Read-Blocks> +

<Local-Dirtied-Blocks>x</Local-Dirtied-Blocks> +

<Local-Written-Blocks>x</Local-Written-Blocks> +

<Temp-Read-Blocks>x</Temp-Read-Blocks> +

<Temp-Written-Blocks>x</Temp-Written-Blocks> +

</Plan> +

</Plans> +

</Plan> +

<Planning> +

<Shared-Hit-Blocks>x</Shared-Hit-Blocks> +

<Shared-Read-Blocks>x</Shared-Read-Blocks> +

<Shared-Dirtied-Blocks>x</Shared-Dirtied-Blocks> +

<Shared-Written-Blocks>x</Shared-Written-Blocks> +

<Local-Hit-Blocks>x</Local-Hit-Blocks> +

<Local-Read-Blocks>x</Local-Read-Blocks> +

<Local-Dirtied-Blocks>x</Local-Dirtied-Blocks> +

<Local-Written-Blocks>x</Local-Written-Blocks> +

<Temp-Read-Blocks>x</Temp-Read-Blocks> +

<Temp-Written-Blocks>x</Temp-Written-Blocks> +

</Planning> +

<Planning-Time>x</Planning-Time> +

<Triggers> +

</Triggers> +

<Execution-Time>x</Execution-Time> +

</Query> +

</explain>

(1 row)

RESET enable_indexscan;

-- Bitmap Heap Scan all formats with all posible fields END

-- Index Scan all formats with all posible fields BEGIN

SET enable_indexonlyscan = OFF;

SELECT regexp_replace(t, '[\d\.]+', 'x', 'g')

FROM query_to_text($$ EXPLAIN (FORMAT TEXT, ANALYZE, BUFFERS)

SELECT * FROM o_explain_formats

WHERE val > 5 ORDER BY val; $$) as t;

regexp_replace

-------------------------------------------------------------------------------------------------------------------------

Index Scan using o_explain_formats_val_key on o_explain_formats (cost=x rows=x width=x) (actual time=x rows=x loops=x)

Index Cond: (val > x)

Planning Time: x ms

Execution Time: x ms

(4 rows)

SELECT regexp_replace(t, '[\d\.]+', 'x', 'g')

FROM query_to_text($$ EXPLAIN (FORMAT YAML, ANALYZE, BUFFERS)

SELECT * FROM o_explain_formats

WHERE val > 5 ORDER BY val; $$) as t;

regexp_replace

---------------------------------------------

- Plan: +

Node Type: "Index Scan" +

Parallel Aware: false +

Async Capable: false +

Scan Direction: "Forward" +

Index Name: "o_explain_formats_val_key"+

Relation Name: "o_explain_formats" +

Alias: "o_explain_formats" +

Startup Cost: x +

Total Cost: x +

Plan Rows: x +

Plan Width: x +

Actual Startup Time: x +

Actual Total Time: x +

Actual Rows: x +

Actual Loops: x +

Index Cond: "(val > x)" +

Rows Removed by Index Recheck: x +

Shared Hit Blocks: x +

Shared Read Blocks: x +

Shared Dirtied Blocks: x +

Shared Written Blocks: x +

Local Hit Blocks: x +

Local Read Blocks: x +

Local Dirtied Blocks: x +

Local Written Blocks: x +

Temp Read Blocks: x +

Temp Written Blocks: x +

Planning: +

Shared Hit Blocks: x +

Shared Read Blocks: x +

Shared Dirtied Blocks: x +

Shared Written Blocks: x +

Local Hit Blocks: x +

Local Read Blocks: x +

Local Dirtied Blocks: x +

Local Written Blocks: x +

Temp Read Blocks: x +

Temp Written Blocks: x +

Planning Time: x +

Triggers: +