Postgres 12

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

Help with an interesting Postgres question: Why isn't an Index Only Scan used on a partition accessed via the parent table?.

select version();


version


PostgreSQL 12.7 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.3.1 20191121 (Red Hat 8.3.1-5), 64-bit


SHOW TRANSACTION ISOLATION LEVEL;


transaction_isolation


read committed


CREATE TABLE tx
(
  id        SERIAL        PRIMARY KEY,
  oi        INTEGER       NOT NULL,     -- some random integer
  url       VARCHAR (265) NOT NULL,     -- whatever...
  epok      INTEGER       NOT NULL,  -- CHECK epok > xxxx AND epok <= yyyyy
  txn_id    INTEGER
);
CREATE TABLE tx_archiv
(
  id     INTEGER,
  oi     INTEGER NOT NULL,
  url    VARCHAR (265) NOT NULL,
  epok   INTEGER NOT NULL,  -- CHECK epok
  txn_id INTEGER
);
CREATE TABLE txn_test  -- for testing that all work was in  the same transaction...
(
  const INT NOT NULL, 
  txn_id INTEGER NOT NULL
); 
INSERT INTO tx (oi, url, epok) VALUES
(1, 'https://www.url.ie', EXTRACT(EPOCH FROM '2021-05-14 07:22:00'::TIMESTAMP)),
(1, 'https://www.url.ie', EXTRACT(EPOCH FROM '2021-05-13 07:22:00'::TIMESTAMP)),
(1, 'https://www.url.ie', EXTRACT(EPOCH FROM '2021-05-12 06:22:00'::TIMESTAMP)),
(2, 'https://www.url.ie', EXTRACT(EPOCH FROM '2021-05-11 06:22:00'::TIMESTAMP)),
(2, 'https://www.url.ie', EXTRACT(EPOCH FROM '2021-05-10 05:22:00'::TIMESTAMP)),
(2, 'https://www.url.ie', EXTRACT(EPOCH FROM '2021-05-09 05:22:00'::TIMESTAMP)),
(3, 'https://www.url.ie', EXTRACT(EPOCH FROM '2021-05-08 04:22:00'::TIMESTAMP)),
(3, 'https://www.url.ie', EXTRACT(EPOCH FROM '2021-05-07 04:22:00'::TIMESTAMP)),
(3, 'https://www.url.ie', EXTRACT(EPOCH FROM '2021-05-06 03:22:00'::TIMESTAMP)),

-- Below here to be deleted...

(4, 'https://www.url.ie', EXTRACT(EPOCH FROM '2020-09-09 22:00:12'::TIMESTAMP)),
(4, 'https://www.url.ie', EXTRACT(EPOCH FROM '2020-09-09 21:00:23'::TIMESTAMP)), 
(4, 'https://www.url.ie', EXTRACT(EPOCH FROM '2020-09-09 20:00:33'::TIMESTAMP)),
(5, 'https://www.url.ie', EXTRACT(EPOCH FROM '2020-09-09 19:00:59'::TIMESTAMP)),



(6, 'https://www.url.ie', EXTRACT(EPOCH FROM '2000-09-09 22:00:12'::TIMESTAMP)),
(6, 'https://www.url.ie', EXTRACT(EPOCH FROM '2000-09-09 21:00:23'::TIMESTAMP)), 
(7, 'https://www.url.ie', EXTRACT(EPOCH FROM '2000-09-09 20:00:33'::TIMESTAMP)),
(7, 'https://www.url.ie', EXTRACT(EPOCH FROM '2000-09-09 19:00:59'::TIMESTAMP));
17 rows affected

SELECT * FROM tx;
SELECT * FROM tx_archiv;


id
oi
url
epok
txn_id


1
1
https://www.url.ie
1620976920
null

2
1
https://www.url.ie
1620890520
null

3
1
https://www.url.ie
1620800520
null

4
2
https://www.url.ie
1620714120
null

5
2
https://www.url.ie
1620624120
null

6
2
https://www.url.ie
1620537720
null

7
3
https://www.url.ie
1620447720
null

8
3
https://www.url.ie
1620361320
null

9
3
https://www.url.ie
1620271320
null

10
4
https://www.url.ie
1599688812
null

11
4
https://www.url.ie
1599685223
null

12
4
https://www.url.ie
1599681633
null

13
5
https://www.url.ie
1599678059
null

14
6
https://www.url.ie
968536812
null

15
6
https://www.url.ie
968533223
null

16
7
https://www.url.ie
968529633
null

17
7
https://www.url.ie
968526059
null


WITH cte1 (id, txn) AS
(
  SELECT id,  txid_current() AS txn
  FROM tx
  WHERE EXTRACT(EPOCH FROM NOW()) - epok > 15613200
),
cte_test_1 (const, curr_tran) AS  -- <<=== JUST FOR TEST PUPOSES cte_test_1 inserts a txid_current
(                          -- which is also later INSERTed and SELECTed at the end - const = 1
  INSERT INTO txn_test 
  SELECT 1 AS const, txid_current() AS curr_tran FROM cte1
  RETURNING const, txid_current()
),
cte2 (id, txn2) AS
(
  INSERT INTO tx_archiv   
  SELECT id, oi, url, epok, txid_current() AS txn2  -- 2nd INSERT of txid_current()
    FROM tx WHERE id IN (SELECT id FROM cte1)
  RETURNING id, txid_current()
),
cte_test_2 (const, curr_tran) AS  -- <<=== JUST FOR TEST PUPOSES cte_test_1 inserts a txid_current
(                          -- which is also later INSERTed and SELECTed at the end - const = 2
  INSERT INTO txn_test 
  SELECT 2 AS const, txid_current() AS curr_tran FROM cte1
  RETURNING const, txid_current()
),
cte3 (id, txn3) AS
(
  DELETE FROM tx WHERE id IN
  (SELECT id FROM cte2)
  RETURNING id, txid_current()
)
SELECT * FROM cte3;


id
txn3


10
958571

11
958571

12
958571

13
958571

14
958571

15
958571

16
958571

17
958571


SELECT 
  txid_current(), 
  txid_status(txid_current()), 
  xmin, 
  xmax, 
  cmin, 
  cmax, 
  ctid, * 
FROM tx;


txid_current
txid_status
xmin
xmax
cmin
cmax
ctid
id
oi
url
epok
txn_id


958572
in progress
958570
0
0
0
(0,1)
1
1
https://www.url.ie
1620976920
null

958572
in progress
958570
0
0
0
(0,2)
2
1
https://www.url.ie
1620890520
null

958572
in progress
958570
0
0
0
(0,3)
3
1
https://www.url.ie
1620800520
null

958572
in progress
958570
0
0
0
(0,4)
4
2
https://www.url.ie
1620714120
null

958572
in progress
958570
0
0
0
(0,5)
5
2
https://www.url.ie
1620624120
null

958572
in progress
958570
0
0
0
(0,6)
6
2
https://www.url.ie
1620537720
null

958572
in progress
958570
0
0
0
(0,7)
7
3
https://www.url.ie
1620447720
null

958572
in progress
958570
0
0
0
(0,8)
8
3
https://www.url.ie
1620361320
null

958572
in progress
958570
0
0
0
(0,9)
9
3
https://www.url.ie
1620271320
null


SELECT 
  txid_current(), 
  txid_status(txid_current()), 
  xmin, 
  xmax, 
  cmin, 
  cmax, 
  ctid, * 
FROM txn_test ORDER BY const;


txid_current
txid_status
xmin
xmax
cmin
cmax
ctid
const
txn_id


958573
in progress
958571
0
0
0
(0,16)
1
958571

958573
in progress
958571
0
0
0
(0,9)
1
958571

958573
in progress
958571
0
0
0
(0,10)
1
958571

958573
in progress
958571
0
0
0
(0,11)
1
958571

958573
in progress
958571
0
0
0
(0,12)
1
958571

958573
in progress
958571
0
0
0
(0,13)
1
958571

958573
in progress
958571
0
0
0
(0,14)
1
958571

958573
in progress
958571
0
0
0
(0,15)
1
958571

958573
in progress
958571
0
0
0
(0,1)
2
958571

958573
in progress
958571
0
0
0
(0,2)
2
958571

958573
in progress
958571
0
0
0
(0,3)
2
958571

958573
in progress
958571
0
0
0
(0,4)
2
958571

958573
in progress
958571
0
0
0
(0,5)
2
958571

958573
in progress
958571
0
0
0
(0,6)
2
958571

958573
in progress
958571
0
0
0
(0,7)
2
958571

958573
in progress
958571
0
0
0
(0,8)
2
958571


SELECT 
  txid_current(), 
  txid_status(txid_current()), 
  xmin, 
  xmax, 
  cmin, 
  cmax, 
  ctid, * 
FROM tx_archiv;


txid_current
txid_status
xmin
xmax
cmin
cmax
ctid
id
oi
url
epok
txn_id


958574
in progress
958571
0
0
0
(0,1)
10
4
https://www.url.ie
1599688812
958571

958574
in progress
958571
0
0
0
(0,2)
11
4
https://www.url.ie
1599685223
958571

958574
in progress
958571
0
0
0
(0,3)
12
4
https://www.url.ie
1599681633
958571

958574
in progress
958571
0
0
0
(0,4)
13
5
https://www.url.ie
1599678059
958571

958574
in progress
958571
0
0
0
(0,5)
14
6
https://www.url.ie
968536812
958571

958574
in progress
958571
0
0
0
(0,6)
15
6
https://www.url.ie
968533223
958571

958574
in progress
958571
0
0
0
(0,7)
16
7
https://www.url.ie
968529633
958571

958574
in progress
958571
0
0
0
(0,8)
17
7
https://www.url.ie
968526059
958571


SHOW TRANSACTION ISOLATION LEVEL;


transaction_isolation


read committed

id	oi	url	epok	txn_id
1	1	https://www.url.ie	1620976920	null
2	1	https://www.url.ie	1620890520	null
3	1	https://www.url.ie	1620800520	null
4	2	https://www.url.ie	1620714120	null
5	2	https://www.url.ie	1620624120	null
6	2	https://www.url.ie	1620537720	null
7	3	https://www.url.ie	1620447720	null
8	3	https://www.url.ie	1620361320	null
9	3	https://www.url.ie	1620271320	null
10	4	https://www.url.ie	1599688812	null
11	4	https://www.url.ie	1599685223	null
12	4	https://www.url.ie	1599681633	null
13	5	https://www.url.ie	1599678059	null
14	6	https://www.url.ie	968536812	null
15	6	https://www.url.ie	968533223	null
16	7	https://www.url.ie	968529633	null
17	7	https://www.url.ie	968526059	null

id	txn3
10	958571
11	958571
12	958571
13	958571
14	958571
15	958571
16	958571
17	958571

txid_current	txid_status	xmin	ctid	id	oi	url	epok	txn_id
958572	in progress	958570	(0,1)	1	1	https://www.url.ie	1620976920	null
958572	in progress	958570	(0,2)	2	1	https://www.url.ie	1620890520	null
958572	in progress	958570	(0,3)	3	1	https://www.url.ie	1620800520	null
958572	in progress	958570	(0,4)	4	2	https://www.url.ie	1620714120	null
958572	in progress	958570	(0,5)	5	2	https://www.url.ie	1620624120	null
958572	in progress	958570	(0,6)	6	2	https://www.url.ie	1620537720	null
958572	in progress	958570	(0,7)	7	3	https://www.url.ie	1620447720	null
958572	in progress	958570	(0,8)	8	3	https://www.url.ie	1620361320	null
958572	in progress	958570	(0,9)	9	3	https://www.url.ie	1620271320	null

txid_current	txid_status	xmin	ctid	const	txn_id
958573	in progress	958571	(0,16)	1	958571
958573	in progress	958571	(0,9)	1	958571
958573	in progress	958571	(0,10)	1	958571
958573	in progress	958571	(0,11)	1	958571
958573	in progress	958571	(0,12)	1	958571
958573	in progress	958571	(0,13)	1	958571
958573	in progress	958571	(0,14)	1	958571
958573	in progress	958571	(0,15)	1	958571
958573	in progress	958571	(0,1)	2	958571
958573	in progress	958571	(0,2)	2	958571
958573	in progress	958571	(0,3)	2	958571
958573	in progress	958571	(0,4)	2	958571
958573	in progress	958571	(0,5)	2	958571
958573	in progress	958571	(0,6)	2	958571
958573	in progress	958571	(0,7)	2	958571
958573	in progress	958571	(0,8)	2	958571

txid_current	txid_status	xmin	ctid	id	oi	url	epok	txn_id
958574	in progress	958571	(0,1)	10	4	https://www.url.ie	1599688812	958571
958574	in progress	958571	(0,2)	11	4	https://www.url.ie	1599685223	958571
958574	in progress	958571	(0,3)	12	4	https://www.url.ie	1599681633	958571
958574	in progress	958571	(0,4)	13	5	https://www.url.ie	1599678059	958571
958574	in progress	958571	(0,5)	14	6	https://www.url.ie	968536812	958571
958574	in progress	958571	(0,6)	15	6	https://www.url.ie	968533223	958571
958574	in progress	958571	(0,7)	16	7	https://www.url.ie	968529633	958571
958574	in progress	958571	(0,8)	17	7	https://www.url.ie	968526059	958571