MIME-Version: 1.0
References: <CA+FnnTwmt7BOqwZnsB5LOq47U+wzRc86wXaea9B0KEXfK2O+Kw@mail.gmail.com>
 <CAKFQuwa2ef9Bxz79Q8JtVNxmMABN829EJqiLKXxB5=5AEHyr9w@mail.gmail.com>
 <CAKFQuwa9PsXitAWuG1hg-8EPOu0AdB2TiANaBRJfgSrdjioa+Q@mail.gmail.com>
 <CA+FnnTz51Ond5HGkDgKvRB6ERi3pr5Q1wTnwoMK8rCwsm79_sA@mail.gmail.com>
 <CAKFQuwa7rK-NfVwHT7_Nob6S=GN-tKK7F1+eLQ-FQZMsW3gd8g@mail.gmail.com>
 <CA+FnnTw0bNsDBKFEQKsrjFZKqiZgRVZ27zhgXs5sqCLW=bwBVw@mail.gmail.com>
 <CA+FnnTwEWFq1BopZ4o_kEg3SuZvGE+Xhg1sur=c_HjcZ_FjTag@mail.gmail.com>
 <CAKFQuwbTpwDiUy=azMGk8tMo7w2ysWC1h02NmURCqGRqcAhSCw@mail.gmail.com>
 <CA+FnnTwaK-Z26KeqoPsAkKPJHo9OjMJdSse8tXE-KSFV9y0g_Q@mail.gmail.com> <CAKFQuwZ=4-DTzeUwgmSNv1bctyq+aZnjZSsV2xdQDGr406AnOA@mail.gmail.com>
In-Reply-To: <CAKFQuwZ=4-DTzeUwgmSNv1bctyq+aZnjZSsV2xdQDGr406AnOA@mail.gmail.com>
From: Igor Korot <ikorot01@gmail.com>
Date: Sun, 1 Mar 2026 01:50:52 -0600
Message-ID: <CA+FnnTxc_8-GfyPM12D+w5i8+VXVsUzAcfpYjRYKqsxC5Rr3zw@mail.gmail.com>
Subject: Re: Where the info is stored
To: "David G. Johnston" <david.g.johnston@gmail.com>
Cc: "pgsql-generallists.postgresql.org" <pgsql-general@lists.postgresql.org>
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
Archived-At: <https://www.postgresql.org/message-id/CA%2BFnnTxc_8-GfyPM12D%2Bw5i8%2BVXVsUzAcfpYjRYKqsxC5Rr3zw%40mail.gmail.com>
Precedence: bulk

Hi, David,

On Sat, Feb 28, 2026 at 11:49=E2=80=AFPM David G. Johnston
<david.g.johnston@gmail.com> wrote:
>
> On Saturday, February 28, 2026, Igor Korot <ikorot01@gmail.com> wrote:
>>
>>
>> draft=3D#  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 =3D i.indexrelid AND ns.oid =3D ic.relnamespace AND
>> ns.nspname =3D 'public' AND ic.relname =3D '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=3D# SELECT c.relname AS name, ixs.tablespace,
    -- Get included columns (PostgreSQL 11+)
    ARRAY(
        SELECT a.attname
        FROM pg_attribute a
        WHERE a.attrelid =3D idx.indrelid
          AND a.attnum =3D 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 =3D c.relname AND c.oid =3D idx.indexrelid AND t.oid =
=3D
idx.indrelid AND n.oid =3D c.relnamespace AND idx.indisprimary AND
n.nspname =3D 'public' AND t.relname =3D 'leagues_new';
       name       | tablespace |        included         |     storage
------------------+------------+-------------------------+-----------------
 leagues_new_pkey |            | {drafttype,scoringtype} | {fillfactor=3D50=
}
(1 row)

draft=3D#

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.