Get info about the index

On 7/26/25 08:00, Igor Korot wrote:

Hi, ALL,
Is there a better place to get the info about the index other than
https://www.postgresql.org/docs/current/view-pg-indexes.html

What information do you need?

That guy has a full blown CREATE INDEX command and I will need to
parse it to get the info I need.

Thank you.

--
Adrian Klaver
adrian.klaver@aklaver.com

On Sat, 26 Jul 2025, 17:00 Igor Korot, <ikorot01@gmail.com> wrote:

Hi, ALL,
Is there a better place to get the info about the index other than
https://www.postgresql.org/docs/current/view-pg-indexes.html

That guy has a full blown CREATE INDEX command and I will need to
parse it to get the info I need.

Thank you.

What kind of info do you need?

--
Innehållet i detta e-postmeddelande är konfidentiellt och avsett endast för
adressaten.Varje spridning, kopiering eller utnyttjande av innehållet är
förbjuden utan tillåtelse av avsändaren. Om detta meddelande av misstag
gått till fel adressat vänligen radera det ursprungliga meddelandet och
underrätta avsändaren via e-post

On Sat, Jul 26, 2025, 08:00 Igor Korot <ikorot01@gmail.com> wrote:

Hi, ALL,
Is there a better place to get the info about the index other than
https://www.postgresql.org/docs/current/view-pg-indexes.html

That guy has a full blown CREATE INDEX command and I will need to
parse it to get the info I need.

Follow the foreign keys.

All useful info does get parsed out into columns somewhere. The system
doesn't parse a create index command everytime it needs to inspect the
properties of the index.

David J.

On 7/26/25 08:00, Igor Korot wrote:

Hi, ALL,
Is there a better place to get the info about the index other than
https://www.postgresql.org/docs/current/view-pg-indexes.html

That guy has a full blown CREATE INDEX command and I will need to
parse it to get the info I need.

FYI,

pg_class:

https://www.postgresql.org/docs/current/catalog-pg-class.html

and pg_attribute:

https://www.postgresql.org/docs/current/catalog-pg-attribute.html

also have index information.

Thank you.

--
Adrian Klaver
adrian.klaver@aklaver.com

Adrian Klaver <adrian.klaver@aklaver.com> writes:

On 7/26/25 08:00, Igor Korot wrote:

Is there a better place to get the info about the index other than
https://www.postgresql.org/docs/current/view-pg-indexes.html

pg_class:
https://www.postgresql.org/docs/current/catalog-pg-class.html
and pg_attribute:
https://www.postgresql.org/docs/current/catalog-pg-attribute.html
also have index information.

pg_index is the real source of truth here.

regards, tom lane

Adrian,

On Sat, Jul 26, 2025 at 11:08 AM Adrian Klaver
<adrian.klaver@aklaver.com> wrote:

On 7/26/25 08:00, Igor Korot wrote:

Hi, ALL,
Is there a better place to get the info about the index other than
https://www.postgresql.org/docs/current/view-pg-indexes.html

What information do you need?

This is the command for creating the index:
https://www.postgresql.org/docs/current/sql-createindex.html.

I want to know all individual things:
- whether the index is unique.
- what type of index it is
- whether the field is ASC or DESC.
- all other individual arams

I don't want to parse the command since I'm sure I can query the DB
to get the info.

Thank you..

I want to know all individual things:
- whether the index is unique.
- what type of index it is
- whether the field is ASC or DESC.
- all other individual arams

pg_index is the source for those. The one exception is the access method for the index, which is in pg_class.

Hi, Christopher,

On Sat, Jul 26, 2025, 2:14 PM Christophe Pettus <xof@thebuild.com> wrote:

I want to know all individual things:
- whether the index is unique.
- what type of index it is
- whether the field is ASC or DESC.
- all other individual arams

pg_index is the source for those. The one exception is the access method
for the index, which is in pg_class.

I didn't find the sorting for the field.

Can you help?

Thank you.

On Sat, 2025-07-26 at 15:13 -0400, Igor Korot wrote:

On Sat, Jul 26, 2025, 2:14 PM Christophe Pettus <xof@thebuild.com> wrote:

I want to know all individual things:
- whether the index is unique.
- what type of index it is
- whether the field is ASC or DESC.
- all other individual arams

pg_index is the source for those.  The one exception is the access method for the index, which is in pg_class.

I didn't find the sorting for the field.

That's in pg_index.indoption.

The flags stored there are defined in src/include/catalog/pg_index.h

/*
* Index AMs that support ordered scans must support these two indoption
* bits. Otherwise, the content of the per-column indoption fields is
* open for future definition.
*/
#define INDOPTION_DESC 0x0001 /* values are in reverse order */
#define INDOPTION_NULLS_FIRST 0x0002 /* NULLs are first instead of last */

So a value of 3 would mean DESC NULLS FIRST.

Yours,
Laurenz Albe

On Sat, Jul 26, 2025 at 3:13 PM Igor Korot <ikorot01@gmail.com> wrote:

I didn't find the sorting for the field.

Can you help?

The pg_index_column_has_property() can provide this information. E.g.,

select pg_index_column_has_property('my_index'::regclass, 2, 'desc');

Couple of suggestions. You might try ChatGPT. I've had some success using
this tool to uncover improvements to the use of indexes. The other would
be to look at https://explain.depesz.com/. It's pretty self-explanatory.
You run an explain plan and paste the results into the tool and it will run
an automated analysis.

On Sat, Jul 26, 2025 at 2:51 PM Jon Zeppieri <zeppieri@gmail.com> wrote:

On Mon, 2025-07-28 at 08:19 -0500, David Barbour wrote:

Couple of suggestions.  You might try ChatGPT.

Please don't be insulting. He asked for real information.

Yours,
Laurenz Albe

Hi, guys,

On Mon, Jul 28, 2025 at 10:13 AM Laurenz Albe <laurenz.albe@cybertec.at> wrote:

On Mon, 2025-07-28 at 08:19 -0500, David Barbour wrote:

Couple of suggestions. You might try ChatGPT.

Please don't be insulting. He asked for real information.

I finally formulate my google request and got this:

https://www.google.com/search?q=get+the+index+field+info+postgresql&amp;safe=active&amp;sca_esv=32011946fbc5f18a&amp;ei=i7KHaLSSEK6f5NoPpO-IgQ8&amp;ved=0ahUKEwj0g_agieCOAxWuD1kFHaQ3IvAQ4dUDCBA&amp;uact=5&amp;oq=get+the+index+field+info+postgresql&amp;gs_lp=Egxnd3Mtd2l6LXNlcnAiI2dldCB0aGUgaW5kZXggZmllbGQgaW5mbyBwb3N0Z3Jlc3FsMgUQABjvBTIIEAAYogQYiQUyCBAAGIAEGKIEMgUQABjvBUjBfFCyWViwdHABeACQAQCYAYABoAH_BaoBAzMuNLgBA8gBAPgBAZgCB6ACoAXCAgoQABiwAxjWBBhHmAMA4gMFEgExIECIBgGQBgiSBwMzLjSgB-0esgcDMi40uAebBcIHBTAuMS42yAcV&amp;sclient=gws-wiz-serp

SELECT
t.relname AS table_name,
i.relname AS index_name,
a.attname AS column_name
FROM
pg_class t,
pg_class i,
pg_index ix,
pg_attribute a
WHERE
t.oid = ix.indrelid AND
i.oid = ix.indexrelid AND
a.attrelid = t.oid AND
a.attnum = ANY(ix.indkey) AND
t.relkind = 'r' AND -- 'r' for regular table
t.relname = 'your_table_name' -- Optional: filter by table name
ORDER BY
t.relname,
i.relname,
a.attnum;

I can build on top of this query, however I have 2 issues:

First and most important one - they are filtering by just table name.
How can I filter by the fully qualified name - catalog.schema.table?

Second - how cn I get the partial index condition? Either the whole
WHERE clause (which I will have to parse)
or the broken down one (field, condition {AND|OR} field, condition}?

Thank you.

On Tue, 2025-07-29 at 06:46 -0500, Igor Korot wrote:

SELECT
    t.relname AS table_name,
    i.relname AS index_name,
    a.attname AS column_name
FROM
    pg_class t,
    pg_class i,
    pg_index ix,
    pg_attribute a
WHERE
    t.oid = ix.indrelid AND
    i.oid = ix.indexrelid AND
    a.attrelid = t.oid AND
    a.attnum = ANY(ix.indkey) AND
    t.relkind = 'r' AND -- 'r' for regular table
    t.relname = 'your_table_name' -- Optional: filter by table name
ORDER BY
    t.relname,
    i.relname,
    a.attnum;

I can build on top of this query, however I have 2 issues:

First and most important one - they are filtering by just table name.
How can I filter by the fully qualified name - catalog.schema.table?

"catalog" is irrelevant, since PostgreSQL doesn't allow cross-database queries.

To add a filter for the schema, use

AND t.relnamespace = 'schemaname'::regnamespace

Second - how cn I get the partial index condition? Either the whole
WHERE clause (which I will have to parse)
or the broken down one (field, condition {AND|OR} field, condition}?

SELECT pg_get_expr(ix.indpred, t.oid)

Yours,
Laurenz Albe

On Tue, Jul 29, 2025 at 7:07 AM Laurenz Albe <laurenz.albe@cybertec.at> wrote:

On Tue, 2025-07-29 at 06:46 -0500, Igor Korot wrote:

SELECT
t.relname AS table_name,
i.relname AS index_name,
a.attname AS column_name
FROM
pg_class t,
pg_class i,
pg_index ix,
pg_attribute a
WHERE
t.oid = ix.indrelid AND
i.oid = ix.indexrelid AND
a.attrelid = t.oid AND
a.attnum = ANY(ix.indkey) AND
t.relkind = 'r' AND -- 'r' for regular table
t.relname = 'your_table_name' -- Optional: filter by table name
ORDER BY
t.relname,
i.relname,
a.attnum;

I can build on top of this query, however I have 2 issues:

First and most important one - they are filtering by just table name.
How can I filter by the fully qualified name - catalog.schema.table?

"catalog" is irrelevant, since PostgreSQL doesn't allow cross-database queries.

The reason I'm asking this is because I want to have the same
interface for different DBMSes.

But it also made me curious.

If I have a database for some financial data for the year 2024 in the
mydb_2024 and I have current year financial data in the mydb_2025 how
can I compare the data?

To add a filter for the schema, use

AND t.relnamespace = 'schemaname'::regnamespace

Second - how cn I get the partial index condition? Either the whole
WHERE clause (which I will have to parse)
or the broken down one (field, condition {AND|OR} field, condition}?

SELECT pg_get_expr(ix.indpred, t.oid)

Yours,
Laurenz Albe

Thank you.

On Tue, 2025-07-29 at 07:18 -0500, Igor Korot wrote:

If I have a database for some financial data for the year 2024 in the
mydb_2024 and I have current year financial data in the mydb_2025 how
can I compare the data?

You connect to both and query them both.

Alternatively, you use postgres_fdw to access the other database.

Even better, put both tables in the same database, perhaps in different schemas.

Yours,
Laurenz Albe

On Tue, Jul 29, 2025, 9:24 AM Laurenz Albe <laurenz.albe@cybertec.at> wrote:

On Tue, 2025-07-29 at 07:18 -0500, Igor Korot wrote:

If I have a database for some financial data for the year 2024 in the
mydb_2024 and I have current year financial data in the mydb_2025 how
can I compare the data?

You connect to both and query them both.

Alternatively, you use postgres_fdw to access the other database.

Even better, put both tables in the same database, perhaps in different
schemas.

Understood.

It was just an educational question to better understand all possible ways
to proceed.

Thank you.

Hi, Laurenz,

On Tue, Jul 29, 2025 at 7:07 AM Laurenz Albe <laurenz.albe@cybertec.at> wrote:

On Tue, 2025-07-29 at 06:46 -0500, Igor Korot wrote:

SELECT
t.relname AS table_name,
i.relname AS index_name,
a.attname AS column_name
FROM
pg_class t,
pg_class i,
pg_index ix,
pg_attribute a
WHERE
t.oid = ix.indrelid AND
i.oid = ix.indexrelid AND
a.attrelid = t.oid AND
a.attnum = ANY(ix.indkey) AND
t.relkind = 'r' AND -- 'r' for regular table
t.relname = 'your_table_name' -- Optional: filter by table name
ORDER BY
t.relname,
i.relname,
a.attnum;

I can build on top of this query, however I have 2 issues:

First and most important one - they are filtering by just table name.
How can I filter by the fully qualified name - catalog.schema.table?

"catalog" is irrelevant, since PostgreSQL doesn't allow cross-database queries.

To add a filter for the schema, use

AND t.relnamespace = 'schemaname'::regnamespace

Second - how cn I get the partial index condition? Either the whole
WHERE clause (which I will have to parse)
or the broken down one (field, condition {AND|OR} field, condition}?

SELECT pg_get_expr(ix.indpred, t.oid)

I'm looking at the pg_index table and I see it has:

[quote]
indisexclusion bool

If true, this index supports an exclusion constraint
[/quote]

If I read the docs correctly, this field indicates whether the
WHERE condition is actually present.

Am I right?

Thank you.

On 8/1/25 17:06, Igor Korot wrote:

Hi, Laurenz,

I'm looking at the pg_index table and I see it has:

[quote]
indisexclusion bool

If true, this index supports an exclusion constraint
[/quote]

If I read the docs correctly, this field indicates whether the
WHERE condition is actually present.

Am I right?

No, it refers to:

https://www.postgresql.org/docs/current/ddl-constraints.html#DDL-CONSTRAINTS-EXCLUSION

Thank you.

Yours,
Laurenz Albe

--
Adrian Klaver
adrian.klaver@aklaver.com