From f4b99d9ca2a905e652cdb134c333825b6322fe5a Mon Sep 17 00:00:00 2001 From: Gurjeet Singh Date: Wed, 2 Jun 2010 02:18:40 +0530 Subject: [PATCH] dos2unix on all repo files, and added OR REPLACE clauses to view and function creation. --- Makefile | 78 +-- README.pgadviser | 4 +- resources/index_advisory.create.sql | 20 +- resources/sample_error_messages.sql | 160 +++--- resources/sample_error_messages.txt | 384 +++++++-------- resources/sample_psql_session.sql | 160 +++--- resources/sample_psql_session.txt | 542 ++++++++++----------- resources/select_index_advisory.create.sql | 26 +- resources/show_index_advisory.create.sql | 202 ++++---- 9 files changed, 789 insertions(+), 787 deletions(-) diff --git a/Makefile b/Makefile index c00c8b1..66b3121 100644 --- a/Makefile +++ b/Makefile @@ -1,39 +1,39 @@ -# -# PostgreSQL Adviser top level makefile -# - -PGFILEDESC = "PostgreSQL Index Advisor" - -ifdef USE_PGXS -PGXS := $(shell pg_config --pgxs) -include $(PGXS) -else -subdir = contrib/adviser -top_builddir = ../.. -include $(top_builddir)/src/Makefile.global -include $(top_srcdir)/contrib/contrib-global.mk -endif - - -all: - $(MAKE) -C index_adviser $@ - $(MAKE) -C pg_advise $@ - $(MAKE) -C resources $@ - @echo "PostgreSQL Index Advisor successfully made. Ready to install." - -install: - $(MAKE) -C index_adviser $@ - $(MAKE) -C pg_advise $@ - $(MAKE) -C resources $@ - @echo "PostgreSQL Index Advisor installed." - -uninstall: - $(MAKE) -C index_adviser $@ - $(MAKE) -C pg_advise $@ - $(MAKE) -C resources $@ - @echo "PostgreSQL Index Advisor uninstalled." - -clean: - $(MAKE) -C index_adviser $@ - $(MAKE) -C pg_advise $@ - $(MAKE) -C resources $@ +# +# PostgreSQL Adviser top level makefile +# + +PGFILEDESC = "PostgreSQL Index Advisor" + +ifdef USE_PGXS +PGXS := $(shell pg_config --pgxs) +include $(PGXS) +else +subdir = contrib/adviser +top_builddir = ../.. +include $(top_builddir)/src/Makefile.global +include $(top_srcdir)/contrib/contrib-global.mk +endif + + +all: + $(MAKE) -C index_adviser $@ + $(MAKE) -C pg_advise $@ + $(MAKE) -C resources $@ + @echo "PostgreSQL Index Advisor successfully made. Ready to install." + +install: + $(MAKE) -C index_adviser $@ + $(MAKE) -C pg_advise $@ + $(MAKE) -C resources $@ + @echo "PostgreSQL Index Advisor installed." + +uninstall: + $(MAKE) -C index_adviser $@ + $(MAKE) -C pg_advise $@ + $(MAKE) -C resources $@ + @echo "PostgreSQL Index Advisor uninstalled." + +clean: + $(MAKE) -C index_adviser $@ + $(MAKE) -C pg_advise $@ + $(MAKE) -C resources $@ diff --git a/README.pgadviser b/README.pgadviser index 8417d6f..6279e26 100644 --- a/README.pgadviser +++ b/README.pgadviser @@ -1,2 +1,2 @@ -As of now, this module contains only an index adviser. Please read index_adviser/README.index_advisery. - +As of now, this module contains only an index adviser. Please read index_adviser/README.index_advisery. + diff --git a/resources/index_advisory.create.sql b/resources/index_advisory.create.sql index 1317107..a9238e9 100644 --- a/resources/index_advisory.create.sql +++ b/resources/index_advisory.create.sql @@ -1,10 +1,10 @@ - -create table index_advisory( reloid oid, - attrs integer[], - profit real, - index_size integer, - backend_pid integer, - timestamp timestamptz); - -create index IA_reloid on index_advisory( reloid ); -create index IA_backend_pid on index_advisory( backend_pid ); + +create table index_advisory( reloid oid, + attrs integer[], + profit real, + index_size integer, + backend_pid integer, + timestamp timestamptz); + +create index IA_reloid on index_advisory( reloid ); +create index IA_backend_pid on index_advisory( backend_pid ); diff --git a/resources/sample_error_messages.sql b/resources/sample_error_messages.sql index 2ef008d..503cd10 100644 --- a/resources/sample_error_messages.sql +++ b/resources/sample_error_messages.sql @@ -1,80 +1,80 @@ - -\c postgres test - -drop table if exists advise_index; -drop view if exists advise_index; - -/* create the advise_index same as provided in the contrib module */; -create table advise_index( reloid oid, attrs integer[], profit real, - index_size integer, backend_pid integer, - timestamp timestamptz); - -/* set the client to see the log messages generated by the Adviser */; -set client_min_messages to log; - -/* As expected, the EXPLAIN will work */; -explain select * from t where a = 100; - -select * from advise_index; - -/* Now lets drop the advise_index and see what ERROR it throws */; -drop table if exists advise_index; -drop view if exists advise_index; - -explain select * from t where a = 100; - -/* create another object by the same name (in the same namespace) */; -create index advise_index on t1(a); - -/* advise_index does exist, but its not a table or view! */; -explain select * from t where a = 100; - -/* now create a table named advise_index, but with a different signature! */; -drop index advise_index; - -create table advise_index(a int); - -/* This ERROR comes from the executor, but we still see our DETAIL and HINT */; -explain select * from t where a = 100; - -/* create a table with same signature but different name */; -drop table if exists advise_index; -drop view if exists advise_index; - -drop table if exists advise_index_data cascade; - -create table advise_index_data( reloid oid, attrs integer[], profit real, - index_size integer, backend_pid integer, - timestamp timestamptz); - -/* and a view on that table */; -create view advise_index as select * from advise_index_data; - -/* now try to insert into the view, and notice the ERROR, DETAIL and HINT from executor */; -explain select * from t where a = 100; - -/* now create a RULE on the view that redirects the INSERTs into the table */; -create or replace rule advise_index_insert as -ON INSERT to advise_index -do instead -INSERT into advise_index_data values (new.reloid, new.attrs, new.profit, - new.index_size, new.backend_pid, - new.timestamp) ; - -/* and voila, (internal) INSERT into the view succeeds! */; -explain select * from t where a = 100; - -/* Now, lets try what happens under a read-only transaction */; -begin; - -set transaction_read_only=t; - -show transaction_read_only; - -explain select * from t where a = 100; - -end; - -select * from advise_index; - -select * from advise_index_data; + +\c postgres test + +drop table if exists advise_index; +drop view if exists advise_index; + +/* create the advise_index same as provided in the contrib module */; +create table advise_index( reloid oid, attrs integer[], profit real, + index_size integer, backend_pid integer, + timestamp timestamptz); + +/* set the client to see the log messages generated by the Adviser */; +set client_min_messages to log; + +/* As expected, the EXPLAIN will work */; +explain select * from t where a = 100; + +select * from advise_index; + +/* Now lets drop the advise_index and see what ERROR it throws */; +drop table if exists advise_index; +drop view if exists advise_index; + +explain select * from t where a = 100; + +/* create another object by the same name (in the same namespace) */; +create index advise_index on t1(a); + +/* advise_index does exist, but its not a table or view! */; +explain select * from t where a = 100; + +/* now create a table named advise_index, but with a different signature! */; +drop index advise_index; + +create table advise_index(a int); + +/* This ERROR comes from the executor, but we still see our DETAIL and HINT */; +explain select * from t where a = 100; + +/* create a table with same signature but different name */; +drop table if exists advise_index; +drop view if exists advise_index; + +drop table if exists advise_index_data cascade; + +create table advise_index_data( reloid oid, attrs integer[], profit real, + index_size integer, backend_pid integer, + timestamp timestamptz); + +/* and a view on that table */; +create view advise_index as select * from advise_index_data; + +/* now try to insert into the view, and notice the ERROR, DETAIL and HINT from executor */; +explain select * from t where a = 100; + +/* now create a RULE on the view that redirects the INSERTs into the table */; +create or replace rule advise_index_insert as +ON INSERT to advise_index +do instead +INSERT into advise_index_data values (new.reloid, new.attrs, new.profit, + new.index_size, new.backend_pid, + new.timestamp) ; + +/* and voila, (internal) INSERT into the view succeeds! */; +explain select * from t where a = 100; + +/* Now, lets try what happens under a read-only transaction */; +begin; + +set transaction_read_only=t; + +show transaction_read_only; + +explain select * from t where a = 100; + +end; + +select * from advise_index; + +select * from advise_index_data; diff --git a/resources/sample_error_messages.txt b/resources/sample_error_messages.txt index db6dc77..8a49dc9 100644 --- a/resources/sample_error_messages.txt +++ b/resources/sample_error_messages.txt @@ -1,192 +1,192 @@ - -postgres=> \c postgres test -You are now connected to database "postgres". -postgres=> -postgres=> drop table if exists advise_index; -NOTICE: table "advise_index" does not exist, skipping -DROP TABLE -postgres=> drop view if exists advise_index; -NOTICE: view "advise_index" does not exist, skipping -DROP VIEW -postgres=> -postgres=> /* create the advise_index same as provided in the contrib module */; -postgres=> create table advise_index( reloid oid, attrs integer[], profit real, -postgres(> index_size integer, backend_pid integer, -postgres(> timestamp timestamptz); -CREATE TABLE -postgres=> -postgres=> /* set the client to see the log messages generated by the Adviser */; -postgres=> set client_min_messages to log; -SET -postgres=> -postgres=> /* As expected, the EXPLAIN will work */; -postgres=> explain select * from t where a = 100; -LOG: Index Adviser: Plan using estimates for suggested indexes: - -Index Scan using idx_adv_0 on t (cost=0.00..8.28 rows=1 width=8) - Index Cond: (a = 100) - - QUERY PLAN ----------------------------------------------------- - Seq Scan on t (cost=0.00..1791.00 rows=1 width=8) - Filter: (a = 100) -(2 rows) - -postgres=> -postgres=> select * from advise_index; - reloid | attrs | profit | index_size | backend_pid | timestamp ---------+-------+---------+------------+-------------+------------------------------- - 16395 | {1} | 1782.72 | 2608 | 5256 | 2007-01-13 13:08:53.593+05:30 -(1 row) - -postgres=> -postgres=> /* Now lets drop the advise_index and see what ERROR it throws */; -postgres=> drop table if exists advise_index; -DROP TABLE -postgres=> drop view if exists advise_index; -NOTICE: view "advise_index" does not exist, skipping -DROP VIEW -postgres=> -postgres=> explain select * from t where a = 100; -LOG: Index Adviser: Plan using estimates for suggested indexes: - -Index Scan using idx_adv_0 on t (cost=0.00..8.28 rows=1 width=8) - Index Cond: (a = 100) - -ERROR: relation "advise_index" does not exist. -DETAIL: Index Adviser uses "advise_index" table to store it's advisory. You should have INSERT permissions on a table or an (INSERT-able) view named "advise_index". Also, make sure that you are not running the Index Adviser under a read-only transaction. -HINT: Please create the "advise_index" table using the script provided in pg_advise_index contrib module. -postgres=> -postgres=> /* create another object by the same name (in the same namespace) */; - -postgres=> create index advise_index on t1(a); -CREATE INDEX -postgres=> -postgres=> /* advise_index does exist, but its not a table or view! */; -postgres=> explain select * from t where a = 100; -LOG: Index Adviser: Plan using estimates for suggested indexes: - -Index Scan using idx_adv_0 on t (cost=0.00..8.28 rows=1 width=8) - Index Cond: (a = 100) - -ERROR: "advise_index" is not a table or view. -DETAIL: Index Adviser uses "advise_index" table to store it's advisory. You should have INSERT permissions on a table or an (INSERT-able) view named "advise_index". Also, make sure that you are not running the Index Adviser under a read-only transaction. -HINT: Please create the "advise_index" table using the script provided in pg_advise_index contrib module. -postgres=> -postgres=> /* now create a table named advise_index, but with a different signature! */; -postgres=> drop index advise_index; -DROP INDEX -postgres=> -postgres=> create table advise_index(a int); -CREATE TABLE -postgres=> -postgres=> /* This ERROR comes from the executor, but we still see our DETAIL and HINT */; -postgres=> explain select * from t where a = 100; -LOG: Index Adviser: Plan using estimates for suggested indexes: - -Index Scan using idx_adv_0 on t (cost=0.00..8.28 rows=1 width=8) - Index Cond: (a = 100) - -ERROR: INSERT has more expressions than target columns -DETAIL: Index Adviser uses "advise_index" table to store it's advisory. You should have INSERT permissions on a table or an (INSERT-able) view named "advise_index". Also, make sure that you are not running the Index Adviser under a read-only transaction. -HINT: Please create the "advise_index" table using the script provided in pg_advise_index contrib module. -CONTEXT: SQL statement "insert into "advise_index" values( 16395, array[1], 1782.719482, 2608, 5256, now());" -postgres=> -postgres=> /* create a table with same signature but different name */; -postgres=> drop table if exists advise_index; -DROP TABLE -postgres=> drop view if exists advise_index; -NOTICE: view "advise_index" does not exist, skipping -DROP VIEW -postgres=> -postgres=> drop table if exists advise_index_data cascade; -NOTICE: table "advise_index_data" does not exist, skipping -DROP TABLE -postgres=> -postgres=> create table advise_index_data( reloid oid, attrs integer[], profit real, -postgres(> index_size integer, backend_pid integer, -postgres(> timestamp timestamptz); -CREATE TABLE -postgres=> -postgres=> /* and a view on that table */; -postgres=> create view advise_index as select * from advise_index_data; -CREATE VIEW -postgres=> -postgres=> /* now try to insert into the view, and notice the ERROR, DETAIL and HINT from executor */; -postgres=> explain select * from t where a = 100; -LOG: Index Adviser: Plan using estimates for suggested indexes: - -Index Scan using idx_adv_0 on t (cost=0.00..8.28 rows=1 width=8) - Index Cond: (a = 100) - -ERROR: cannot insert into a view -DETAIL: Index Adviser uses "advise_index" table to store it's advisory. You should have INSERT permissions on a table or an (INSERT-able) view named "advise_index". Also, make sure that you are not running the Index Adviser under a read-only transaction. -HINT: Please create the "advise_index" table using the script provided in pg_advise_index contrib module. -CONTEXT: SQL statement "insert into "advise_index" values( 16395, array[1], 1782.719482, 2608, 5256, now());" -postgres=> -postgres=> /* now create a RULE on the view that redirects the INSERTs into the -table */; -postgres=> create or replace rule advise_index_insert as -postgres-> ON INSERT to advise_index -postgres-> do instead -postgres-> INSERT into advise_index_data values (new.reloid, new.attrs, new.profit, -postgres(> new.index_size, new.backend_pid, -postgres(> new.timestamp) ; -CREATE RULE -postgres=> -postgres=> /* and voila, (internal) INSERT into the view succeeds! */; -postgres=> explain select * from t where a = 100; -LOG: Index Adviser: Plan using estimates for suggested indexes: - -Index Scan using idx_adv_0 on t (cost=0.00..8.28 rows=1 width=8) - Index Cond: (a = 100) - - QUERY PLAN ----------------------------------------------------- - Seq Scan on t (cost=0.00..1791.00 rows=1 width=8) - Filter: (a = 100) -(2 rows) - -postgres=> -postgres=> /* Now, lets try what happens under a read-only transaction */; -postgres=> begin; -BEGIN -postgres=> -postgres=> set transaction_read_only=t; -SET -postgres=> -postgres=> show transaction_read_only; - transaction_read_only ------------------------ - on -(1 row) - -postgres=> -postgres=> explain select * from t where a = 100; -LOG: Index Adviser: Plan using estimates for suggested indexes: - -Index Scan using idx_adv_0 on t (cost=0.00..8.28 rows=1 width=8) - Index Cond: (a = 100) - -ERROR: transaction is read-only -DETAIL: Index Adviser uses "advise_index" table to store it's advisory. You should have INSERT permissions on a table or an (INSERT-able) view named "advise_index". Also, make sure that you are not running the Index Adviser under a read-only transaction. -HINT: Please create the "advise_index" table using the script provided in pg_advise_index contrib module. -CONTEXT: SQL statement "insert into "advise_index" values( 16395, array[1], 1782.719482, 2608, 5256, now());" -postgres=> -postgres=> end; -ROLLBACK -postgres=> -postgres=> select * from advise_index; - reloid | attrs | profit | index_size | backend_pid | timestamp ---------+-------+---------+------------+-------------+------------------------------ - 16395 | {1} | 1782.72 | 2608 | 5256 | 2007-01-13 13:08:54.25+05:30 -(1 row) - -postgres=> -postgres=> select * from advise_index_data; - reloid | attrs | profit | index_size | backend_pid | timestamp ---------+-------+---------+------------+-------------+------------------------------ - 16395 | {1} | 1782.72 | 2608 | 5256 | 2007-01-13 13:08:54.25+05:30 -(1 row) - -postgres=> + +postgres=> \c postgres test +You are now connected to database "postgres". +postgres=> +postgres=> drop table if exists advise_index; +NOTICE: table "advise_index" does not exist, skipping +DROP TABLE +postgres=> drop view if exists advise_index; +NOTICE: view "advise_index" does not exist, skipping +DROP VIEW +postgres=> +postgres=> /* create the advise_index same as provided in the contrib module */; +postgres=> create table advise_index( reloid oid, attrs integer[], profit real, +postgres(> index_size integer, backend_pid integer, +postgres(> timestamp timestamptz); +CREATE TABLE +postgres=> +postgres=> /* set the client to see the log messages generated by the Adviser */; +postgres=> set client_min_messages to log; +SET +postgres=> +postgres=> /* As expected, the EXPLAIN will work */; +postgres=> explain select * from t where a = 100; +LOG: Index Adviser: Plan using estimates for suggested indexes: + +Index Scan using idx_adv_0 on t (cost=0.00..8.28 rows=1 width=8) + Index Cond: (a = 100) + + QUERY PLAN +---------------------------------------------------- + Seq Scan on t (cost=0.00..1791.00 rows=1 width=8) + Filter: (a = 100) +(2 rows) + +postgres=> +postgres=> select * from advise_index; + reloid | attrs | profit | index_size | backend_pid | timestamp +--------+-------+---------+------------+-------------+------------------------------- + 16395 | {1} | 1782.72 | 2608 | 5256 | 2007-01-13 13:08:53.593+05:30 +(1 row) + +postgres=> +postgres=> /* Now lets drop the advise_index and see what ERROR it throws */; +postgres=> drop table if exists advise_index; +DROP TABLE +postgres=> drop view if exists advise_index; +NOTICE: view "advise_index" does not exist, skipping +DROP VIEW +postgres=> +postgres=> explain select * from t where a = 100; +LOG: Index Adviser: Plan using estimates for suggested indexes: + +Index Scan using idx_adv_0 on t (cost=0.00..8.28 rows=1 width=8) + Index Cond: (a = 100) + +ERROR: relation "advise_index" does not exist. +DETAIL: Index Adviser uses "advise_index" table to store it's advisory. You should have INSERT permissions on a table or an (INSERT-able) view named "advise_index". Also, make sure that you are not running the Index Adviser under a read-only transaction. +HINT: Please create the "advise_index" table using the script provided in pg_advise_index contrib module. +postgres=> +postgres=> /* create another object by the same name (in the same namespace) */; + +postgres=> create index advise_index on t1(a); +CREATE INDEX +postgres=> +postgres=> /* advise_index does exist, but its not a table or view! */; +postgres=> explain select * from t where a = 100; +LOG: Index Adviser: Plan using estimates for suggested indexes: + +Index Scan using idx_adv_0 on t (cost=0.00..8.28 rows=1 width=8) + Index Cond: (a = 100) + +ERROR: "advise_index" is not a table or view. +DETAIL: Index Adviser uses "advise_index" table to store it's advisory. You should have INSERT permissions on a table or an (INSERT-able) view named "advise_index". Also, make sure that you are not running the Index Adviser under a read-only transaction. +HINT: Please create the "advise_index" table using the script provided in pg_advise_index contrib module. +postgres=> +postgres=> /* now create a table named advise_index, but with a different signature! */; +postgres=> drop index advise_index; +DROP INDEX +postgres=> +postgres=> create table advise_index(a int); +CREATE TABLE +postgres=> +postgres=> /* This ERROR comes from the executor, but we still see our DETAIL and HINT */; +postgres=> explain select * from t where a = 100; +LOG: Index Adviser: Plan using estimates for suggested indexes: + +Index Scan using idx_adv_0 on t (cost=0.00..8.28 rows=1 width=8) + Index Cond: (a = 100) + +ERROR: INSERT has more expressions than target columns +DETAIL: Index Adviser uses "advise_index" table to store it's advisory. You should have INSERT permissions on a table or an (INSERT-able) view named "advise_index". Also, make sure that you are not running the Index Adviser under a read-only transaction. +HINT: Please create the "advise_index" table using the script provided in pg_advise_index contrib module. +CONTEXT: SQL statement "insert into "advise_index" values( 16395, array[1], 1782.719482, 2608, 5256, now());" +postgres=> +postgres=> /* create a table with same signature but different name */; +postgres=> drop table if exists advise_index; +DROP TABLE +postgres=> drop view if exists advise_index; +NOTICE: view "advise_index" does not exist, skipping +DROP VIEW +postgres=> +postgres=> drop table if exists advise_index_data cascade; +NOTICE: table "advise_index_data" does not exist, skipping +DROP TABLE +postgres=> +postgres=> create table advise_index_data( reloid oid, attrs integer[], profit real, +postgres(> index_size integer, backend_pid integer, +postgres(> timestamp timestamptz); +CREATE TABLE +postgres=> +postgres=> /* and a view on that table */; +postgres=> create view advise_index as select * from advise_index_data; +CREATE VIEW +postgres=> +postgres=> /* now try to insert into the view, and notice the ERROR, DETAIL and HINT from executor */; +postgres=> explain select * from t where a = 100; +LOG: Index Adviser: Plan using estimates for suggested indexes: + +Index Scan using idx_adv_0 on t (cost=0.00..8.28 rows=1 width=8) + Index Cond: (a = 100) + +ERROR: cannot insert into a view +DETAIL: Index Adviser uses "advise_index" table to store it's advisory. You should have INSERT permissions on a table or an (INSERT-able) view named "advise_index". Also, make sure that you are not running the Index Adviser under a read-only transaction. +HINT: Please create the "advise_index" table using the script provided in pg_advise_index contrib module. +CONTEXT: SQL statement "insert into "advise_index" values( 16395, array[1], 1782.719482, 2608, 5256, now());" +postgres=> +postgres=> /* now create a RULE on the view that redirects the INSERTs into the +table */; +postgres=> create or replace rule advise_index_insert as +postgres-> ON INSERT to advise_index +postgres-> do instead +postgres-> INSERT into advise_index_data values (new.reloid, new.attrs, new.profit, +postgres(> new.index_size, new.backend_pid, +postgres(> new.timestamp) ; +CREATE RULE +postgres=> +postgres=> /* and voila, (internal) INSERT into the view succeeds! */; +postgres=> explain select * from t where a = 100; +LOG: Index Adviser: Plan using estimates for suggested indexes: + +Index Scan using idx_adv_0 on t (cost=0.00..8.28 rows=1 width=8) + Index Cond: (a = 100) + + QUERY PLAN +---------------------------------------------------- + Seq Scan on t (cost=0.00..1791.00 rows=1 width=8) + Filter: (a = 100) +(2 rows) + +postgres=> +postgres=> /* Now, lets try what happens under a read-only transaction */; +postgres=> begin; +BEGIN +postgres=> +postgres=> set transaction_read_only=t; +SET +postgres=> +postgres=> show transaction_read_only; + transaction_read_only +----------------------- + on +(1 row) + +postgres=> +postgres=> explain select * from t where a = 100; +LOG: Index Adviser: Plan using estimates for suggested indexes: + +Index Scan using idx_adv_0 on t (cost=0.00..8.28 rows=1 width=8) + Index Cond: (a = 100) + +ERROR: transaction is read-only +DETAIL: Index Adviser uses "advise_index" table to store it's advisory. You should have INSERT permissions on a table or an (INSERT-able) view named "advise_index". Also, make sure that you are not running the Index Adviser under a read-only transaction. +HINT: Please create the "advise_index" table using the script provided in pg_advise_index contrib module. +CONTEXT: SQL statement "insert into "advise_index" values( 16395, array[1], 1782.719482, 2608, 5256, now());" +postgres=> +postgres=> end; +ROLLBACK +postgres=> +postgres=> select * from advise_index; + reloid | attrs | profit | index_size | backend_pid | timestamp +--------+-------+---------+------------+-------------+------------------------------ + 16395 | {1} | 1782.72 | 2608 | 5256 | 2007-01-13 13:08:54.25+05:30 +(1 row) + +postgres=> +postgres=> select * from advise_index_data; + reloid | attrs | profit | index_size | backend_pid | timestamp +--------+-------+---------+------------+-------------+------------------------------ + 16395 | {1} | 1782.72 | 2608 | 5256 | 2007-01-13 13:08:54.25+05:30 +(1 row) + +postgres=> diff --git a/resources/sample_psql_session.sql b/resources/sample_psql_session.sql index f3ba473..56e56de 100644 --- a/resources/sample_psql_session.sql +++ b/resources/sample_psql_session.sql @@ -1,80 +1,80 @@ - -create language plpgsql; - -drop schema if exists test cascade; - -drop user if exists test; - -create user test; - -create schema test authorization test; - -\c postgres test - -load '$libdir/plugins/index_adviser'; - -drop view if exists select_index_advisory; -drop function if exists show_index_advisory(index_advisory.backend_pid%type); -drop table if exists index_advisory; - -\i contrib/pgadviser/resources/index_advisory.create.sql - -\i contrib/pgadviser/resources/show_index_advisory.create.sql - -\i contrib/pgadviser/resources/select_index_advisory.create.sql - -drop table if exists t, t1; - -create table t( a int, b int ); - -insert into t select s, 99999-s from generate_series(0,99999) as s; - -analyze t; - -create table t1 as select * from t; - -/* notice no ANALYZE for T1 */; - -select count(*) from t; - -select count(*) from t1; - -/* stop here if you wish to run only pg_advise tool */; - -/* set the client to see the log messages generated by the Adviser */; -set client_min_messages to log; - -explain select * from t where a = 100; - -explain select * from t where b = 100; - -explain select * from t where a = 100 and b = 100; - -explain select * from t where a = 100 or b = 100; - -/* now we shall test with T1 */; - -explain select * from t1 where a = 100; - -explain select * from t1 where b = 100; - -explain select * from t1 where a = 100 and b = 100; - -explain select * from t1 where a = 100 or b = 100; - -/* let's do some sensible join over these two tables */; -explain -select * -from t, - t1 -where t.a = 100 -and t1.a = 100 - or t.b = 100 - and t1.b = 100; - -/* following are the contents of the advise_index table */; -select * from index_advisory; - -/* And following is the summary of the advices generated */; - -select * from select_index_advisory; + +create language plpgsql; + +drop schema if exists test cascade; + +drop user if exists test; + +create user test; + +create schema test authorization test; + +\c postgres test + +load '$libdir/plugins/index_adviser'; + +drop view if exists select_index_advisory; +drop function if exists show_index_advisory(index_advisory.backend_pid%type); +drop table if exists index_advisory; + +\i contrib/pgadviser/resources/index_advisory.create.sql + +\i contrib/pgadviser/resources/show_index_advisory.create.sql + +\i contrib/pgadviser/resources/select_index_advisory.create.sql + +drop table if exists t, t1; + +create table t( a int, b int ); + +insert into t select s, 99999-s from generate_series(0,99999) as s; + +analyze t; + +create table t1 as select * from t; + +/* notice no ANALYZE for T1 */; + +select count(*) from t; + +select count(*) from t1; + +/* stop here if you wish to run only pg_advise tool */; + +/* set the client to see the log messages generated by the Adviser */; +set client_min_messages to log; + +explain select * from t where a = 100; + +explain select * from t where b = 100; + +explain select * from t where a = 100 and b = 100; + +explain select * from t where a = 100 or b = 100; + +/* now we shall test with T1 */; + +explain select * from t1 where a = 100; + +explain select * from t1 where b = 100; + +explain select * from t1 where a = 100 and b = 100; + +explain select * from t1 where a = 100 or b = 100; + +/* let's do some sensible join over these two tables */; +explain +select * +from t, + t1 +where t.a = 100 +and t1.a = 100 + or t.b = 100 + and t1.b = 100; + +/* following are the contents of the advise_index table */; +select * from index_advisory; + +/* And following is the summary of the advices generated */; + +select * from select_index_advisory; diff --git a/resources/sample_psql_session.txt b/resources/sample_psql_session.txt index 149451f..1ab3b8a 100644 --- a/resources/sample_psql_session.txt +++ b/resources/sample_psql_session.txt @@ -1,271 +1,271 @@ - -Welcome to psql 8.2.1, the PostgreSQL interactive terminal. - -Type: \copyright for distribution terms - \h for help with SQL commands - \? for help with psql commands - \g or terminate with semicolon to execute query - \q to quit - -Warning: Console code page (437) differs from Windows code page (1252) - 8-bit characters may not work correctly. See psql reference - page "Notes for Windows users" for details. - -postgres=# /* -postgres*# You must start the session with something like: -postgres*# -postgres*# env PGOPTIONS='-c local_preload_libraries=libpg_index_adviser' psql postgres -postgres*# */; -postgres=# -postgres=# create language plpgsql; -CREATE LANGUAGE -postgres=# -postgres=# drop schema if exists test cascade; -NOTICE: schema "test" does not exist, skipping -DROP SCHEMA -postgres=# -postgres=# drop user if exists test; -NOTICE: role "test" does not exist, skipping -DROP ROLE -postgres=# -postgres=# create user test; -CREATE ROLE -postgres=# -postgres=# create schema test authorization test; -CREATE SCHEMA -postgres=# -postgres=# \c postgres test -You are now connected to database "postgres" as user "test". -postgres=> -postgres=> drop table if exists advise_index; -NOTICE: table "advise_index" does not exist, skipping -DROP TABLE -postgres=> -postgres=> \i contrib/pg_index_adviser/advise_index.create.sql -CREATE TABLE -postgres=> -postgres=> \d advise_index; - Table "test.advise_index" - Column | Type | Modifiers --------------+--------------------------+----------- - reloid | oid | - attrs | integer[] | - profit | real | - index_size | integer | - backend_pid | integer | - timestamp | timestamp with time zone | - -postgres=> -postgres=> \i contrib/pg_index_adviser/advise_index_show.create.sql -CREATE FUNCTION -postgres=> -postgres=> select * from advise_index; - reloid | attrs | profit | index_size | backend_pid | timestamp ---------+-------+--------+------------+-------------+----------- -(0 rows) - -postgres=> -postgres=> drop table if exists t, t1; -NOTICE: table "t" does not exist, skipping -NOTICE: table "t1" does not exist, skipping -DROP TABLE -postgres=> -postgres=> create table t( a int, b int ); -CREATE TABLE -postgres=> -postgres=> insert into t select s, 99999-s from generate_series(0,99999) as s; -INSERT 0 100000 -postgres=> -postgres=> analyze t; -ANALYZE -postgres=> -postgres=> create table t1 as select * from t; -SELECT -postgres=> -postgres=> /* notice no ANALYZE for T1 */; -postgres=> -postgres=> select count(*) from t; - count --------- - 100000 -(1 row) - -postgres=> -postgres=> select count(*) from t1; - count --------- - 100000 -(1 row) - -postgres=> -postgres=> /* stop here if you wish to run only pg_advise_index tool */; -postgres=> -postgres=> -- set enable_advise_index to true; -postgres=> /* set the client to see the log messages generated by the Adviser */ -; -postgres=> set client_min_messages to log; -SET -postgres=> -postgres=> explain select * from t where a = 100; -LOG: Index Adviser: Plan using estimates for suggested indexes: - -Index Scan using idx_adv_0 on t (cost=0.00..8.28 rows=1 width=8) - Index Cond: (a = 100) - - QUERY PLAN ----------------------------------------------------- - Seq Scan on t (cost=0.00..1791.00 rows=1 width=8) - Filter: (a = 100) -(2 rows) - -postgres=> -postgres=> explain select * from t where b = 100; -LOG: Index Adviser: Plan using estimates for suggested indexes: - -Index Scan using idx_adv_0 on t (cost=0.00..8.28 rows=1 width=8) - Index Cond: (b = 100) - - QUERY PLAN ----------------------------------------------------- - Seq Scan on t (cost=0.00..1791.00 rows=1 width=8) - Filter: (b = 100) -(2 rows) - -postgres=> -postgres=> explain select * from t where a = 100 and b = 100; -LOG: Index Adviser: Plan using estimates for suggested indexes: - -Index Scan using idx_adv_1 on t (cost=0.00..8.28 rows=1 width=8) - Index Cond: (b = 100) - Filter: (a = 100) - - QUERY PLAN ----------------------------------------------------- - Seq Scan on t (cost=0.00..2041.00 rows=1 width=8) - Filter: ((a = 100) AND (b = 100)) -(2 rows) - -postgres=> -postgres=> explain select * from t where a = 100 or b = 100; -LOG: Index Adviser: Plan using estimates for suggested indexes: - -Bitmap Heap Scan on t (cost=8.54..16.21 rows=2 width=8) - Recheck Cond: ((a = 100) OR (b = 100)) - -> BitmapOr (cost=8.54..8.54 rows=2 width=0) - -> Bitmap Index Scan on idx_adv_0 (cost=0.00..4.27 rows=1 width=0) - Index Cond: (a = 100) - -> Bitmap Index Scan on idx_adv_1 (cost=0.00..4.27 rows=1 width=0) - Index Cond: (b = 100) - - QUERY PLAN ----------------------------------------------------- - Seq Scan on t (cost=0.00..2041.00 rows=2 width=8) - Filter: ((a = 100) OR (b = 100)) -(2 rows) - -postgres=> -postgres=> /* now we shall test with T1 */; -postgres=> -postgres=> explain select * from t1 where a = 100; - QUERY PLAN -------------------------------------------------------- - Seq Scan on t1 (cost=0.00..1852.93 rows=525 width=8) - Filter: (a = 100) -(2 rows) - -postgres=> -postgres=> explain select * from t1 where b = 100; - QUERY PLAN -------------------------------------------------------- - Seq Scan on t1 (cost=0.00..1852.93 rows=525 width=8) - Filter: (b = 100) -(2 rows) - -postgres=> -postgres=> explain select * from t1 where a = 100 and b = 100; - QUERY PLAN ------------------------------------------------------ - Seq Scan on t1 (cost=0.00..2115.31 rows=3 width=8) - Filter: ((a = 100) AND (b = 100)) -(2 rows) - -postgres=> -postgres=> explain select * from t1 where a = 100 or b = 100; - QUERY PLAN --------------------------------------------------------- - Seq Scan on t1 (cost=0.00..2115.31 rows=1047 width=8) - Filter: ((a = 100) OR (b = 100)) -(2 rows) - -postgres=> -postgres=> /* let's do some sensible join over these two tables */; -postgres=> explain -postgres-> select * -postgres-> from t, -postgres-> t1 -postgres-> where t.a = 100 -postgres-> and t1.a = 100 -postgres-> or t.b = 100 -postgres-> and t1.b = 100; -LOG: Index Adviser: Plan using estimates for suggested indexes: - -Nested Loop (cost=8.54..7395.45 rows=1050 width=16) - Join Filter: (((t.a = 100) AND (t1.a = 100)) OR ((t.b = 100) AND (t1.b = 100))) - -> Bitmap Heap Scan on t (cost=8.54..16.21 rows=2 width=8) - Recheck Cond: ((a = 100) OR (b = 100)) - -> BitmapOr (cost=8.54..8.54 rows=2 width=0) - -> Bitmap Index Scan on idx_adv_0 (cost=0.00..4.27 rows=1 width=0) - Index Cond: (a = 100) - -> Bitmap Index Scan on idx_adv_1 (cost=0.00..4.27 rows=1 width=0) - Index Cond: (b = 100) - -> Seq Scan on t1 (cost=0.00..1590.54 rows=104954 width=8) - - QUERY PLAN ------------------------------------------------------------------------------------ - Nested Loop (cost=0.00..368963541.00 rows=1050 width=16) - Join Filter: (((t.a = 100) AND (t1.a = 100)) OR ((t.b = 100) AND (t1.b = 100))) - -> Seq Scan on t (cost=0.00..1541.00 rows=100000 width=8) - -> Seq Scan on t1 (cost=0.00..1590.54 rows=104954 width=8) -(4 rows) - -postgres=> -postgres=> /* following are the contents of the advise_index table */; -postgres=> select * from advise_index; - reloid | attrs | profit | index_size | backend_pid | timestamp ---------+-------+--------------+------------+-------------+------------------------------- - 16395 | {1} | 1782.72 | 2608 | 2272 | 2007-01-13 12:30:02.39+05:30 - 16395 | {2} | 1782.72 | 2608 | 2272 | 2007-01-13 12:30:02.531+05:30 - 16395 | {2} | 2032.72 | 2608 | 2272 | 2007-01-13 12:30:02.578+05:30 - 16395 | {1} | 1012.4 | 2608 | 2272 | 2007-01-13 12:30:02.64+05:30 - 16395 | {2} | 1012.4 | 2608 | 2272 | 2007-01-13 12:30:02.64+05:30 - 16395 | {1} | 1.84478e+008 | 2608 | 2272 | 2007-01-13 12:30:17.984+05:30 - 16395 | {2} | 1.84478e+008 | 2608 | 2272 | 2007-01-13 12:30:17.984+05:30 -(7 rows) - -postgres=> -postgres=> /* And following is the summary of the advices generated */; -postgres=> -postgres=> select E'backend_pid\n' -postgres-> || E'===========\n' -postgres-> || backend_pid, -postgres-> advise_index_show( backend_pid ) -postgres-> from (select distinct backend_pid -postgres(> from advise_index as adv -postgres(> where adv.reloid in (select oid -postgres(> from pg_class -postgres(> where relkind = 'r') -postgres(> ) as v; - ?column? | advise_index_show --------------+-------------------------------------------------------------------- - backend_pid | /* Index Adviser */ - =========== : /* ============= */ - 2272 : - : /* size: 2608 KB, benefit: 1.84483e+008, gain: 70737.3067484663 */ - : create index idx_t_b on t(b); - : - : /* size: 2608 KB, benefit: 1.84481e+008, gain: 70736.527607362 */ - : create index idx_t_a on t(a); - : -(1 row) - -postgres=> + +Welcome to psql 8.2.1, the PostgreSQL interactive terminal. + +Type: \copyright for distribution terms + \h for help with SQL commands + \? for help with psql commands + \g or terminate with semicolon to execute query + \q to quit + +Warning: Console code page (437) differs from Windows code page (1252) + 8-bit characters may not work correctly. See psql reference + page "Notes for Windows users" for details. + +postgres=# /* +postgres*# You must start the session with something like: +postgres*# +postgres*# env PGOPTIONS='-c local_preload_libraries=libpg_index_adviser' psql postgres +postgres*# */; +postgres=# +postgres=# create language plpgsql; +CREATE LANGUAGE +postgres=# +postgres=# drop schema if exists test cascade; +NOTICE: schema "test" does not exist, skipping +DROP SCHEMA +postgres=# +postgres=# drop user if exists test; +NOTICE: role "test" does not exist, skipping +DROP ROLE +postgres=# +postgres=# create user test; +CREATE ROLE +postgres=# +postgres=# create schema test authorization test; +CREATE SCHEMA +postgres=# +postgres=# \c postgres test +You are now connected to database "postgres" as user "test". +postgres=> +postgres=> drop table if exists advise_index; +NOTICE: table "advise_index" does not exist, skipping +DROP TABLE +postgres=> +postgres=> \i contrib/pg_index_adviser/advise_index.create.sql +CREATE TABLE +postgres=> +postgres=> \d advise_index; + Table "test.advise_index" + Column | Type | Modifiers +-------------+--------------------------+----------- + reloid | oid | + attrs | integer[] | + profit | real | + index_size | integer | + backend_pid | integer | + timestamp | timestamp with time zone | + +postgres=> +postgres=> \i contrib/pg_index_adviser/advise_index_show.create.sql +CREATE FUNCTION +postgres=> +postgres=> select * from advise_index; + reloid | attrs | profit | index_size | backend_pid | timestamp +--------+-------+--------+------------+-------------+----------- +(0 rows) + +postgres=> +postgres=> drop table if exists t, t1; +NOTICE: table "t" does not exist, skipping +NOTICE: table "t1" does not exist, skipping +DROP TABLE +postgres=> +postgres=> create table t( a int, b int ); +CREATE TABLE +postgres=> +postgres=> insert into t select s, 99999-s from generate_series(0,99999) as s; +INSERT 0 100000 +postgres=> +postgres=> analyze t; +ANALYZE +postgres=> +postgres=> create table t1 as select * from t; +SELECT +postgres=> +postgres=> /* notice no ANALYZE for T1 */; +postgres=> +postgres=> select count(*) from t; + count +-------- + 100000 +(1 row) + +postgres=> +postgres=> select count(*) from t1; + count +-------- + 100000 +(1 row) + +postgres=> +postgres=> /* stop here if you wish to run only pg_advise_index tool */; +postgres=> +postgres=> -- set enable_advise_index to true; +postgres=> /* set the client to see the log messages generated by the Adviser */ +; +postgres=> set client_min_messages to log; +SET +postgres=> +postgres=> explain select * from t where a = 100; +LOG: Index Adviser: Plan using estimates for suggested indexes: + +Index Scan using idx_adv_0 on t (cost=0.00..8.28 rows=1 width=8) + Index Cond: (a = 100) + + QUERY PLAN +---------------------------------------------------- + Seq Scan on t (cost=0.00..1791.00 rows=1 width=8) + Filter: (a = 100) +(2 rows) + +postgres=> +postgres=> explain select * from t where b = 100; +LOG: Index Adviser: Plan using estimates for suggested indexes: + +Index Scan using idx_adv_0 on t (cost=0.00..8.28 rows=1 width=8) + Index Cond: (b = 100) + + QUERY PLAN +---------------------------------------------------- + Seq Scan on t (cost=0.00..1791.00 rows=1 width=8) + Filter: (b = 100) +(2 rows) + +postgres=> +postgres=> explain select * from t where a = 100 and b = 100; +LOG: Index Adviser: Plan using estimates for suggested indexes: + +Index Scan using idx_adv_1 on t (cost=0.00..8.28 rows=1 width=8) + Index Cond: (b = 100) + Filter: (a = 100) + + QUERY PLAN +---------------------------------------------------- + Seq Scan on t (cost=0.00..2041.00 rows=1 width=8) + Filter: ((a = 100) AND (b = 100)) +(2 rows) + +postgres=> +postgres=> explain select * from t where a = 100 or b = 100; +LOG: Index Adviser: Plan using estimates for suggested indexes: + +Bitmap Heap Scan on t (cost=8.54..16.21 rows=2 width=8) + Recheck Cond: ((a = 100) OR (b = 100)) + -> BitmapOr (cost=8.54..8.54 rows=2 width=0) + -> Bitmap Index Scan on idx_adv_0 (cost=0.00..4.27 rows=1 width=0) + Index Cond: (a = 100) + -> Bitmap Index Scan on idx_adv_1 (cost=0.00..4.27 rows=1 width=0) + Index Cond: (b = 100) + + QUERY PLAN +---------------------------------------------------- + Seq Scan on t (cost=0.00..2041.00 rows=2 width=8) + Filter: ((a = 100) OR (b = 100)) +(2 rows) + +postgres=> +postgres=> /* now we shall test with T1 */; +postgres=> +postgres=> explain select * from t1 where a = 100; + QUERY PLAN +------------------------------------------------------- + Seq Scan on t1 (cost=0.00..1852.93 rows=525 width=8) + Filter: (a = 100) +(2 rows) + +postgres=> +postgres=> explain select * from t1 where b = 100; + QUERY PLAN +------------------------------------------------------- + Seq Scan on t1 (cost=0.00..1852.93 rows=525 width=8) + Filter: (b = 100) +(2 rows) + +postgres=> +postgres=> explain select * from t1 where a = 100 and b = 100; + QUERY PLAN +----------------------------------------------------- + Seq Scan on t1 (cost=0.00..2115.31 rows=3 width=8) + Filter: ((a = 100) AND (b = 100)) +(2 rows) + +postgres=> +postgres=> explain select * from t1 where a = 100 or b = 100; + QUERY PLAN +-------------------------------------------------------- + Seq Scan on t1 (cost=0.00..2115.31 rows=1047 width=8) + Filter: ((a = 100) OR (b = 100)) +(2 rows) + +postgres=> +postgres=> /* let's do some sensible join over these two tables */; +postgres=> explain +postgres-> select * +postgres-> from t, +postgres-> t1 +postgres-> where t.a = 100 +postgres-> and t1.a = 100 +postgres-> or t.b = 100 +postgres-> and t1.b = 100; +LOG: Index Adviser: Plan using estimates for suggested indexes: + +Nested Loop (cost=8.54..7395.45 rows=1050 width=16) + Join Filter: (((t.a = 100) AND (t1.a = 100)) OR ((t.b = 100) AND (t1.b = 100))) + -> Bitmap Heap Scan on t (cost=8.54..16.21 rows=2 width=8) + Recheck Cond: ((a = 100) OR (b = 100)) + -> BitmapOr (cost=8.54..8.54 rows=2 width=0) + -> Bitmap Index Scan on idx_adv_0 (cost=0.00..4.27 rows=1 width=0) + Index Cond: (a = 100) + -> Bitmap Index Scan on idx_adv_1 (cost=0.00..4.27 rows=1 width=0) + Index Cond: (b = 100) + -> Seq Scan on t1 (cost=0.00..1590.54 rows=104954 width=8) + + QUERY PLAN +----------------------------------------------------------------------------------- + Nested Loop (cost=0.00..368963541.00 rows=1050 width=16) + Join Filter: (((t.a = 100) AND (t1.a = 100)) OR ((t.b = 100) AND (t1.b = 100))) + -> Seq Scan on t (cost=0.00..1541.00 rows=100000 width=8) + -> Seq Scan on t1 (cost=0.00..1590.54 rows=104954 width=8) +(4 rows) + +postgres=> +postgres=> /* following are the contents of the advise_index table */; +postgres=> select * from advise_index; + reloid | attrs | profit | index_size | backend_pid | timestamp +--------+-------+--------------+------------+-------------+------------------------------- + 16395 | {1} | 1782.72 | 2608 | 2272 | 2007-01-13 12:30:02.39+05:30 + 16395 | {2} | 1782.72 | 2608 | 2272 | 2007-01-13 12:30:02.531+05:30 + 16395 | {2} | 2032.72 | 2608 | 2272 | 2007-01-13 12:30:02.578+05:30 + 16395 | {1} | 1012.4 | 2608 | 2272 | 2007-01-13 12:30:02.64+05:30 + 16395 | {2} | 1012.4 | 2608 | 2272 | 2007-01-13 12:30:02.64+05:30 + 16395 | {1} | 1.84478e+008 | 2608 | 2272 | 2007-01-13 12:30:17.984+05:30 + 16395 | {2} | 1.84478e+008 | 2608 | 2272 | 2007-01-13 12:30:17.984+05:30 +(7 rows) + +postgres=> +postgres=> /* And following is the summary of the advices generated */; +postgres=> +postgres=> select E'backend_pid\n' +postgres-> || E'===========\n' +postgres-> || backend_pid, +postgres-> advise_index_show( backend_pid ) +postgres-> from (select distinct backend_pid +postgres(> from advise_index as adv +postgres(> where adv.reloid in (select oid +postgres(> from pg_class +postgres(> where relkind = 'r') +postgres(> ) as v; + ?column? | advise_index_show +-------------+-------------------------------------------------------------------- + backend_pid | /* Index Adviser */ + =========== : /* ============= */ + 2272 : + : /* size: 2608 KB, benefit: 1.84483e+008, gain: 70737.3067484663 */ + : create index idx_t_b on t(b); + : + : /* size: 2608 KB, benefit: 1.84481e+008, gain: 70736.527607362 */ + : create index idx_t_a on t(a); + : +(1 row) + +postgres=> diff --git a/resources/select_index_advisory.create.sql b/resources/select_index_advisory.create.sql index 0a762a2..c9137b6 100644 --- a/resources/select_index_advisory.create.sql +++ b/resources/select_index_advisory.create.sql @@ -1,13 +1,13 @@ - -create view select_index_advisory as -select E'backend_pid\n' - || E'===========\n' - || backend_pid, - show_index_advisory( backend_pid ) -from (select distinct backend_pid - from index_advisory as adv - /* do not consider tables that no longer exist */ - where exists (select oid - from pg_class - where oid = adv.reloid) - ) as v; + +create or replace view select_index_advisory as +select E'backend_pid\n' + || E'===========\n' + || backend_pid, + show_index_advisory( backend_pid ) +from (select distinct backend_pid + from index_advisory as adv + /* do not consider tables that no longer exist */ + where exists (select oid + from pg_class + where oid = adv.reloid) + ) as v; diff --git a/resources/show_index_advisory.create.sql b/resources/show_index_advisory.create.sql index 5764a90..aba1e61 100644 --- a/resources/show_index_advisory.create.sql +++ b/resources/show_index_advisory.create.sql @@ -1,100 +1,102 @@ - -create function show_index_advisory(p_backend_pid index_advisory.backend_pid%type) returns text -as -$$ -declare - pid p_backend_pid%type; - q_advice text; - r_advice record; - q_column text; - r_column record; - ret text; - - NAMEDATALEN int := 64; - - collist_w_C text; /* Column name list with commas */ - collist_w_U text; /* Column name list with underscores */ - colidlist_w_U text; /* Column id list with underscores */ -begin - if p_backend_pid is null then - pid = pg_backend_pid(); - else - pid = p_backend_pid; - end if; - - ret := '/* Index Adviser */' || E'\n' || - '/* ============= */' || E'\n'; - - q_advice := 'SELECT c.relname, - c.oid as reloid, - a.attrs AS colids, - MAX( a.index_size ) AS size_in_KB, - SUM( a.profit ) AS benefit, - SUM( a.profit )/MAX( a.index_size ) AS gain - FROM index_advisory a, - pg_class c - WHERE a.backend_pid = ' || pid || ' - AND a.reloid = c.oid - GROUP BY c.relname, c.oid, a.attrs - ORDER BY gain - DESC'; - - for r_advice in execute q_advice loop - - ret := ret || - E'\n/* size: ' || r_advice.size_in_KB || ' KB, ' - || 'benefit: ' || r_advice.benefit || ', ' - || 'gain: ' || r_advice.gain || E' */\n'; - - collist_w_C := ''; - collist_w_U := ''; - colidlist_w_U := ''; - - for i in array_lower( r_advice.colids, 1 ) - .. array_upper( r_advice.colids, 1 ) - loop - - q_column := 'SELECT a.attname as name, - a.attnum as id - FROM pg_class c, - pg_attribute a - WHERE c.oid = ' || r_advice.reloid || ' - AND a.attrelid = c.oid - AND a.attnum = ' || r_advice.colids[i] || ' - '; - - execute q_column into r_column; - --- if ROW_COUNT > 1 then --- raise EXCEPTION 'an internal query failed'; --- end if; - - if i <> 1 then - collist_w_C := collist_w_C || ', '; - collist_w_U := collist_w_U || '_'; - colidlist_w_U := colidlist_w_U || '_'; - end if; - - collist_w_C := collist_w_C || r_column.name; - collist_w_U := collist_w_U || r_column.name; - colidlist_w_U := colidlist_w_U || r_column.id; - - end loop; - - ret := ret || 'create index '; - - if (length('idx_' || r_advice.relname || '_' || collist_w_U) - <= NAMEDATALEN) - then - ret := ret || 'idx_' || r_advice.relname || '_' || collist_w_U; - else - ret := ret || 'idx_' || r_advice.reloid || '_' || colidlist_w_U; - end if; - - ret := ret || ' on ' || r_advice.relname || '(' || collist_w_C || E');\n'; - - end loop; - - return ret; -end; -$$ language plpgsql; + +create or replace function show_index_advisory() returns text as $$ + select show_index_advisory(null); +$$ language sql; + +create or replace function show_index_advisory(p_backend_pid index_advisory.backend_pid%type) returns text as $$ +declare + pid p_backend_pid%type; + q_advice text; + r_advice record; + q_column text; + r_column record; + ret text; + + NAMEDATALEN int := 64; + + collist_w_C text; /* Column name list with commas */ + collist_w_U text; /* Column name list with underscores */ + colidlist_w_U text; /* Column id list with underscores */ +begin + if p_backend_pid is null then + pid = pg_backend_pid(); + else + pid = p_backend_pid; + end if; + + ret := '/* Index Adviser */' || E'\n' || + '/* ============= */' || E'\n'; + + q_advice := 'SELECT c.relname, + c.oid as reloid, + a.attrs AS colids, + MAX( a.index_size ) AS size_in_KB, + SUM( a.profit ) AS benefit, + SUM( a.profit )/MAX( a.index_size ) AS gain + FROM index_advisory a, + pg_class c + WHERE a.backend_pid = ' || pid || ' + AND a.reloid = c.oid + GROUP BY c.relname, c.oid, a.attrs + ORDER BY gain + DESC'; + + for r_advice in execute q_advice loop + + ret := ret || + E'\n/* size: ' || r_advice.size_in_KB || ' KB, ' + || 'benefit: ' || r_advice.benefit || ', ' + || 'gain: ' || r_advice.gain || E' */\n'; + + collist_w_C := ''; + collist_w_U := ''; + colidlist_w_U := ''; + + for i in array_lower( r_advice.colids, 1 ) + .. array_upper( r_advice.colids, 1 ) + loop + + q_column := 'SELECT a.attname as name, + a.attnum as id + FROM pg_class c, + pg_attribute a + WHERE c.oid = ' || r_advice.reloid || ' + AND a.attrelid = c.oid + AND a.attnum = ' || r_advice.colids[i] || ' + '; + + execute q_column into r_column; + +-- if ROW_COUNT > 1 then +-- raise EXCEPTION 'an internal query failed'; +-- end if; + + if i <> 1 then + collist_w_C := collist_w_C || ', '; + collist_w_U := collist_w_U || '_'; + colidlist_w_U := colidlist_w_U || '_'; + end if; + + collist_w_C := collist_w_C || r_column.name; + collist_w_U := collist_w_U || r_column.name; + colidlist_w_U := colidlist_w_U || r_column.id; + + end loop; + + ret := ret || 'create index '; + + if (length('idx_' || r_advice.relname || '_' || collist_w_U) + <= NAMEDATALEN) + then + ret := ret || 'idx_' || r_advice.relname || '_' || collist_w_U; + else + ret := ret || 'idx_' || r_advice.reloid || '_' || colidlist_w_U; + end if; + + ret := ret || ' on ' || r_advice.relname || '(' || collist_w_C || E');\n'; + + end loop; + + return ret; +end; +$$ language plpgsql; -- 2.39.5