Re: Show method of index

On Sun, May 10, 2009 at 3:59 AM, Alvaro Herrera
<alvherre(at)commandprompt(dot)com> wrote:
> Yeah.  (I note that the expressions are already shown as footers when
> you display the table instead of the index.  It seems like the \d code
> for indexes did not get updated when that new code was added.)

Made some changes to the patch to show expressions. Would appreciate
any comments as I am still fairly new to the pg codebase.

---
postgres=# CREATE TABLE foo(a bigserial, b text, PRIMARY KEY (a,b));
NOTICE: CREATE TABLE will create implicit sequence "foo_a_seq" for
serial column "foo.a"
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index
"foo_pkey" for table "foo"
CREATE TABLE
postgres=# CREATE INDEX idx_foo_hash ON foo USING hash(a);
CREATE INDEX
postgres=# CREATE INDEX idx_foo_bt_func ON foo USING btree(md5(a||b));
CREATE INDEX
postgres=# CREATE INDEX idx_foo_hash_func ON foo USING hash(md5(b||a));
CREATE INDEX
postgres=# \div
********* QUERY **********
SELECT n.nspname as "Schema",
c.relname as "Name",
CASE c.relkind WHEN 'r' THEN 'table' WHEN 'v' THEN 'view' WHEN 'i'
THEN 'index' WHEN 'S' THEN 'sequence' WHEN 's' THEN 'special' END as
"Type",
pg_catalog.pg_get_userbyid(c.relowner) as "Owner",
c2.relname as "Table",
am.amname as "Method",
COALESCE(pg_get_expr(i.indexprs,i.indrelid),pg_get_indexdef(i.indexrelid,-999,TRUE))
as "Expression"
FROM pg_catalog.pg_class c
LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
LEFT JOIN pg_catalog.pg_index i ON i.indexrelid = c.oid
LEFT JOIN pg_catalog.pg_class c2 ON i.indrelid = c2.oid
LEFT JOIN pg_catalog.pg_am am ON am.oid = c.relam
WHERE c.relkind IN ('v','i','')
AND n.nspname <> 'pg_catalog'
AND n.nspname <> 'information_schema'
AND n.nspname !~ '^pg_toast'
AND pg_catalog.pg_table_is_visible(c.oid)
ORDER BY 1,2;
**************************

List of relations
Schema | Name | Type | Owner | Table | Method | Expression
--------+-------------------+-------+-------+-------+--------+---------------
public | foo_pkey | index | rubik | foo | btree | a, b
public | idx_foo_bt_func | index | rubik | foo | btree | md5((a || b))
public | idx_foo_hash | index | rubik | foo | hash | a
public | idx_foo_hash_func | index | rubik | foo | hash | md5((b || a))
(4 rows)

postgres=# \di idx_foo_hash_func
********* QUERY **********
SELECT n.nspname as "Schema",
c.relname as "Name",
CASE c.relkind WHEN 'r' THEN 'table' WHEN 'v' THEN 'view' WHEN 'i'
THEN 'index' WHEN 'S' THEN 'sequence' WHEN 's' THEN 'special' END as
"Type",
pg_catalog.pg_get_userbyid(c.relowner) as "Owner",
c2.relname as "Table",
am.amname as "Method",
COALESCE(pg_get_expr(i.indexprs,i.indrelid),pg_get_indexdef(i.indexrelid,-999,TRUE))
as "Expression"
FROM pg_catalog.pg_class c
LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
LEFT JOIN pg_catalog.pg_index i ON i.indexrelid = c.oid
LEFT JOIN pg_catalog.pg_class c2 ON i.indrelid = c2.oid
LEFT JOIN pg_catalog.pg_am am ON am.oid = c.relam
WHERE c.relkind IN ('i','s','')
AND n.nspname !~ '^pg_toast'
AND c.relname ~ '^(idx_foo_hash_func)$'
AND pg_catalog.pg_table_is_visible(c.oid)
ORDER BY 1,2;
**************************

List of relations
Schema | Name | Type | Owner | Table | Method | Expression
--------+-------------------+-------+-------+-------+--------+---------------
public | idx_foo_hash_func | index | rubik | foo | hash | md5((b || a))
(1 row)

postgres=#

---

Regards,
Khee Chin.