Menu
If you use psql to access the PostgreSQL database, you can use the \d command to view the index information for a table.
To see indexes for all tables within a specific schema you can use the STATISTICS table from INFORMATION_SCHEMA: SELECT DISTINCT TABLE_NAME, INDEX_NAME FROM INFORMATION_SCHEMA. STATISTICS WHERE TABLE_SCHEMA = 'your_schema'; Removing the where clause will show you all indexes in all schemas.
All indexes in PostgreSQL are secondary indexes, meaning that each index is stored separately from the table's main data area (which is called the table's heap in PostgreSQL terminology). This means that in an ordinary index scan, each row retrieval requires fetching data from both the index and the heap.
Use the fields in the General tab to identify the index: Use the Name field to add a descriptive name for the index. The name will be displayed in the pgAdmin tree control. Use the drop-down listbox next to Tablespace to select the tablespace in which the index will reside.
The view pg_indexes provides access to useful information about each index in the database, eg.
select *
from pg_indexes
where tablename not like 'pg%';
if you're in psql, then:
\d tablename
show Indexes, Foreign Keys and references...
You can use this query:
select tablename,indexname,tablespace,indexdef from pg_indexes where tablename = 'your_table_name';
where has tablename is a field in pg_indexes ,you an get an accurate indices by matching user defined table at 'your_table_name' at WHERE clause . This will give you the desired details.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With