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 14.10 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.5.0 20210514 (Red Hat 8.5.0-20), 64-bit |
SELECT 1
create table ruuids( id uuid default gen_random_uuid() primary key
, name text
);
insert into ruuids(name)
select 'Item #' || to_char(n,'fm009')
from generate_series (1,20) gs(n);
select *
from ruuids;
CREATE TABLE
INSERT 0 20
| id | name |
|---|---|
| 91ee76f7-e56c-43d8-ad65-68ebfe6b9f51 | Item #001 |
| 3abba23d-0516-4002-8884-765bce376947 | Item #002 |
| 43d1e5f6-4a41-4ee5-b9ef-b27114811312 | Item #003 |
| 829365df-8a71-48ce-a317-73526b36ffd1 | Item #004 |
| 75856f2b-be29-4383-9f3f-09d6db317ca0 | Item #005 |
| ef8dfae2-7ab2-4635-ab9e-b12bf184e406 | Item #006 |
| 1cacaff4-c406-4889-b5ed-edc55a463e65 | Item #007 |
| 6a9aab4e-dddc-487c-898c-63801435c3c1 | Item #008 |
| 12f176ef-7611-4cee-b8a0-203c3c9517fa | Item #009 |
| 1a89c2ef-8f87-4b5f-87b6-bb9a55cf44c8 | Item #010 |
| 441edce6-ee06-4f22-9481-a79037b85f34 | Item #011 |
| 054b67a9-6763-4ca3-95a2-d0a63d8c5106 | Item #012 |
| 2370e727-b8b7-451f-8db5-d3b6f0f99d91 | Item #013 |
| e67eb8c7-cc68-4dd8-8160-e98c89eea2ed | Item #014 |
| 36115bfd-8f2c-413f-9f9b-9f698ef2688a | Item #015 |
| 2ef8a0e1-8504-493d-81dd-bea080c5eeeb | Item #016 |
| e8b606c5-0054-43b8-a61e-00ba797656c6 | Item #017 |
| c1e8ebec-b55b-40d2-94f3-9605d6e9a37f | Item #018 |
| fa11491c-5934-4c88-8592-e3c34c49b997 | Item #019 |
| 945a3c3c-50a6-4df7-b1f7-6b890a6f071f | Item #020 |
SELECT 20
select *
from ruuids
order by random()
limit 5;
| id | name |
|---|---|
| ef8dfae2-7ab2-4635-ab9e-b12bf184e406 | Item #006 |
| 829365df-8a71-48ce-a317-73526b36ffd1 | Item #004 |
| 945a3c3c-50a6-4df7-b1f7-6b890a6f071f | Item #020 |
| 12f176ef-7611-4cee-b8a0-203c3c9517fa | Item #009 |
| 3abba23d-0516-4002-8884-765bce376947 | Item #002 |
SELECT 5
select *
from ruuids
order by random()
limit 5;
| id | name |
|---|---|
| fa11491c-5934-4c88-8592-e3c34c49b997 | Item #019 |
| 36115bfd-8f2c-413f-9f9b-9f698ef2688a | Item #015 |
| 43d1e5f6-4a41-4ee5-b9ef-b27114811312 | Item #003 |
| 829365df-8a71-48ce-a317-73526b36ffd1 | Item #004 |
| 91ee76f7-e56c-43d8-ad65-68ebfe6b9f51 | Item #001 |
SELECT 5