Re: Proposal to Enable/Disable Index using ALTER INDEX (with patch) - Mailing list pgsql-hackers

> Rebased with the latest master as well.

Hi,

This is a great, long needed feature. Thanks for doing this.

I am late to this thread, but I took a look at the current patch
and have some comments as I continue to look.

1/
instead of

+       If true, the index is currently enabled and should be used for queries.
+       If false, the index is disabled and should not be used for queries,

how about?

"If true, the index is currently enabled and may be used for queries.
If false, the index is disabled and may not be used for queries,"

"may" is more accurate than "should" in this context.

2/
instead of

+       but is still maintained when the table is modified. Default is true.

how about?

"but is still updated when the table is modified. Default is true."

"update" of an index is the current verb used. See bottom of
https://www.postgresql.org/docs/current/indexes-intro.html

3/
instead of saying "used by the query planner for query optimization",
can it just be "The index will be used for queries."

+     <para>
+      Enable the specified index. The index will be used by the query planner
+      for query optimization. This is the default state for newly
created indexes.
+     </para>

Same for

+    <listitem>
+     <para>
+      Disable the specified index. A disabled index is not used by
the query planner
+      for query optimization, but it is still maintained when the
underlying table
+      data changes and will still be used to enforce constraints
(such as UNIQUE,
+      or PRIMARY KEY constraints).

4/ Should documentation recommend a direct catalog update?

+      to identify potentially unused indexes. Note that if you want
to completely
+      prevent an index from being used, including for constraint
enforcement, you
+      would need to mark it as invalid using a direct update to the
system catalogs
+      (e.g., <literal>UPDATE pg_index SET indisvalid = false WHERE
indexrelid = 'index_name'::regclass</literal>).

"indisvalid" does not control constraint enforcement in this case. It will be
"indisready" being set to false that will.

But even then, this goes against the general principle ( also documnted )
of not updating the catalog directly. See [1]

I think this part should be removed.

5/

In a case of a prepared statement, disabling the index
has no effect.

postgres=# create table foo ( id int primary key );
CREATE TABLE
postgres=# prepare prp as select * from foo where id = 1;
PREPARE
postgres=# explain analyze execute prp;
                                                    QUERY PLAN
-------------------------------------------------------------------------------------------------------------------
 Index Only Scan using foo_pkey on foo  (cost=0.15..8.17 rows=1
width=4) (actual time=0.018..0.019 rows=0 loops=1)
   Index Cond: (id = 1)
   Heap Fetches: 0
   Buffers: shared hit=2
 Planning:
   Buffers: shared hit=15 read=7
 Planning Time: 2.048 ms
 Execution Time: 0.071 ms
(8 rows)

postgres=# alter index foo_pkey disable ;
ALTER INDEX
postgres=# explain analyze execute prp;
                                                    QUERY PLAN
-------------------------------------------------------------------------------------------------------------------
 Index Only Scan using foo_pkey on foo  (cost=0.15..8.17 rows=1
width=4) (actual time=0.035..0.036 rows=0 loops=1)
   Index Cond: (id = 1)
   Heap Fetches: 0
   Buffers: shared hit=2
 Planning Time: 0.012 ms
 Execution Time: 0.320 ms
(6 rows)

Should this not behave like if you drop (or create) an index
during a prepared statement? I have not yet looked closely at
this code to see what could be done.

Regards,

Sami Imseih
Amazon Web Services (AWS)


[1] https://www.postgresql.org/docs/current/catalogs.html