Hi, David,
On Sat, Feb 28, 2026 at 11:49 PM David G. Johnston
<[email protected]> wrote:
>
> On Saturday, February 28, 2026, Igor Korot <[email protected]> wrote:
>>
>>
>> draft=# WITH idx AS( SELECT i.indexrelid, i.indrelid, i.indnkeyatts,
>> i.indkey::int2[] AS indkey FROM pg_index i, pg_class ic, pg_namespace
>> ns
>>
>>
>> WHERE ic.oid = i.indexrelid AND ns.oid = ic.relnamespace AND
>> ns.nspname = 'public' AND ic.relname = 'leagues_new' )
>>
>>
>
> I doubt your index name is leagues_new. That is probably the table name.
> But ic.relname contains index names.
With some trial and error I think I finally get the query right ;-)
draft=# SELECT c.relname AS name, ixs.tablespace,
-- Get included columns (PostgreSQL 11+)
ARRAY(
SELECT a.attname
FROM pg_attribute a
WHERE a.attrelid = idx.indrelid
AND a.attnum = ANY(idx.indkey)
AND a.attnum > 0
ORDER BY array_position(idx.indkey, a.attnum)
OFFSET idx.indnkeyatts
) AS included,
c.reloptions AS storage
FROM pg_index idx, pg_class c, pg_namespace n, pg_class t, pg_indexes ixs
WHERE ixs.indexname = c.relname AND c.oid = idx.indexrelid AND t.oid =
idx.indrelid AND n.oid = c.relnamespace AND idx.indisprimary AND
n.nspname = 'public' AND t.relname = 'leagues_new';
name | tablespace | included | storage
------------------+------------+-------------------------+-----------------
leagues_new_pkey | | {drafttype,scoringtype} | {fillfactor=50}
(1 row)
draft=#
Thank you for sticking with me and sorry for the trouble.
I am getting everything in one DB hit and the query is not that big.
I am only worrying about tablespace filtering but I think I got it
right.
Thx once again.
P.S.: If you see any improvement - please by all means do let me know.
>
> David J.
