local_index.out

create table test (a int, b int) partition by range (a);
create table test_0 partition of test for values from (0) to (2);
create table test_1 partition of test for values from (2) to (4)
	partition by list (a);
create table test_1_0 partition of test_1 for values in (2);
create table test_1_1 partition of test_1 for values in (3);
-- Test recursive simple index creation --
create index on test (b);
select indexrelid::regclass, indrelid::regclass
	from pg_index where indrelid::regclass::text like 'test%'
	order by indexrelid::regclass::text;
   indexrelid   | indrelid 
----------------+----------
 test_0_b_idx   | test_0
 test_1_0_b_idx | test_1_0
 test_1_1_b_idx | test_1_1
 test_1_b_idx   | test_1
 test_b_idx     | test
(5 rows)

select objid::regclass, refobjid::regclass from pg_depend
	where refobjid::regclass::text like 'test%_idx'
	order by objid::regclass::text;
     objid      |   refobjid   
----------------+--------------
 test_0_b_idx   | test_b_idx
 test_1_0_b_idx | test_1_b_idx
 test_1_1_b_idx | test_1_b_idx
 test_1_b_idx   | test_b_idx
(4 rows)

-- Test index usage in SELECT query --
insert into test select i%4, i from generate_series(1, 1000) i;
set enable_seqscan to off;
analyze test;
explain (costs off) select * from test where a=1 and b=100;
                  QUERY PLAN                   
-----------------------------------------------
 Append
   ->  Index Scan using test_0_b_idx on test_0
         Index Cond: (b = 100)
         Filter: (a = 1)
(4 rows)

-- Test recursive index dropping --
drop index test_b_idx;
ERROR:  cannot drop relation test_b_idx because other objects depend on it
DETAIL:  relation test_1_b_idx depends on relation test_b_idx
index test_1_1_b_idx depends on relation test_1_b_idx
index test_1_0_b_idx depends on relation test_1_b_idx
index test_0_b_idx depends on relation test_b_idx
HINT:  Use DROP ... CASCADE to drop the dependent objects too.
drop index test_0_b_idx;
select indexrelid::regclass, indrelid::regclass
	from pg_index where indrelid::regclass::text like 'test%'
	order by indexrelid::regclass::text;
   indexrelid   | indrelid 
----------------+----------
 test_1_0_b_idx | test_1_0
 test_1_1_b_idx | test_1_1
 test_1_b_idx   | test_1
 test_b_idx     | test
(4 rows)

select objid::regclass, refobjid::regclass from pg_depend
	where refobjid::regclass::text like 'test%_idx'
	order by objid::regclass::text;
     objid      |   refobjid   
----------------+--------------
 test_1_0_b_idx | test_1_b_idx
 test_1_1_b_idx | test_1_b_idx
 test_1_b_idx   | test_b_idx
(3 rows)

drop index test_b_idx cascade;
NOTICE:  drop cascades to 3 other objects
DETAIL:  drop cascades to relation test_1_b_idx
drop cascades to index test_1_1_b_idx
drop cascades to index test_1_0_b_idx
select indexrelid::regclass, indrelid::regclass
	from pg_index where indrelid::regclass::text like 'test%'
	order by indexrelid::regclass::text;
 indexrelid | indrelid 
------------+----------
(0 rows)

select objid::regclass, refobjid::regclass from pg_depend
	where refobjid::regclass::text like 'test%_idx'
	order by objid::regclass::text;
 objid | refobjid 
-------+----------
(0 rows)

-- Test creating of naming index --
create index local_idx_on_test on test (b);
select indexrelid::regclass, indrelid::regclass
	from pg_index where indrelid::regclass::text like 'test%'
	order by indexrelid::regclass::text;
    indexrelid     | indrelid 
-------------------+----------
 local_idx_on_test | test
 test_0_b_idx      | test_0
 test_1_0_b_idx    | test_1_0
 test_1_1_b_idx    | test_1_1
 test_1_b_idx      | test_1
(5 rows)

-- Test reindex --
reindex index local_idx_on_test;
reindex table test;
reindex schema public;
drop table test cascade;