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 | 200 | 60 | 1.8 |
| 2 | 200 | 60 | 1.8 |
| 3 | 200 | 60 | 1.8 |
| 4 | 200 | 60 | 1.8 |
| 5 | 200 | 60 | 1.8 |
| 6 | 200 | 60 | 1.8 |
SELECT 6
truncate test; alter table test alter column id restart;
select setseed(.42);--this makes subsequent calls to `random()` repeatable
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 tablesample bernoulli(42)repeatable(.42)LIMIT 6;
TRUNCATE TABLE
ALTER TABLE
| setseed |
|---|
SELECT 1
INSERT 0 100
| id | contract | amount0 | amount1 | price |
|---|---|---|---|---|
| 3 | klm | 100 | 80 | 2.5 |
| 5 | klm | 200 | 50 | 2.1 |
| 9 | klm | 80 | 60 | 1.5 |
| 15 | abc | 80 | 200 | 2.1 |
| 18 | xyz | 50 | 60 | 1.8 |
| 19 | xyz | 100 | 60 | 1.8 |
SELECT 6
truncate test; alter table test alter column id restart;
select setseed(.42);--this makes subsequent calls to `random()` repeatable
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 tablesample bernoulli(42)repeatable(.42)LIMIT 6;
TRUNCATE TABLE
ALTER TABLE
| setseed |
|---|
SELECT 1
INSERT 0 100
| id | contract | amount0 | amount1 | price |
|---|---|---|---|---|
| 3 | klm | 100 | 80 | 2.5 |
| 5 | klm | 200 | 50 | 2.1 |
| 9 | klm | 80 | 60 | 1.5 |
| 15 | abc | 80 | 200 | 2.1 |
| 18 | xyz | 50 | 60 | 1.8 |
| 19 | xyz | 100 | 60 | 1.8 |
SELECT 6
truncate test; alter table test alter column id restart;
select setseed(.42);--array_sample can't be made repeatable using `setseed()`
INSERT INTO test (contract,amount0, amount1, price)
SELECT (array_sample(ARRAY['abc','klm','xyz'] ,1))[1],
(array_sample(ARRAY[50, 60, 80, 100, 200],1))[1],
(array_sample(ARRAY[50, 60, 80, 100, 200],1))[1],
(array_sample(ARRAY[1.5, 1.8, 2.1, 2.5] ,1))[1]
FROM generate_series(1, 1e2, 1);
SELECT id, contract,amount0, amount1, price
FROM test tablesample bernoulli(42)repeatable(.42)LIMIT 6;
TRUNCATE TABLE
ALTER TABLE
| setseed |
|---|
SELECT 1
INSERT 0 100
| id | contract | amount0 | amount1 | price |
|---|---|---|---|---|
| 3 | abc | 60 | 80 | 1.5 |
| 5 | abc | 60 | 50 | 1.8 |
| 9 | xyz | 100 | 60 | 1.5 |
| 15 | xyz | 60 | 80 | 2.5 |
| 18 | klm | 100 | 50 | 1.8 |
| 19 | abc | 80 | 100 | 1.8 |
SELECT 6
truncate test; alter table test alter column id restart;
select setseed(.42);--array_sample can't be made repeatable using `setseed()`
INSERT INTO test (contract,amount0, amount1, price)
SELECT (array_sample(ARRAY['abc','klm','xyz'] ,1))[1],
(array_sample(ARRAY[50, 60, 80, 100, 200],1))[1],
(array_sample(ARRAY[50, 60, 80, 100, 200],1))[1],
(array_sample(ARRAY[1.5, 1.8, 2.1, 2.5] ,1))[1]
FROM generate_series(1, 1e2, 1);
SELECT id, contract,amount0, amount1, price
FROM test tablesample bernoulli(42)repeatable(.42)LIMIT 6;
TRUNCATE TABLE
ALTER TABLE
| setseed |
|---|
SELECT 1
INSERT 0 100
| id | contract | amount0 | amount1 | price |
|---|---|---|---|---|
| 3 | abc | 60 | 60 | 2.5 |
| 5 | klm | 80 | 80 | 2.5 |
| 9 | klm | 60 | 100 | 1.5 |
| 15 | abc | 200 | 100 | 2.1 |
| 18 | xyz | 80 | 50 | 1.5 |
| 19 | klm | 200 | 200 | 1.5 |
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 |
|---|---|
| (xyz,100,100,1.5) | 3 |
| (klm,200,200,2.1) | 2 |
| (klm,60,100,1.5) | 2 |
| (klm,60,200,1.8) | 2 |
| (klm,200,100,1.5) | 2 |
| (klm,200,200,1.8) | 2 |
| (klm,100,50,1.5) | 2 |
| (abc,60,50,1.5) | 1 |
| (abc,50,200,2.5) | 1 |
| (abc,50,100,1.5) | 1 |
| (abc,60,100,2.5) | 1 |
| (abc,60,200,1.8) | 1 |
| (abc,60,200,2.5) | 1 |
| (abc,80,50,2.1) | 1 |
| (abc,60,80,1.8) | 1 |
| (abc,60,80,1.5) | 1 |
| (abc,50,200,1.5) | 1 |
| (abc,80,100,2.1) | 1 |
| (abc,80,100,2.5) | 1 |
| (abc,100,50,1.5) | 1 |
| (abc,100,50,1.8) | 1 |
| (abc,100,50,2.5) | 1 |
| (abc,100,100,2.1) | 1 |
| (abc,100,100,2.5) | 1 |
| (abc,100,200,2.5) | 1 |
| (abc,200,50,1.8) | 1 |
| (abc,200,50,2.1) | 1 |
| (abc,200,60,2.1) | 1 |
| (abc,200,60,2.5) | 1 |
| (abc,80,100,1.5) | 1 |
SELECT 30