public inbox for [email protected]
help / color / mirror / Atom feedRe: Get info about the index
11+ messages / 6 participants
[nested] [flat]
* Re: Get info about the index
@ 2025-07-26 15:08 Adrian Klaver <[email protected]>
2025-07-26 16:41 ` Re: Get info about the index Igor Korot <[email protected]>
0 siblings, 1 reply; 11+ messages in thread
From: Adrian Klaver @ 2025-07-26 15:08 UTC (permalink / raw)
To: Igor Korot <[email protected]>; pgsql-generallists.postgresql.org <[email protected]>
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 protected]
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Get info about the index
2025-07-26 15:08 Re: Get info about the index Adrian Klaver <[email protected]>
@ 2025-07-26 16:41 ` Igor Korot <[email protected]>
2025-07-26 18:13 ` Re: Get info about the index Christophe Pettus <[email protected]>
0 siblings, 1 reply; 11+ messages in thread
From: Igor Korot @ 2025-07-26 16:41 UTC (permalink / raw)
To: Adrian Klaver <[email protected]>; +Cc: pgsql-generallists.postgresql.org <[email protected]>
Adrian,
On Sat, Jul 26, 2025 at 11:08 AM Adrian Klaver
<[email protected]> 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..
>
> >
> > 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 protected]
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Get info about the index
2025-07-26 15:08 Re: Get info about the index Adrian Klaver <[email protected]>
2025-07-26 16:41 ` Re: Get info about the index Igor Korot <[email protected]>
@ 2025-07-26 18:13 ` Christophe Pettus <[email protected]>
2025-07-26 19:13 ` Re: Get info about the index Igor Korot <[email protected]>
0 siblings, 1 reply; 11+ messages in thread
From: Christophe Pettus @ 2025-07-26 18:13 UTC (permalink / raw)
To: Igor Korot <[email protected]>; +Cc: Adrian Klaver <[email protected]>; pgsql-generallists.postgresql.org <[email protected]>
> 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.
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Get info about the index
2025-07-26 15:08 Re: Get info about the index Adrian Klaver <[email protected]>
2025-07-26 16:41 ` Re: Get info about the index Igor Korot <[email protected]>
2025-07-26 18:13 ` Re: Get info about the index Christophe Pettus <[email protected]>
@ 2025-07-26 19:13 ` Igor Korot <[email protected]>
2025-07-26 19:50 ` Re: Get info about the index Laurenz Albe <[email protected]>
2025-07-26 19:50 ` Re: Get info about the index Jon Zeppieri <[email protected]>
0 siblings, 2 replies; 11+ messages in thread
From: Igor Korot @ 2025-07-26 19:13 UTC (permalink / raw)
To: Christophe Pettus <[email protected]>; +Cc: Adrian Klaver <[email protected]>; pgsql-generallists.postgresql.org <[email protected]>
Hi, Christopher,
On Sat, Jul 26, 2025, 2:14 PM Christophe Pettus <[email protected]> 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.
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Get info about the index
2025-07-26 15:08 Re: Get info about the index Adrian Klaver <[email protected]>
2025-07-26 16:41 ` Re: Get info about the index Igor Korot <[email protected]>
2025-07-26 18:13 ` Re: Get info about the index Christophe Pettus <[email protected]>
2025-07-26 19:13 ` Re: Get info about the index Igor Korot <[email protected]>
@ 2025-07-26 19:50 ` Laurenz Albe <[email protected]>
1 sibling, 0 replies; 11+ messages in thread
From: Laurenz Albe @ 2025-07-26 19:50 UTC (permalink / raw)
To: Igor Korot <[email protected]>; Christophe Pettus <[email protected]>; +Cc: Adrian Klaver <[email protected]>; pgsql-generallists.postgresql.org <[email protected]>
On Sat, 2025-07-26 at 15:13 -0400, Igor Korot wrote:
> On Sat, Jul 26, 2025, 2:14 PM Christophe Pettus <[email protected]> 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
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Get info about the index
2025-07-26 15:08 Re: Get info about the index Adrian Klaver <[email protected]>
2025-07-26 16:41 ` Re: Get info about the index Igor Korot <[email protected]>
2025-07-26 18:13 ` Re: Get info about the index Christophe Pettus <[email protected]>
2025-07-26 19:13 ` Re: Get info about the index Igor Korot <[email protected]>
@ 2025-07-26 19:50 ` Jon Zeppieri <[email protected]>
2025-07-28 13:19 ` Re: Get info about the index David Barbour <[email protected]>
1 sibling, 1 reply; 11+ messages in thread
From: Jon Zeppieri @ 2025-07-26 19:50 UTC (permalink / raw)
To: Igor Korot <[email protected]>; +Cc: Christophe Pettus <[email protected]>; Adrian Klaver <[email protected]>; pgsql-generallists.postgresql.org <[email protected]>
On Sat, Jul 26, 2025 at 3:13 PM Igor Korot <[email protected]> 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');
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Get info about the index
2025-07-26 15:08 Re: Get info about the index Adrian Klaver <[email protected]>
2025-07-26 16:41 ` Re: Get info about the index Igor Korot <[email protected]>
2025-07-26 18:13 ` Re: Get info about the index Christophe Pettus <[email protected]>
2025-07-26 19:13 ` Re: Get info about the index Igor Korot <[email protected]>
2025-07-26 19:50 ` Re: Get info about the index Jon Zeppieri <[email protected]>
@ 2025-07-28 13:19 ` David Barbour <[email protected]>
2025-07-28 15:13 ` Re: Get info about the index Laurenz Albe <[email protected]>
0 siblings, 1 reply; 11+ messages in thread
From: David Barbour @ 2025-07-28 13:19 UTC (permalink / raw)
To: Jon Zeppieri <[email protected]>; +Cc: Igor Korot <[email protected]>; Christophe Pettus <[email protected]>; Adrian Klaver <[email protected]>; pgsql-generallists.postgresql.org <[email protected]>
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 <[email protected]> wrote:
> On Sat, Jul 26, 2025 at 3:13 PM Igor Korot <[email protected]> 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');
>
>
>
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Get info about the index
2025-07-26 15:08 Re: Get info about the index Adrian Klaver <[email protected]>
2025-07-26 16:41 ` Re: Get info about the index Igor Korot <[email protected]>
2025-07-26 18:13 ` Re: Get info about the index Christophe Pettus <[email protected]>
2025-07-26 19:13 ` Re: Get info about the index Igor Korot <[email protected]>
2025-07-26 19:50 ` Re: Get info about the index Jon Zeppieri <[email protected]>
2025-07-28 13:19 ` Re: Get info about the index David Barbour <[email protected]>
@ 2025-07-28 15:13 ` Laurenz Albe <[email protected]>
2025-07-29 11:46 ` Re: Get info about the index Igor Korot <[email protected]>
0 siblings, 1 reply; 11+ messages in thread
From: Laurenz Albe @ 2025-07-28 15:13 UTC (permalink / raw)
To: David Barbour <[email protected]>; Jon Zeppieri <[email protected]>; +Cc: Igor Korot <[email protected]>; Christophe Pettus <[email protected]>; Adrian Klaver <[email protected]>; pgsql-generallists.postgresql.org <[email protected]>
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
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Get info about the index
2025-07-26 15:08 Re: Get info about the index Adrian Klaver <[email protected]>
2025-07-26 16:41 ` Re: Get info about the index Igor Korot <[email protected]>
2025-07-26 18:13 ` Re: Get info about the index Christophe Pettus <[email protected]>
2025-07-26 19:13 ` Re: Get info about the index Igor Korot <[email protected]>
2025-07-26 19:50 ` Re: Get info about the index Jon Zeppieri <[email protected]>
2025-07-28 13:19 ` Re: Get info about the index David Barbour <[email protected]>
2025-07-28 15:13 ` Re: Get info about the index Laurenz Albe <[email protected]>
@ 2025-07-29 11:46 ` Igor Korot <[email protected]>
2025-07-29 12:07 ` Re: Get info about the index Laurenz Albe <[email protected]>
0 siblings, 1 reply; 11+ messages in thread
From: Igor Korot @ 2025-07-29 11:46 UTC (permalink / raw)
To: Laurenz Albe <[email protected]>; +Cc: David Barbour <[email protected]>; Jon Zeppieri <[email protected]>; Christophe Pettus <[email protected]>; Adrian Klaver <[email protected]>; pgsql-generallists.postgresql.org <[email protected]>
Hi, guys,
On Mon, Jul 28, 2025 at 10:13 AM Laurenz Albe <[email protected]> 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&safe=active&sca_esv=3201...
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.
>
> Yours,
> Laurenz Albe
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Get info about the index
2025-07-26 15:08 Re: Get info about the index Adrian Klaver <[email protected]>
2025-07-26 16:41 ` Re: Get info about the index Igor Korot <[email protected]>
2025-07-26 18:13 ` Re: Get info about the index Christophe Pettus <[email protected]>
2025-07-26 19:13 ` Re: Get info about the index Igor Korot <[email protected]>
2025-07-26 19:50 ` Re: Get info about the index Jon Zeppieri <[email protected]>
2025-07-28 13:19 ` Re: Get info about the index David Barbour <[email protected]>
2025-07-28 15:13 ` Re: Get info about the index Laurenz Albe <[email protected]>
2025-07-29 11:46 ` Re: Get info about the index Igor Korot <[email protected]>
@ 2025-07-29 12:07 ` Laurenz Albe <[email protected]>
2025-07-29 12:18 ` Re: Get info about the index Igor Korot <[email protected]>
0 siblings, 1 reply; 11+ messages in thread
From: Laurenz Albe @ 2025-07-29 12:07 UTC (permalink / raw)
To: Igor Korot <[email protected]>; +Cc: David Barbour <[email protected]>; Jon Zeppieri <[email protected]>; Christophe Pettus <[email protected]>; Adrian Klaver <[email protected]>; pgsql-generallists.postgresql.org <[email protected]>
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
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Get info about the index
2025-07-26 15:08 Re: Get info about the index Adrian Klaver <[email protected]>
2025-07-26 16:41 ` Re: Get info about the index Igor Korot <[email protected]>
2025-07-26 18:13 ` Re: Get info about the index Christophe Pettus <[email protected]>
2025-07-26 19:13 ` Re: Get info about the index Igor Korot <[email protected]>
2025-07-26 19:50 ` Re: Get info about the index Jon Zeppieri <[email protected]>
2025-07-28 13:19 ` Re: Get info about the index David Barbour <[email protected]>
2025-07-28 15:13 ` Re: Get info about the index Laurenz Albe <[email protected]>
2025-07-29 11:46 ` Re: Get info about the index Igor Korot <[email protected]>
2025-07-29 12:07 ` Re: Get info about the index Laurenz Albe <[email protected]>
@ 2025-07-29 12:18 ` Igor Korot <[email protected]>
0 siblings, 0 replies; 11+ messages in thread
From: Igor Korot @ 2025-07-29 12:18 UTC (permalink / raw)
To: Laurenz Albe <[email protected]>; +Cc: David Barbour <[email protected]>; Jon Zeppieri <[email protected]>; Christophe Pettus <[email protected]>; Adrian Klaver <[email protected]>; pgsql-generallists.postgresql.org <[email protected]>
On Tue, Jul 29, 2025 at 7:07 AM Laurenz Albe <[email protected]> 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.
^ permalink raw reply [nested|flat] 11+ messages in thread
end of thread, other threads:[~2025-07-29 12:18 UTC | newest]
Thread overview: 11+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2025-07-26 15:08 Re: Get info about the index Adrian Klaver <[email protected]>
2025-07-26 16:41 ` Igor Korot <[email protected]>
2025-07-26 18:13 ` Christophe Pettus <[email protected]>
2025-07-26 19:13 ` Igor Korot <[email protected]>
2025-07-26 19:50 ` Laurenz Albe <[email protected]>
2025-07-26 19:50 ` Jon Zeppieri <[email protected]>
2025-07-28 13:19 ` David Barbour <[email protected]>
2025-07-28 15:13 ` Laurenz Albe <[email protected]>
2025-07-29 11:46 ` Igor Korot <[email protected]>
2025-07-29 12:07 ` Laurenz Albe <[email protected]>
2025-07-29 12:18 ` Igor Korot <[email protected]>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox