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;

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

From what I have seen it is rather complicated in postgres.

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;

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.nspn
ame) || '.' || quote_ident(i.relname))) AS
table_size,

pg_size_pretty(pg_relation_size(quote_ident(n.nspn
ame) || '.' || 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.
<http://us.i1.yimg.com/us.yimg.com/i/mesg/tsmileys
2/01.gif>

www.youtube.com/unusedhero

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

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

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

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

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

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