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