Thread: Enhanced index details using \d in psql
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
I've revised my patch to use the pg_get_indexdef function, as
suggested by Tom Lane. When doing a \d in psql, it shows not
only the name of the index, but which columns it uses,
what type of index it is (btree, hash, etc.), whether it
is unique or a primary key, and (thanks to the pg_get_indexdef
magic) information about functional and partial indexes.
I also made the ORDER BY stick the primary keys and unique
indexes at the top of the list, then alphabetasize the rest,
which I think is a good trade off for having ruined the
previous "separation" of the 3 types of indexes.
Here is some sample output:
CREATE TABLE PIZZA (
slicename VARCHAR(10) PRIMARY KEY,
crust VARCHAR(12),
topping INTEGER
);
CREATE INDEX banana ON pizza (crust);
CREATE UNIQUE INDEX watermelon ON pizza (topping);
CREATE INDEX apple ON pizza USING hash (topping) WHERE (topping > 99);
CREATE INDEX peach ON pizza (lower(crust));
bakery=# \d pizza
Table "pizza"
Column | Type | Modifiers
- -----------+-----------------------+-----------
slicename | character varying(10) | not null
crust | character varying(12) |
topping | integer |
Indexes: pizza_pkey primary key btree (slicename),
watermelon unique btree (topping),
apple hash (topping) WHERE (topping > 99),
banana btree (crust),
peach btree (lower(crust))
Greg Sabino Mullane
greg@turnstep.com
PGP Key: 0x14964AC8 200111081007
-----BEGIN PGP SIGNATURE-----
Comment: http://www.turnstep.com/pgp.html
iQA/AwUBO+qfuLybkGcUlkrIEQKFNgCg0ycYPt5DMvXI8EH/CH2mc8ZOJYwAoK1x
RDPvYrxuU55Sp1UIYgTWCRdD
=VBAa
-----END PGP SIGNATURE-----
*** ./src/bin/psql/describe.c.orig Mon Nov 5 15:43:13 2001
--- ./src/bin/psql/describe.c Thu Nov 8 09:22:49 2001
***************
*** 654,660 ****
PGresult *result;
sprintf(buf, "SELECT i.indisunique, i.indisprimary, a.amname,\n"
! " pg_get_expr(i.indpred, i.indrelid) as indpred\n"
"FROM pg_index i, pg_class c, pg_am a\n"
"WHERE i.indexrelid = c.oid AND c.relname = '%s' AND c.relam = a.oid",
name);
--- 654,660 ----
PGresult *result;
sprintf(buf, "SELECT i.indisunique, i.indisprimary, a.amname,\n"
! "i.indpred\n"
"FROM pg_index i, pg_class c, pg_am a\n"
"WHERE i.indexrelid = c.oid AND c.relname = '%s' AND c.relam = a.oid",
name);
***************
*** 707,718 ****
PGresult *result1 = NULL,
*result2 = NULL,
*result3 = NULL,
! *result4 = NULL,
! *result5 = NULL,
! *result6 = NULL;
int index_count = 0,
- primary_count = 0,
- unique_count = 0,
constr_count = 0,
rule_count = 0,
trigger_count = 0;
--- 707,714 ----
PGresult *result1 = NULL,
*result2 = NULL,
*result3 = NULL,
! *result4 = NULL;
int index_count = 0,
constr_count = 0,
rule_count = 0,
trigger_count = 0;
***************
*** 721,730 ****
/* count indexes */
if (!error && tableinfo.hasindex)
{
! sprintf(buf, "SELECT c2.relname\n"
"FROM pg_class c, pg_class c2, pg_index i\n"
"WHERE c.relname = '%s' AND c.oid = i.indrelid AND i.indexrelid = c2.oid\n"
! "AND NOT i.indisunique ORDER BY c2.relname",
name);
result1 = PSQLexec(buf);
if (!result1)
--- 717,726 ----
/* count indexes */
if (!error && tableinfo.hasindex)
{
! sprintf(buf, "SELECT c2.relname, pg_get_indexdef(i.indexrelid), i.indisprimary, i.indisunique\n"
"FROM pg_class c, pg_class c2, pg_index i\n"
"WHERE c.relname = '%s' AND c.oid = i.indrelid AND i.indexrelid = c2.oid\n"
! "ORDER BY i.indisprimary DESC, i.indisunique DESC, c2.relname",
name);
result1 = PSQLexec(buf);
if (!result1)
***************
*** 733,768 ****
index_count = PQntuples(result1);
}
- /* count primary keys */
- if (!error && tableinfo.hasindex)
- {
- sprintf(buf, "SELECT c2.relname\n"
- "FROM pg_class c, pg_class c2, pg_index i\n"
- "WHERE c.relname = '%s' AND c.oid = i.indrelid AND i.indexrelid = c2.oid\n"
- "AND i.indisprimary AND i.indisunique ORDER BY c2.relname",
- name);
- result5 = PSQLexec(buf);
- if (!result5)
- error = true;
- else
- primary_count = PQntuples(result5);
- }
-
- /* count unique constraints */
- if (!error && tableinfo.hasindex)
- {
- sprintf(buf, "SELECT c2.relname\n"
- "FROM pg_class c, pg_class c2, pg_index i\n"
- "WHERE c.relname = '%s' AND c.oid = i.indrelid AND i.indexrelid = c2.oid\n"
- "AND NOT i.indisprimary AND i.indisunique ORDER BY c2.relname",
- name);
- result6 = PSQLexec(buf);
- if (!result6)
- error = true;
- else
- unique_count = PQntuples(result6);
- }
-
/* count table (and column) constraints */
if (!error && tableinfo.checks)
{
--- 729,734 ----
***************
*** 807,860 ****
trigger_count = PQntuples(result4);
}
! footers = xmalloc((index_count + primary_count + unique_count +
! constr_count + rule_count + trigger_count + 1)
* sizeof(*footers));
/* print indexes */
for (i = 0; i < index_count; i++)
{
char *s = _("Indexes");
!
if (i == 0)
snprintf(buf, sizeof(buf), "%s: %s", s, PQgetvalue(result1, i, 0));
else
snprintf(buf, sizeof(buf), "%*s %s", (int) strlen(s), "", PQgetvalue(result1, i, 0));
! if (i < index_count - 1)
! strcat(buf, ",");
!
! footers[count_footers++] = xstrdup(buf);
! }
! /* print primary keys */
! for (i = 0; i < primary_count; i++)
! {
! char *s = _("Primary key");
! if (i == 0)
! snprintf(buf, sizeof(buf), "%s: %s", s, PQgetvalue(result5, i, 0));
! else
! snprintf(buf, sizeof(buf), "%*s %s", (int) strlen(s), "", PQgetvalue(result5, i, 0));
! if (i < primary_count - 1)
strcat(buf, ",");
footers[count_footers++] = xstrdup(buf);
}
- /* print unique constraints */
- for (i = 0; i < unique_count; i++)
- {
- char *s = _("Unique keys");
-
- if (i == 0)
- snprintf(buf, sizeof(buf), "%s: %s", s, PQgetvalue(result6, i, 0));
- else
- snprintf(buf, sizeof(buf), "%*s %s", (int) strlen(s), "", PQgetvalue(result6, i, 0));
- if (i < unique_count - 1)
- strcat(buf, ",");
-
- footers[count_footers++] = xstrdup(buf);
- }
/* print constraints */
for (i = 0; i < constr_count; i++)
--- 773,809 ----
trigger_count = PQntuples(result4);
}
! footers = xmalloc((index_count + constr_count + rule_count + trigger_count + 1)
* sizeof(*footers));
/* print indexes */
for (i = 0; i < index_count; i++)
{
char *s = _("Indexes");
!
if (i == 0)
snprintf(buf, sizeof(buf), "%s: %s", s, PQgetvalue(result1, i, 0));
else
snprintf(buf, sizeof(buf), "%*s %s", (int) strlen(s), "", PQgetvalue(result1, i, 0));
! /* Label primary key or unique (but not both) */
! if (strcmp(PQgetvalue(result1,i,2),"t") == 0) {
! strcat(buf, _(" primary key"));
! }
! else if (strcmp(PQgetvalue(result1,i,3),"t") == 0) {
! strcat(buf, _(" unique"));
! }
! /* Everything after "USING" is echoed verbatim */
! if (strstr(PQgetvalue(result1,i,1),"USING ")) {
! strcat(buf, strstr(strstr(PQgetvalue(result1,i,1),"USING")," "));
! }
! if (i < index_count - 1)
strcat(buf, ",");
footers[count_footers++] = xstrdup(buf);
}
/* print constraints */
for (i = 0; i < constr_count; i++)
***************
*** 907,914 ****
PQclear(result2);
PQclear(result3);
PQclear(result4);
- PQclear(result5);
- PQclear(result6);
}
if (!error)
--- 856,861 ----
I think that's a lot more useful than the previous changes I submitted that
just broke them into index type.
I'm in favour of it.
Chris
-----Original Message-----
From: pgsql-patches-owner@postgresql.org
[mailto:pgsql-patches-owner@postgresql.org]On Behalf Of Greg Sabino
Mullane
Sent: Thursday, 8 November 2001 11:08 PM
To: pgsql-patches@postgresql.org
Subject: [PATCHES] Enhanced index details using \d in psql
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
I've revised my patch to use the pg_get_indexdef function, as
suggested by Tom Lane. When doing a \d in psql, it shows not
only the name of the index, but which columns it uses,
what type of index it is (btree, hash, etc.), whether it
is unique or a primary key, and (thanks to the pg_get_indexdef
magic) information about functional and partial indexes.
I also made the ORDER BY stick the primary keys and unique
indexes at the top of the list, then alphabetasize the rest,
which I think is a good trade off for having ruined the
previous "separation" of the 3 types of indexes.
Here is some sample output:
CREATE TABLE PIZZA (
slicename VARCHAR(10) PRIMARY KEY,
crust VARCHAR(12),
topping INTEGER
);
CREATE INDEX banana ON pizza (crust);
CREATE UNIQUE INDEX watermelon ON pizza (topping);
CREATE INDEX apple ON pizza USING hash (topping) WHERE (topping > 99);
CREATE INDEX peach ON pizza (lower(crust));
bakery=# \d pizza
Table "pizza"
Column | Type | Modifiers
- -----------+-----------------------+-----------
slicename | character varying(10) | not null
crust | character varying(12) |
topping | integer |
Indexes: pizza_pkey primary key btree (slicename),
watermelon unique btree (topping),
apple hash (topping) WHERE (topping > 99),
banana btree (crust),
peach btree (lower(crust))
Greg Sabino Mullane
greg@turnstep.com
PGP Key: 0x14964AC8 200111081007
-----BEGIN PGP SIGNATURE-----
Comment: http://www.turnstep.com/pgp.html
iQA/AwUBO+qfuLybkGcUlkrIEQKFNgCg0ycYPt5DMvXI8EH/CH2mc8ZOJYwAoK1x
RDPvYrxuU55Sp1UIYgTWCRdD
=VBAa
-----END PGP SIGNATURE-----
"Greg Sabino Mullane" <greg@turnstep.com> writes:
> Here is some sample output:
> bakery=# \d pizza
> Table "pizza"
> Column | Type | Modifiers
> -----------+-----------------------+-----------
> slicename | character varying(10) | not null
> crust | character varying(12) |
> topping | integer |
> Indexes: pizza_pkey primary key btree (slicename),
> watermelon unique btree (topping),
> apple hash (topping) WHERE (topping > 99),
> banana btree (crust),
> peach btree (lower(crust))
This looks great (not the code necessarily, I haven't read it yet,
but the output looks plenty cool).
However, since I was just an hour ago pontificating to the JDBC guys
about how we're in beta and we've got to resist the urge to add new
features, I must regretfully opine that I think it's too late to put
it in 7.2. It should be at the front of the line for 7.3 though.
A couple of things you might want to work on while waiting for the
7.3 development cycle to start:
1. \di probably should be extended as well. I haven't got any fixed
opinions about what its output should look like, but surely \di index
should convey at least as much info as \d parent provides. Right now
I don't believe it knows much about functional or partial indexes.
2. Documentation. There are probably examples of the use of \d and
\di scattered through the SGML docs. They need to be found and
updated.
Looks great so far!
regards, tom lane
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
> It should be at the front of the line for 7.3 though.
No problem, it's already aimed at 7.3 in my mind. We need to
get 7.2 out the door already! :)
> 1. \di probably should be extended as well. I haven't got any fixed
> opinions about what its output should look like, but surely \di index
> should convey at least as much info as \d parent provides. Right now
> I don't believe it knows much about functional or partial indexes.
I'm working on these. (mostly done actually). I'm also trying to
add support for viewing temporary relations (e.g. tables and
indexes) and promise to put in some doc patches when evreything
is done.
I'm also adding a "table" display to the index viewing, for the
benefit of those people who don't give their indexes (I still
want to type "indices" sometime....) good, meaningful names
(er...like I was guilty of in my previous example).
Sample output:
bakery=# \di carrot
Index "carrot"
Column | Type
- ----------+-----------------------
dressing | character varying(20)
btree for table "salad"
bakery=# \di
List of relations
Name | Type | Owner | Table
- ------------+-------+----------+-------
apple | index | postgres | pizza
banana | index | postgres | pizza
carrot | index | postgres | salad
peach | index | postgres | pizza
pizza_pkey | index | postgres | pizza
watermelon | index | postgres | pizza
By the way, that "Index predicate" at the end of \di output
is pretty ugly IMO. What is the purpose of it? I'm not going to
document *that* one. ;)
bakery=# \di apple
Index "apple"
Column | Type
- ---------+---------
topping | integer
hash for table "pizza" WHERE (topping > 99)
Index predicate: ({ EXPR :typeOid 16 :opType op :oper { OPER :opno 521 :opid 14
7 :opresulttype 16 } :args ({ VAR :varno 1 :varattno 3 :vartype 23 :vartypmod -1
:varlevelsup 0 :varnoold 1 :varoattno 3} { CONST :consttype 23 :constlen 4 :co
nstbyval true :constisnull false :constvalue 4 [ 99 0 0 0 ] })})
Greg Sabino Mullane
greg@turnstep.com
PGP Key: 0x14964AC8 200111090946
-----BEGIN PGP SIGNATURE-----
Comment: http://www.turnstep.com/pgp.html
iQA/AwUBO+vtMLybkGcUlkrIEQKDxgCgkRvTH9H2afTML5R3k3zTrJVhBh4AniHH
N4Nt+7QOofoygRSKeeRh/2tt
=uSmD
-----END PGP SIGNATURE-----
"Greg Sabino Mullane" <greg@turnstep.com> writes:
> By the way, that "Index predicate" at the end of \di output
> is pretty ugly IMO. What is the purpose of it?
It looks like a dump of the internal form of the partial-index
predicate. I don't know where it's coming from, but it's not
in current CVS sources. Using your example I get just
regression=# \d apple
Index "apple"
Column | Type
---------+---------
topping | integer
hash
Index predicate: (topping > 99)
regression=# \di apple
List of relations
Name | Type | Owner
-------+-------+----------
apple | index | postgres
(1 row)
regression=#
which reminds me that I've been misstating in this conversation;
the query that I was thinking about was "\d index" not "\di index".
\di is for a bulk listing of multiple indexes, one per line, so there's
a limit to how much info you can supply per index. I doubt predicates
can be made to fit. It might be worth adding a few columns though,
like name of parent table.
regards, tom lane