Is it possible to list indexes created on materialized views in postgresql

Question

I am able to to list all indexes on tables by using pg_indexes. This however, does not list the indexes created on the materialized view. Neither does the pgadmin3 frontend show the indexes on materialized views.

Is there a way?

Answer 1

If I understand your question correctly (even though there's a lot of ambiguity), you can use the pg_indexes view to get information about indexes on materialized views. I used an example schema and query to verify that you can use to list all indexes on materialized views in the public schema:

-- Create a table
CREATE TABLE my_table (
    id SERIAL PRIMARY KEY,
    name TEXT NOT NULL,
    value NUMERIC NOT NULL
);

-- Insert some data into the table
INSERT INTO my_table (name, value)
VALUES ('A', 1), ('B', 2), ('C', 3);

-- Create a materialized view
CREATE MATERIALIZED VIEW my_view AS
SELECT name, SUM(value) as total_value
FROM my_table
GROUP BY name;

-- Create an index on the materialized view
CREATE INDEX my_view_name_idx ON my_view (name);

-- List indices
SELECT i.*
FROM pg_indexes i
JOIN pg_class c ON i.schemaname = c.relnamespace::regnamespace::text AND i.tablename = c.relname
WHERE c.relkind = 'm' AND i.schemaname = 'public';

This query joins the pg_indexes view with the pg_class table to filter out only materialized views (relkind = 'm'). You can modify the WHERE clause to filter the results based on your needs.

This was tested on PostgreSQL 14 (on Google Colab to be exact)

You can also refer to these for more insight

• Get constraints and indexes on materialized views
• Materialized Views and Indexes