By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
Help with an interesting Postgres question: Why isn't an Index Only Scan used on a partition accessed via the parent table?.
-- test table with all kinds of indexes and constraints
CREATE TABLE big(
big_id serial PRIMARY KEY -- PK constraint
, big text
, usr_id integer NOT NULL
, bigtype_id integer NOT NULL
, somedate date UNIQUE -- unique constraint
, tsr tsrange
, CONSTRAINT tsr_no_overlap EXCLUDE USING gist (tsr WITH &&) -- exclusion constraint
);
CREATE INDEX big_big_fts_gin_idx ON big USING gin (to_tsvector('simple', big));
CREATE INDEX big_big_trgm_gin_idx ON big USING gin (big gin_trgm_ops);
CREATE UNIQUE INDEX big_big_uni ON big (big);
CREATE INDEX big_bigtype_id_brin_idx ON big USING brin (bigtype_id);
CREATE INDEX big_bigtype_id_idx ON big (bigtype_id);
CREATE UNIQUE INDEX big_lower_case_big_idx ON big (lower(big));
CREATE INDEX big_multi_in_test ON big (bigtype_id, big_id);
CREATE INDEX big_partial_notes_null_idx ON big (big_id) WHERE somedate IS NULL;
CREATE INDEX big_partial_stat_test_idx ON big (left(big, 3));
CREATE INDEX big_partial_test_id_idx ON big (big_id, usr_id) WHERE usr_id = 200;
CLUSTER big USING big_big_uni; -- cluster table
CREATE TABLE
CREATE INDEX
CREATE INDEX
CREATE INDEX
CREATE INDEX
CREATE INDEX
CREATE INDEX
CREATE INDEX
CREATE INDEX
CREATE INDEX
CREATE INDEX
CLUSTER
-- all indices
SELECT n.nspname AS schema, i.indrelid::regclass::text AS tbl, cl.relname AS idx, pg_get_indexdef(i.indexrelid)
, 'DROP INDEX ' || i.indexrelid::regclass AS drop_cmd
FROM pg_index i
JOIN pg_class cl ON cl.oid = i.indexrelid
JOIN pg_namespace n ON n.oid = cl.relnamespace
WHERE n.nspname <> 'information_schema'
AND n.nspname NOT LIKE 'pg\_%'
| schema | tbl | idx | pg_get_indexdef | drop_cmd |
|---|---|---|---|---|
| x_postgis | spatial_ref_sys | spatial_ref_sys_pkey | CREATE UNIQUE INDEX spatial_ref_sys_pkey ON x_postgis.spatial_ref_sys USING btree (srid) | DROP INDEX spatial_ref_sys_pkey |
| public | big | big_pkey | CREATE UNIQUE INDEX big_pkey ON public.big USING btree (big_id) | DROP INDEX big_pkey |
| public | big | big_somedate_key | CREATE UNIQUE INDEX big_somedate_key ON public.big USING btree (somedate) | DROP INDEX big_somedate_key |
| public | big | tsr_no_overlap | CREATE INDEX tsr_no_overlap ON public.big USING gist (tsr) | DROP INDEX tsr_no_overlap |
| public | big | big_big_fts_gin_idx | CREATE INDEX big_big_fts_gin_idx ON public.big USING gin (to_tsvector('simple'::regconfig, big)) | DROP INDEX big_big_fts_gin_idx |
| public | big | big_big_trgm_gin_idx | CREATE INDEX big_big_trgm_gin_idx ON public.big USING gin (big gin_trgm_ops) | DROP INDEX big_big_trgm_gin_idx |
| public | big | big_big_uni | CREATE UNIQUE INDEX big_big_uni ON public.big USING btree (big) | DROP INDEX big_big_uni |
| public | big | big_bigtype_id_brin_idx | CREATE INDEX big_bigtype_id_brin_idx ON public.big USING brin (bigtype_id) | DROP INDEX big_bigtype_id_brin_idx |
| public | big | big_bigtype_id_idx | CREATE INDEX big_bigtype_id_idx ON public.big USING btree (bigtype_id) | DROP INDEX big_bigtype_id_idx |
| public | big | big_lower_case_big_idx | CREATE UNIQUE INDEX big_lower_case_big_idx ON public.big USING btree (lower(big)) | DROP INDEX big_lower_case_big_idx |
| public | big | big_multi_in_test | CREATE INDEX big_multi_in_test ON public.big USING btree (bigtype_id, big_id) | DROP INDEX big_multi_in_test |
| public | big | big_partial_notes_null_idx | CREATE INDEX big_partial_notes_null_idx ON public.big USING btree (big_id) WHERE (somedate IS NULL) | DROP INDEX big_partial_notes_null_idx |
| public | big | big_partial_stat_test_idx | CREATE INDEX big_partial_stat_test_idx ON public.big USING btree ("left"(big, 3)) | DROP INDEX big_partial_stat_test_idx |
| public | big | big_partial_test_id_idx | CREATE INDEX big_partial_test_id_idx ON public.big USING btree (big_id, usr_id) WHERE (usr_id = 200) | DROP INDEX big_partial_test_id_idx |
SELECT 14
-- "expendable" indices
SELECT n.nspname AS schema, i.indrelid::regclass::text AS tbl, cl.relname AS idx, pg_get_indexdef(i.indexrelid)
, 'DROP INDEX ' || i.indexrelid::regclass AS drop_cmd
FROM pg_index i
JOIN pg_class cl ON cl.oid = i.indexrelid
JOIN pg_namespace n ON n.oid = cl.relnamespace
LEFT JOIN pg_constraint co ON co.conindid = i.indexrelid
WHERE n.nspname <> 'information_schema'
AND n.nspname NOT LIKE 'pg\_%'
AND co.conindid IS NULL -- no connected constraint
AND NOT i.indisprimary
AND NOT i.indisunique
AND NOT i.indisexclusion
AND NOT i.indisclustered
AND NOT i.indisreplident
ORDER BY 1, 2, 3;
| schema | tbl | idx | pg_get_indexdef | drop_cmd |
|---|---|---|---|---|
| public | big | big_big_fts_gin_idx | CREATE INDEX big_big_fts_gin_idx ON public.big USING gin (to_tsvector('simple'::regconfig, big)) | DROP INDEX big_big_fts_gin_idx |
| public | big | big_big_trgm_gin_idx | CREATE INDEX big_big_trgm_gin_idx ON public.big USING gin (big gin_trgm_ops) | DROP INDEX big_big_trgm_gin_idx |
| public | big | big_bigtype_id_brin_idx | CREATE INDEX big_bigtype_id_brin_idx ON public.big USING brin (bigtype_id) | DROP INDEX big_bigtype_id_brin_idx |
| public | big | big_bigtype_id_idx | CREATE INDEX big_bigtype_id_idx ON public.big USING btree (bigtype_id) | DROP INDEX big_bigtype_id_idx |
| public | big | big_multi_in_test | CREATE INDEX big_multi_in_test ON public.big USING btree (bigtype_id, big_id) | DROP INDEX big_multi_in_test |
| public | big | big_partial_notes_null_idx | CREATE INDEX big_partial_notes_null_idx ON public.big USING btree (big_id) WHERE (somedate IS NULL) | DROP INDEX big_partial_notes_null_idx |
| public | big | big_partial_stat_test_idx | CREATE INDEX big_partial_stat_test_idx ON public.big USING btree ("left"(big, 3)) | DROP INDEX big_partial_stat_test_idx |
| public | big | big_partial_test_id_idx | CREATE INDEX big_partial_test_id_idx ON public.big USING btree (big_id, usr_id) WHERE (usr_id = 200) | DROP INDEX big_partial_test_id_idx |
SELECT 8
-- command to drop them all
SELECT 'DROP INDEX ' || string_agg(i.indexrelid::regclass::text, ', ' ORDER BY n.nspname, i.indrelid::regclass::text, cl.relname) AS drop_cmd
FROM pg_index i
JOIN pg_class cl ON cl.oid = i.indexrelid
JOIN pg_namespace n ON n.oid = cl.relnamespace
LEFT JOIN pg_constraint co ON co.conindid = i.indexrelid
WHERE n.nspname <> 'information_schema'
AND n.nspname NOT LIKE 'pg\_%'
AND co.conindid IS NULL -- no connected constraint
AND NOT i.indisprimary
AND NOT i.indisunique
AND NOT i.indisexclusion
AND NOT i.indisclustered
AND NOT i.indisreplident;
| drop_cmd |
|---|
| DROP INDEX big_big_fts_gin_idx, big_big_trgm_gin_idx, big_bigtype_id_brin_idx, big_bigtype_id_idx, big_multi_in_test, big_partial_notes_null_idx, big_partial_stat_test_idx, big_partial_test_id_idx |
SELECT 1