public inbox for [email protected]help / color / mirror / Atom feed
Re: 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]> 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 16:41 Igor Korot <[email protected]> parent: Adrian Klaver <[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 18:13 Christophe Pettus <[email protected]> parent: 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 19:13 Igor Korot <[email protected]> parent: Christophe Pettus <[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 19:50 Laurenz Albe <[email protected]> parent: Igor Korot <[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 19:50 Jon Zeppieri <[email protected]> parent: Igor Korot <[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-28 13:19 David Barbour <[email protected]> parent: Jon Zeppieri <[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-28 15:13 Laurenz Albe <[email protected]> parent: David Barbour <[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-29 11:46 Igor Korot <[email protected]> parent: 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-29 12:07 Laurenz Albe <[email protected]> parent: 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-29 12:18 Igor Korot <[email protected]> parent: Laurenz Albe <[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