[Bug]: Slow drop index on hypertable

[anayrat]

What type of bug is this?

Performance issue

What subsystems and features are affected?

Command processing

What happened?

Hello,
We have a big database with many hypertables (200K). We noticed dropping an index is slow due to event trigger processing, around 1s/index.
It seems timescaledb scan several times the catalog in the event trigger.

We have many indexes to remove (more than 100K).
Is there any way to avoid this overhead?
Thanks

TimescaleDB version affected

2.17.1

PostgreSQL version used

14.15

What operating system did you use?

Red Hat Enterprise Linux release 9.5 (Plow)

What installation method did you use?

Source

What platform did you run on?

Amazon Web Services (AWS)

Relevant log output and stack trace

Here is a perf top -p -g of the relevant backend :

-   38.52%     0.75%  timescaledb-2.17.1.so                             [.] ts_scanner_next                                                                                                                      ◆
   - 37.64% ts_scanner_next                                                                                                                                                                                      ▒
        28.46% 0x7f45b2d6fb82                                                                                                                                                                                    ▒
        4.31% __strncmp_evex                                                                                                                                                                                     ▒
        1.24% namestrcmp@plt                                                                                                                                                                                     ▒
-   32.87%    32.75%  libc.so.6                                         [.] __strncmp_evex                                                                                                                       ▒
     32.75% 0x7f4500000005                                                                                                                                                                                       ▒
        0x664553                                                                                                                                                                                                 ▒
        ts_timescaledb_process_ddl_event                                                                                                                                                                         ▒
        ts_chunk_index_delete_by_name                                                                                                                                                                            ▒
        ts_scanner_scan                                                                                                                                                                                          ▒
        ts_scanner_next                                                                                                                                                                                          ▒
+   11.08%    11.03%  postgres                                          [.] LWLockRelease                                                                                                                        ▒
+   10.11%    10.11%  postgres                                          [.] 0x000000000045b096                                                                                                                   ▒
+    8.42%     8.40%  postgres                                          [.] hash_search_with_hash_value                                                                                                          ▒
+    8.23%     8.19%  postgres                                          [.] HeapTupleSatisfiesVisibility                                                                                                         ▒
+    4.66%     0.00%  [unknown]                                         [.] 0x00007f4500000005                                                                                                                   ▒
+    4.66%     0.00%  timescaledb-2.17.1.so                             [.] ts_scanner_scan                                                                                                                      ▒
+    4.66%     0.00%  postgres                                          [.] 0x0000000000664553                                                                                                                   ▒
+    4.66%     0.00%  timescaledb-2.17.1.so                             [.] ts_timescaledb_process_ddl_event                                                                                                     ▒
+    4.66%     0.00%  timescaledb-2.17.1.so                             [.] ts_chunk_index_delete_by_name                                                                                                        ▒
+    3.10%     3.09%  postgres                                          [.] LWLockAcquire                                                                                                                        ▒
+    2.10%     2.09%  timescaledb-2.17.1.so                             [.] chunk_index_name_and_schema_filter                                                                                                   ▒
     1.99%     1.99%  postgres                                          [.] 0x0000000000157388                                                                                                                   ▒
+    1.75%     0.00%  [unknown]                                         [.] 0000000000000000                                                                                                                     ▒
+    1.72%     1.72%  postgres                                          [.] ExecStoreBufferHeapTuple                                                                                                             ▒
+    1.68%     1.68%  postgres                                          [.] heap_getnextslot                                                                                                                     ▒
+    1.24%     1.24%  timescaledb-2.17.1.so                             [.] namestrcmp@plt                                                                                                                       ▒
+    1.20%     0.00%  postgres                                          [.] 0x000000000085b096                                                                                                                   ▒
+    1.18%     1.17%  timescaledb-2.17.1.so                             [.] table_scanner_getnext

How can we reproduce the bug?

Create many hyper tables (we have 200K) and drop a index from an hypertable.

[mkindahl]

@anayrat This is unfortunately a known limitation and there are a few things that can be done internally to improve performance when you have a lot of hypertables and/or hypertables with a lot of chunks. It is, unfortunately, a larger refactoring.

Thank you for the bug report though. We see more and more users that have large installations, so it might be something to start look into.