Subject: Re: Get info about the index

From: Adrian Klaver <[email-masked]>
Date: Sat, 26 Jul 2025 08:08:09 -0700

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
[email-masked]

---

From: Igor Korot <[email-masked]>
Date: Sat, 26 Jul 2025 12:41:48 -0400

Adrian,

> 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..

---

From: Christophe Pettus <[email-masked]>
Date: Sat, 26 Jul 2025 11:13:48 -0700

> - 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.

---

From: Igor Korot <[email-masked]>
Date: Sat, 26 Jul 2025 15:13:23 -0400

Hi, Christopher,

> for the index, which is in pg_class.

I didn't find the sorting for the field.

Can you help?

Thank you.

---

From: Laurenz Albe <[email-masked]>
Date: Sat, 26 Jul 2025 21:50:20 +0200

> 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

---

From: Jon Zeppieri <[email-masked]>
Date: Sat, 26 Jul 2025 15:50:53 -0400

> I didn't find the sorting for the field.

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

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

---

From: David Barbour <[email-masked]>
Date: Mon, 28 Jul 2025 08:19:56 -0500

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.

---

From: Laurenz Albe <[email-masked]>
Date: Mon, 28 Jul 2025 17:13:18 +0200

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

---

From: Igor Korot <[email-masked]>
Date: Tue, 29 Jul 2025 06:46:53 -0500

Hi, guys,

> 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&safe=active&sca_esv=32011946fbc5f18a&ei=i7KHaLSSEK6f5NoPpO-IgQ8&ved=0ahUKEwj0g_agieCOAxWuD1kFHaQ3IvAQ4dUDCBA&uact=5&oq=get+the+index+field+info+postgresql&gs_lp=Egxnd3Mtd2l6LXNlcnAiI2dldCB0aGUgaW5kZXggZmllbGQgaW5mbyBwb3N0Z3Jlc3FsMgUQABjvBTIIEAAYogQYiQUyCBAAGIAEGKIEMgUQABjvBUjBfFCyWViwdHABeACQAQCYAYABoAH_BaoBAzMuNLgBA8gBAPgBAZgCB6ACoAXCAgoQABiwAxjWBBhHmAMA4gMFEgExIECIBgGQBgiSBwMzLjSgB-0esgcDMi40uAebBcIHBTAuMS42yAcV&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.

---

From: Laurenz Albe <[email-masked]>
Date: Tue, 29 Jul 2025 14:07:25 +0200

> 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

> or the broken down one (field, condition {AND|OR} field, condition}?

   SELECT pg_get_expr(ix.indpred, t.oid)

Yours,
Laurenz Albe

---

From: Igor Korot <[email-masked]>
Date: Tue, 29 Jul 2025 07:18:15 -0500

> "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?

Thank you.