Index tab doesn't show full details of the indexes

eelke commented

2018-05-01 12:04:23 +00:00

(Migrated from gitlab.com)

The reason it does not at the moment is that the information is hard to retrieve (or atleast convert to something presentable).

I have however found a query in the pgAdmin source that retrieves relevant info. It is executed per column (which I do not realy like as it will be relative slow to retrieve).

SELECT i.indoption[0] AS options,
  pg_get_indexdef(i.indexrelid, 1, true) AS coldef,
  op.oprname,
  CASE WHEN (o.opcdefault = FALSE) THEN o.opcname ELSE null END AS opcname,

    coll.collname, nspc.nspname as collnspname
FROM pg_index i
	JOIN pg_attribute a ON (a.attrelid = i.indexrelid AND attnum = 1)
	LEFT OUTER JOIN pg_opclass o ON (o.oid = i.indclass[0])
	LEFT OUTER JOIN pg_constraint c ON (c.conindid = i.indexrelid) 
	LEFT OUTER JOIN pg_operator op ON (op.oid = c.conexclop[1])

	LEFT OUTER JOIN pg_collation coll ON a.attcollation=coll.oid
	LEFT OUTER JOIN pg_namespace nspc ON coll.collnamespace=nspc.oid
WHERE i.indexrelid = 16680061

I do need to verify the exclusion constraint joins as the index of c.conexclop is one higher then I
would expect. Would be nice if we could retrieve this data efficiently. Working with arrays however is hard.

The reason it does not at the moment is that the information is hard to retrieve (or atleast convert to something presentable). I have however found a query in the pgAdmin source that retrieves relevant info. It is executed per column (which I do not realy like as it will be relative slow to retrieve). ```sql SELECT i.indoption[0] AS options, pg_get_indexdef(i.indexrelid, 1, true) AS coldef, op.oprname, CASE WHEN (o.opcdefault = FALSE) THEN o.opcname ELSE null END AS opcname, coll.collname, nspc.nspname as collnspname FROM pg_index i JOIN pg_attribute a ON (a.attrelid = i.indexrelid AND attnum = 1) LEFT OUTER JOIN pg_opclass o ON (o.oid = i.indclass[0]) LEFT OUTER JOIN pg_constraint c ON (c.conindid = i.indexrelid) LEFT OUTER JOIN pg_operator op ON (op.oid = c.conexclop[1]) LEFT OUTER JOIN pg_collation coll ON a.attcollation=coll.oid LEFT OUTER JOIN pg_namespace nspc ON coll.collnamespace=nspc.oid WHERE i.indexrelid = 16680061 ``` I do need to verify the exclusion constraint joins as the index of c.conexclop is one higher then I would expect. Would be nice if we could retrieve this data efficiently. Working with arrays however is hard.

eelke commented

2018-08-05 07:36:06 +00:00

(Migrated from gitlab.com)

changed the description

eelke commented

2018-08-05 07:52:17 +00:00

(Migrated from gitlab.com)

Version of query that unpacks the array's

WITH i AS (
	SELECT indexrelid, idx, i.indoption[idx-1] AS options, 
	  pg_get_indexdef(i.indexrelid, idx, true) AS coldef,
	  i.indclass[idx-1] AS indclass
	FROM pg_index i
		, LATERAL (SELECT generate_series(1, i.indnatts)) AS arridx(idx)
)
SELECT indexrelid, idx, i.options, i.coldef, op.oprname, 
	CASE WHEN (o.opcdefault = FALSE) THEN o.opcname ELSE null END AS opcname,
	    coll.collname, nspc.nspname as collnspname
FROM i
	JOIN pg_attribute a ON (a.attrelid = i.indexrelid AND attnum = i.idx)
	LEFT OUTER JOIN pg_opclass o ON (o.oid = i.indclass)
	LEFT OUTER JOIN pg_constraint c ON (c.conindid = i.indexrelid) 
	LEFT OUTER JOIN pg_operator op ON (op.oid = c.conexclop[i.idx])
	LEFT OUTER JOIN pg_collation coll ON a.attcollation=coll.oid
	LEFT OUTER JOIN pg_namespace nspc ON coll.collnamespace=nspc.oid
ORDER BY indexrelid, idx

Postgres can cough this up pretty fast this way. Still need to test of the index of c.conexclop[i.idx] needs a -1 or not.

Version of query that unpacks the array's ```sql WITH i AS ( SELECT indexrelid, idx, i.indoption[idx-1] AS options, pg_get_indexdef(i.indexrelid, idx, true) AS coldef, i.indclass[idx-1] AS indclass FROM pg_index i , LATERAL (SELECT generate_series(1, i.indnatts)) AS arridx(idx) ) SELECT indexrelid, idx, i.options, i.coldef, op.oprname, CASE WHEN (o.opcdefault = FALSE) THEN o.opcname ELSE null END AS opcname, coll.collname, nspc.nspname as collnspname FROM i JOIN pg_attribute a ON (a.attrelid = i.indexrelid AND attnum = i.idx) LEFT OUTER JOIN pg_opclass o ON (o.oid = i.indclass) LEFT OUTER JOIN pg_constraint c ON (c.conindid = i.indexrelid) LEFT OUTER JOIN pg_operator op ON (op.oid = c.conexclop[i.idx]) LEFT OUTER JOIN pg_collation coll ON a.attcollation=coll.oid LEFT OUTER JOIN pg_namespace nspc ON coll.collnamespace=nspc.oid ORDER BY indexrelid, idx ``` Postgres can cough this up pretty fast this way. Still need to test of the index of `c.conexclop[i.idx]` needs a `-1` or not.

eelke commented

2018-08-05 07:52:26 +00:00

(Migrated from gitlab.com)

changed the description

eelke commented

2018-08-05 09:27:58 +00:00

(Migrated from gitlab.com)

closed via commit f1020ac56e

closed via commit f1020ac56ed27e92e21b66e11a222b1164f29c95

eelke commented

2018-08-05 09:58:19 +00:00

(Migrated from gitlab.com)