public inbox for [email protected]
help / color / mirror / Atom feedFrom: Igor Korot <[email protected]>
To: David G. Johnston <[email protected]>
Cc: pgsql-generallists.postgresql.org <[email protected]>
Subject: Re: Where the info is stored
Date: Sun, 1 Mar 2026 01:58:37 -0600
Message-ID: <CA+FnnTwGjCQFKxZ7Ug5DAScHSznXvGVANwAC9+f6kqWsymLsXQ@mail.gmail.com> (raw)
In-Reply-To: <CA+FnnTxc_8-GfyPM12D+w5i8+VXVsUzAcfpYjRYKqsxC5Rr3zw@mail.gmail.com>
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>
<CA+FnnTxc_8-GfyPM12D+w5i8+VXVsUzAcfpYjRYKqsxC5Rr3zw@mail.gmail.com>
It would be interesting to know what to bind ARRAY column
in ODBC code (the type in the SQLBindCol()), but that's a
different topic which is probably OT here.
Thx.
On Sun, Mar 1, 2026 at 1:50 AM Igor Korot <[email protected]> wrote:
>
> 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.
view thread (14+ messages) latest in thread
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected], [email protected], [email protected]
Subject: Re: Where the info is stored
In-Reply-To: <CA+FnnTwGjCQFKxZ7Ug5DAScHSznXvGVANwAC9+f6kqWsymLsXQ@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox