Re: Questions about Partitioned Tables and Indexes

Hi,

what you proposed seems correct to me.
I don't know how to list indexes from a partitionned index.

You can check if your indexes are valid using:

select i.relname as indexname,i.relkind, t.relname as tablename,
t.relkind, idx.indisvalid
from pg_class i
join pg_index idx on idx.indexrelid = i.oid
join pg_class t on t.oid = idx.indrelid
where t.relname like 'test%'

to be really sure, try EXPLAIN with statements that should use those indexes
like

postgres=# explain select * from test_part where date_key >0;
QUERY PLAN
---------------------------------------------------------------------------------------
Append (cost=6.53..43.80 rows=614 width=60)
-> Bitmap Heap Scan on test_201908 (cost=6.53..20.37 rows=307 width=60)
Recheck Cond: (date_key > '0'::numeric)
-> Bitmap Index Scan on test_201908_pkey (cost=0.00..6.45
rows=307 width=0)
Index Cond: (date_key > '0'::numeric)
-> Bitmap Heap Scan on test_201909 (cost=6.53..20.37 rows=307 width=60)
Recheck Cond: (date_key > '0'::numeric)
-> Bitmap Index Scan on test_201909_pkey (cost=0.00..6.45
rows=307 width=0)
Index Cond: (date_key > '0'::numeric)
(9 rows)

or

postgres=# explain select * from test_part where metric >0;
QUERY PLAN
---------------------------------------------------------------------------------------------
Append (cost=6.53..43.80 rows=614 width=60)
-> Bitmap Heap Scan on test_201908 (cost=6.53..20.37 rows=307 width=60)
Recheck Cond: (metric > '0'::numeric)
-> Bitmap Index Scan on test_idx1_201908 (cost=0.00..6.45
rows=307 width=0)
Index Cond: (metric > '0'::numeric)
-> Bitmap Heap Scan on test_201909 (cost=6.53..20.37 rows=307 width=60)
Recheck Cond: (metric > '0'::numeric)
-> Bitmap Index Scan on test_201909_metric_idx (cost=0.00..6.45
rows=307 width=0)
Index Cond: (metric > '0'::numeric)
(9 rows)

Regards
PAscal

--
Sent from: https://www.postgresql-archive.org/PostgreSQL-general-f1843780.html