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;
select objid::regclass, refobjid::regclass from pg_depend
	where refobjid::regclass::text like 'test%_idx'
	order by objid::regclass::text;

-- 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;

-- Test recursive index dropping --
drop index test_b_idx;
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;
select objid::regclass, refobjid::regclass from pg_depend
	where refobjid::regclass::text like 'test%_idx'
	order by objid::regclass::text;
drop index test_b_idx cascade;
select indexrelid::regclass, indrelid::regclass
	from pg_index where indrelid::regclass::text like 'test%'
	order by indexrelid::regclass::text;
select objid::regclass, refobjid::regclass from pg_depend
	where refobjid::regclass::text like 'test%_idx'
	order by objid::regclass::text;

-- 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;

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

drop table test cascade;