0
  • PostgreSQL version: 12.4
  • TimescaleDB version: 1.7.4

Hi,

I have a table in which data is distributed in multiple chunks based on time only i.e. created hypertables for one day. I am trying to improve the performance of SELECT query. To achieve that I had to run ANALYZE periodically over the whole table. Since there isn't any updation or deletion performed in the table, VACUUM seems inefficient in my case. Also, I cannot put multiple indexes either since it will decrease ingestion performance and it will take a large amount of disk space.

I am ingesting data into the table by order of time. The recent data will be stored in a successive hypertable.

Question:

Will I get any performance improvement if I run ANALYZE on only the newly created hypertable by assuming that I already ran ANALYZE on all previous hypertables?

ANALYZE _timescaledb_internal._hyper_103_45_chunk;

Or is it mandatory to run ANALYZE on the whole table to get best performance and why?

ANALYZE schema.table_name;

Thanks

Satish Kumar
  • 63
  • 6
  • 1
    @JSpratt No. `ANALYZE` does *not* view an execution plan. It collects statistics about the data distribution. – Laurenz Albe Feb 05 '21 at 08:58
  • `ANALYZE` is started automatically by autovacuum, even if you only `INSERT` to a table. Distribution statistics are collected per partition, so there is no need to `ANALYZE` the whole partitioned table. You should provide more data, like the `EXPLAIN (ANALYZE, BUFFERS)` output for the slow and the fast query. – Laurenz Albe Feb 05 '21 at 09:00
  • Changes done in posgresql.conf: wal_level = minimal fsync = off synchronous_commit = off full_page_writes = off archive_mode = off Also each table has autovacuum_enabled = false. – Satish Kumar Feb 08 '21 at 06:12
  • Then you cannot get good performance. Plus, you flipped every possible switch to break your database. Except for `zero_damaged_pages`. – Laurenz Albe Feb 08 '21 at 07:25

0 Answers0