ANALYZE command on index returns warning in PostgreSQL

user_8597613 0
Viewed 620

After reading through documentation I found that Analyze is to collect information about the given table which will be used by the planner at a later point to plan the execution of a query. In Oracle, there is an option to analyze the indexes. In the case of PostgreSQL, ANALYZE command returns warning for the index. I think index should be equally important to PostgreSQL so why it is not supported in PostgreSQL. I might have missed something, Can someone point to the right documentation with which I can get more understanding about this?

postgres=# Create table test(empno integer);
CREATE TABLE
postgres=# create index idx_test1 on test(empno );
CREATE INDEX
postgres=# \di idx_test1
                 List of relations
 Schema |   Name    | Type  |  Owner  | Table 
--------+-----------+-------+---------+-------
 public | idx_test1 | index |   Xyz   | test
(1 row)

postgres=# analyze public.idx_test1
postgres-# ;
2020-06-29 16:37:30.665 IST [118434] WARNING:  skipping "idx_test1" --- cannot analyze non-tables or special system tables
WARNING:  skipping "idx_test1" --- cannot analyze non-tables or special system tables
ANALYZE
postgres=#
postgresql sql
2 Answers

ANALYZE collects statistics about the data in the table (e.g number of distinct values, highest value, lowest value and so on).

Quote from the manual

The statistics collected by ANALYZE usually include a list of some of the most common values in each column and a histogram showing the approximate data distribution in each column.

An index essentially stores a copy of the data stored in the table (but in an ordered fashion, suitable for a quick lookup).

Analyzing the index would thus not yield any additional information compared to analyzing the table. The only exception would be expression based indexes (as the expressions value is not directly stored in the table), for which this might make sense. However that isn't implemented as far as I know

user_3303150

ANALYZE will accept any object that is stored in the pg_class system catalog, that is (partitioned or non-partitioned) tables, (partitioned or non-partitioned) indexes, foreign tables, sequences, TOAST tables, views and materialized views.

However, it will only process partitioned or non-partitioned tables and materialized views. These are the only objects for which it makes sense to collect statistics. For all other object types you will get a warning similar to the one you get, and the object will be skipped.

To collect statistics for an indexed column or expression, ANALYZE the table on which the index is defined.

user_64643080
Related