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