Postgres Pro
Downloads
Home   >   mailing lists

Thread: list of index

list of index

From
"FarjadFarid\(ChkNet\)"
Date:

Hi ,

 

On a Postgresql database i like to obtain (using an sql statement) the list of all user defined indexes and their details specially the column "order by" sort order. e.g. ASC or DESC. Any help would be much appreciated.  

 

 

Re: list of index

From
Szymon Guz
Date:
On 15 August 2014 16:23, FarjadFarid(ChkNet) <farjad.farid@checknetworks.com> wrote:

Hi ,

 

On a Postgresql database i like to obtain (using an sql statement) the list of all user defined indexes and their details specially the column "order by" sort order. e.g. ASC or DESC. Any help would be much appreciated.  

 

 


Hi,
take a look at: select * from pg_indexes;

Re: list of index

From
"FarjadFarid\(ChkNet\)"
Date:

 

Hi ,

 

I have done that. The important point is obtaining the “sort order” of each column in the index. Specially multi column index.

 

Are there no views?

How does the engine handle sort order of a multi column index?!!

 

Any suggestion would be very welcome.  Many Thanks.

 

 

From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-owner@postgresql.org] On Behalf Of Szymon Guz
Sent: 15 August 2014 15:32
To: farjad.farid@checknetworks.com
Cc: PostgreSQL
Subject: Re: [GENERAL] list of index

 

On 15 August 2014 16:23, FarjadFarid(ChkNet) <farjad.farid@checknetworks.com> wrote:

Hi ,

 

On a Postgresql database i like to obtain (using an sql statement) the list of all user defined indexes and their details specially the column "order by" sort order. e.g. ASC or DESC. Any help would be much appreciated.  

 

 

 

Hi,
take a look at: select * from pg_indexes;

Re: list of index

From
"FarjadFarid\(ChkNet\)"
Date:

 

Thanks Melvin, That worked for me. Great.

 

 

 

From: Melvin Davidson [mailto:melvin6925@yahoo.com]
Sent: 15 August 2014 15:46
To: farjad.farid@checknetworks.com; pgsql-general@postgresql.org
Subject: Re: [GENERAL] list of index

 

 

>On a Postgresql database i like to obtain (using an sql

>statement) the list of all user defined indexes and their

>details specially the column "order by" sort order. e.g.

>ASC or DESC. Any help would be much appreciated. 

 

either of the following queries should help:

 

SELECT pg_get_indexdef(idx.indexrelid) || ';'
  FROM pg_stat_all_indexes i
  JOIN pg_class c ON (c.oid = i.relid)
  JOIN pg_namespace n ON (n.oid = c.relnamespace)
  JOIN pg_index idx ON (idx.indexrelid =  i.indexrelid )
 WHERE NOT idx.indisprimary
   AND NOT idx.indisunique
   AND i.relname NOT LIKE 'pg_%'
   AND i.idx_scan = 0
   ORDER BY n.nspname,
          i.relname;


SELECT n.nspname as schema,
       i.relname as table,
       i.indexrelname as index,
       i.idx_scan,
       i.idx_tup_read,
       i.idx_tup_fetch,
       pg_size_pretty(pg_relation_size(quote_ident(n.nspname) || '.' || quote_ident(i.relname))) AS table_size,
       pg_size_pretty(pg_relation_size(quote_ident(n.nspname) || '.' || quote_ident(i.indexrelname))) AS index_size,
       pg_get_indexdef(idx.indexrelid) as idx_definition
  FROM pg_stat_all_indexes i
  JOIN pg_class c ON (c.oid = i.relid)
  JOIN pg_namespace n ON (n.oid = c.relnamespace)
  JOIN pg_index idx ON (idx.indexrelid =  i.indexrelid )
 WHERE n.nspname NOT LIKE 'pg_%'
 ORDER BY 1, 2, 3;

 

Melvin Davidson
    Cell 720-320-0155

I reserve the right to fantasize.  Whether or not you
wish to share my fantasy is entirely up to you.

www.youtube.com/unusedhero

Folk Alley - All Folk - 24 Hours a day
www.folkalley.com

Re: list of index

From
Michael Paquier
Date:
On Fri, Aug 15, 2014 at 11:53 PM, FarjadFarid(ChkNet)
<farjad.farid@checknetworks.com> wrote:
> I have done that. The important point is obtaining the "sort order" of each
> column in the index. Specially multi column index.
> Are there no views?
FWIW, I always find good source of inspiration the queries used by
psql to fetch information from system views. You can get a look at
them with psql -E.
Regards,
--
Michael

Re: list of index

From
"FarjadFarid\(ChkNet\)"
Date:
Thanks for the tip Michael.

I am still a newbie to postgresql. Have to set
aside more time to get to acquiented with its
engine's features.

Haven't used psql in any meaningful way and yet to
go through a proper development cycle debugging
functions etc.

Are there any tutorials for features of psql?
and debug session ?

Many thanks.

Farjad


-----Original Message-----
From: pgsql-general-owner@postgresql.org
[mailto:pgsql-general-owner@postgresql.org] On
Behalf Of Michael Paquier
Sent: 18 August 2014 02:58
To: farjad.farid@checknetworks.com
Cc: Szymon Guz; PostgreSQL
Subject: Re: [GENERAL] list of index

On Fri, Aug 15, 2014 at 11:53 PM,
FarjadFarid(ChkNet)
<farjad.farid@checknetworks.com> wrote:
> I have done that. The important point is
obtaining the "sort order" of
> each column in the index. Specially multi column
index.
> Are there no views?
FWIW, I always find good source of inspiration the
queries used by psql to fetch information from
system views. You can get a look at them with psql
-E.
Regards,
--
Michael


--
Sent via pgsql-general mailing list
(pgsql-general@postgresql.org) To make changes to
your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: list of index

From
Michael Paquier
Date:
On Mon, Aug 18, 2014 at 1:40 PM, FarjadFarid(ChkNet)
<farjad.farid@checknetworks.com> wrote:
> Are there any tutorials for features of psql?

I don't recall particularly one, but the documentation is worth
reading and well-maintained. Here for psql:
http://www.postgresql.org/docs/devel/static/app-psql.html

> and debug session ?
I think going through the docs is perhaps the way to go. There are
many ways to get information to debug an application, like server
logs, EXPLAIN, etc... I am sure that others will point out better
things than I though :)
Regards
--
Michael