0

I have a append only table with more than ~80M records attached to timescaledb, the frequency of insertion of records to table is one minute. Also, there is an index created on non unique column and start time (ds_id, start_time). When I try to run the simple

select * from observation where ds_id in (27525, 27567, 28787,27099)

The query itself is taking longer than 1 minute to give the output.

I, also tried to analyze the table, as it is append only there is no scope of vacuum on this table.

So, I am in confusion why the simple select query is taking much time. I am thinking due to huge number of records it is taking time to query the results.

Please help me in understanding the issue and help me with fixing

query plan: https://explain.depesz.com/s/M8H7

Thanks in advance.

Note: ds_id (fk) and start_time(insertion time) are the one used for getting results. Also, I am sorry for not providing the table structure and details as it is confidential. :(

eshirvana
  • 23,227
  • 3
  • 22
  • 38
chintuyadavsara
  • 1,509
  • 1
  • 12
  • 23
  • 2
    Please **[edit]** your question and add the [execution plan](https://www.postgresql.org/docs/current/static/using-explain.html) generated using **`explain (analyze, buffers, format text)`** (_not_ just a "simple" explain) as [formatted text](http://stackoverflow.com/help/formatting) and make sure you preserve the indention of the plan. Paste the text, then put `\`\`\`` on the line before the plan and on a line after the plan. Please also include complete `create index` statements for all indexes as well. –  May 19 '21 at 05:59
  • @a_horse_with_no_name sure, will do it now Thanks – chintuyadavsara May 19 '21 at 06:00
  • what do yo get for the plan after doing `set enable_seqscan=off` ? – jjanes May 19 '21 at 15:13
  • Enabling compression (https://docs.timescale.com/timescaledb/latest/how-to-guides/compression/) with a segment by of the ds_id will probably help this as well. Though why it's not doing an index scan is a bit confusing. Doing a reorder ( https://docs.timescale.com/api/latest/hypertable/reorder_chunk/#required-arguments) would help if you don't want to do compression for some reason... – davidk May 19 '21 at 16:46

0 Answers0