Get index name of a continuous aggregate table

i suddenly create a wrong index i need to drop. how get its name?

Hi @sarv_sarv !

I’m not sure what tools you’re using but here is some query that can help you. First, find the name of your continuous aggregates as it’s a hypertable:

select materialization_hypertable_schema || '.' || materialization_hypertable_name
 from  timescaledb_information.continuous_aggregates
where view_name = '<your continuous aggregates name>';

Example from a random database here:

select materialization_hypertable_schema || '.' || materialization_hypertable_name
 from  timescaledb_information.continuous_aggregates
where view_name = 'one_day_candle';
-- _timescaledb_internal._materialized_hypertable_11

Then I can use the output to build my next query:

\d+ _timescaledb_internal._materialized_hypertable_11
                           Table "_timescaledb_internal._materialized_hypertable_11"
β”‚  Column  β”‚           Type           β”‚ Collation β”‚ Nullable β”‚ Default β”‚ Storage  β”‚ Stats target β”‚ Description β”‚
β”‚ bucket   β”‚ timestamp with time zone β”‚           β”‚ not null β”‚         β”‚ plain    β”‚              β”‚             β”‚
β”‚ symbol   β”‚ text                     β”‚           β”‚          β”‚         β”‚ extended β”‚              β”‚             β”‚
    "_materialized_hypertable_11_bucket_idx" btree (bucket DESC)
    "_materialized_hypertable_11_symbol_bucket_idx" btree (symbol, bucket DESC)

As you can see the index name in the bottom, then you can drop it.

If you want a query to run it you can get it using a query to get the oid:

SELECT c.oid,
FROM pg_catalog.pg_class c
     LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE c.relname OPERATOR(pg_catalog.~) '^(_materialized_hypertable_11)$' COLLATE pg_catalog.default
  AND n.nspname OPERATOR(pg_catalog.~) '^(_timescaledb_internal)$' COLLATE pg_catalog.default
ORDER BY 2, 3;

And a query to get the index:

SELECT c2.relname, i.indisprimary, i.indisunique, i.indisclustered, i.indisvalid, pg_catalog.pg_get_indexdef(i.indexrelid, 0, true),
  pg_catalog.pg_get_constraintdef(con.oid, true), contype, condeferrable, condeferred, i.indisreplident, c2.reltablespace
FROM pg_catalog.pg_class c, pg_catalog.pg_class c2, pg_catalog.pg_index i
  LEFT JOIN pg_catalog.pg_constraint con ON (conrelid = i.indrelid AND conindid = i.indexrelid AND contype IN ('p','u','x'))
WHERE c.oid = '34562' AND c.oid = i.indrelid AND i.indexrelid = c2.oid
ORDER BY i.indisprimary DESC, c2.relname;

hi @jonatasdp thank you for answer. its exactly what im looking for.

1 Like