Postgres 16

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?.

--https://stackoverflow.com/q/77244940/5298879
--OP's
create table test (
    id int generated by default as identity primary key, 
    contract varchar, 
    amount0 int, 
    amount1 int, 
    price double precision );

INSERT INTO test (amount0, amount1, price)
SELECT
  (SELECT val FROM unnest(ARRAY[50, 60, 80, 100, 200]) AS val ORDER BY random() LIMIT 1),
  (SELECT val FROM unnest(ARRAY[50, 60, 80, 100, 200]) AS val ORDER BY random() LIMIT 1),
  (SELECT val FROM unnest(ARRAY[1.5, 1.8, 2.1, 2.5]) AS val ORDER BY random() LIMIT 1)
FROM generate_series(1, 100);

SELECT id, amount0, amount1, price FROM test LIMIT 6;

CREATE TABLE



INSERT 0 100




id
amount0
amount1
price


1
60
60
1.5

2
60
60
1.5

3
60
60
1.5

4
60
60
1.5

5
60
60
1.5

6
60
60
1.5



SELECT 6


truncate test; alter table test alter column id restart;

INSERT INTO test (contract,amount0, amount1, price)
SELECT contract,amount0, amount1, price
FROM (values (50),(60),(80),(100),(200)) a(amount0),
     (values (50),(60),(80),(100),(200)) b(amount1),
     (values (1.5), (1.8), (2.1), (2.5)) c(price),
     (values ('abc'),('klm'),('xyz')   ) d(contract),
     generate_series(1, 1e2, 1) duplicator(n)
order by n, random()
limit 100;

SELECT id, contract,amount0, amount1, price FROM test LIMIT 6;

TRUNCATE TABLE



ALTER TABLE



INSERT 0 100




id
contract
amount0
amount1
price


1
abc
100
100
1.5

2
abc
60
60
2.5

3
xyz
60
50
2.1

4
klm
100
60
2.1

5
klm
200
200
1.5

6
klm
80
80
1.8



SELECT 6


--this shows how unique those combinations of options are
SELECT (contract,amount0, amount1, price) as combination, count(*) 
FROM test 
GROUP BY 1 ORDER BY 2 DESC, random()
LIMIT 6;


combination
count


(abc,60,60,2.1)
1

(abc,60,100,1.8)
1

(klm,50,200,2.5)
1

(abc,80,80,2.5)
1

(klm,50,60,2.1)
1

(abc,50,100,1.5)
1



SELECT 6


--You'll only see duplicates past 100 samples, 
--because that's when unique combinations run out and need to be recycled
truncate test; alter table test alter column id restart;

INSERT INTO test (contract,amount0, amount1, price)
SELECT contract,amount0, amount1, price
FROM (values (50),(60),(80),(100),(200)) a(amount0),
     (values (50),(60),(80),(100),(200)) b(amount1),
     (values (1.5), (1.8), (2.1), (2.5)) c(price),
     (values ('abc'),('klm'),('xyz')   ) d(contract),
     generate_series(1, 1e2, 1) duplicator(n)
order by n, random()
limit 602;

--this shows how unique those combinations of options are
SELECT (contract,amount0, amount1, price) as combination, count(*) 
FROM test 
GROUP BY 1 ORDER BY 2 DESC, random()
LIMIT 6;

TRUNCATE TABLE



ALTER TABLE



INSERT 0 602




combination
count


(xyz,60,100,2.1)
3

(abc,80,200,2.5)
3

(abc,50,60,1.5)
2

(xyz,200,80,2.5)
2

(klm,200,80,1.5)
2

(klm,80,60,2.5)
2



SELECT 6

id	contract	amount0	amount1	price
1	abc	100	100	1.5
2	abc	60	60	2.5
3	xyz	60	50	2.1
4	klm	100	60	2.1
5	klm	200	200	1.5
6	klm	80	80	1.8

combination	count
(abc,60,60,2.1)	1
(abc,60,100,1.8)	1
(klm,50,200,2.5)	1
(abc,80,80,2.5)	1
(klm,50,60,2.1)	1
(abc,50,100,1.5)	1

combination	count
(xyz,60,100,2.1)	3
(abc,80,200,2.5)	3
(abc,50,60,1.5)	2
(xyz,200,80,2.5)	2
(klm,200,80,1.5)	2
(klm,80,60,2.5)	2

id	contract	amount0	amount1	price
1	abc	100	100	1.5
2	abc	60	60	2.5
3	xyz	60	50	2.1
4	klm	100	60	2.1
5	klm	200	200	1.5
6	klm	80	80	1.8

id	contract	amount0	amount1	price
1	abc	100	100	1.5
2	abc	60	60	2.5
3	xyz	60	50	2.1
4	klm	100	60	2.1
5	klm	200	200	1.5
6	klm	80	80	1.8