0

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?

anil
  • 598
  • 1
  • 6
  • 20
  • What version of PostgreSQL are you using? I assume it is an old version since you are using pgAdmin3. With PostgreSQL v15.3 and pgAdmin4 v7.4, materialized view indexes are visible in the Object Explorer panel. – JohnH Aug 12 '23 at 17:25

1 Answers1

0

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) enter image description here

You can also refer to these for more insight

PGzlan
  • 81
  • 4
  • Thanks. Maybe this is a version support issue. I am using 9.4 and it doesnt work on that. gives me the following issue : ERROR: type "regnamespace" does not exist LINE 3: JOIN pg_class c ON i.schemaname = c.relnamespace::regnamespa... – anil Aug 18 '23 at 09:58
  • The pg_indexes doesnt show it - I had taken a select * and manually searched the output, before putting the query. – anil Aug 18 '23 at 10:06