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
50
100
2.5

2
50
100
2.5

3
50
100
2.5

4
50
100
2.5

5
50
100
2.5

6
50
100
2.5



SELECT 6


truncate test; alter table test alter column id restart;

INSERT INTO test (contract,amount0, amount1, price)
SELECT (ARRAY['abc','klm','xyz'])[(random()*2+1)::int],
       (ARRAY[50, 60, 80, 100, 200])[(random()*4+1)::int],
       (ARRAY[50, 60, 80, 100, 200])[(random()*4+1)::int],
       (ARRAY[1.5, 1.8, 2.1, 2.5])[(random()*3+1)::int]
FROM generate_series(1, 1e2, 1);

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

TRUNCATE TABLE



ALTER TABLE



INSERT 0 100




id
contract
amount0
amount1
price


1
xyz
60
80
2.5

2
klm
80
200
1.5

3
abc
60
100
1.8

4
abc
60
60
2.5

5
xyz
50
80
1.8

6
abc
60
50
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
LIMIT 30;


combination
count


(klm,80,100,2.1)
3

(abc,60,100,1.8)
2

(klm,80,60,2.1)
2

(xyz,80,80,2.1)
2

(klm,50,100,2.1)
2

(abc,60,80,2.1)
2

(klm,200,100,2.1)
2

(klm,100,50,2.1)
2

(xyz,100,80,2.1)
2

(xyz,100,100,2.1)
2

(xyz,60,100,1.5)
2

(abc,80,100,1.5)
1

(abc,60,80,1.8)
1

(abc,80,100,1.8)
1

(abc,80,60,2.5)
1

(abc,80,60,1.8)
1

(abc,60,60,2.5)
1

(abc,50,80,2.1)
1

(abc,100,100,1.5)
1

(abc,80,50,1.8)
1

(abc,100,80,2.1)
1

(abc,100,100,2.1)
1

(abc,100,200,1.8)
1

(abc,200,50,2.1)
1

(abc,200,80,2.1)
1

(abc,200,100,2.1)
1

(abc,200,200,2.1)
1

(klm,50,60,2.1)
1

(klm,50,60,2.5)
1

(abc,100,80,1.5)
1



SELECT 30

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

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

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

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