SELECT TOP 1 queries hanging

TimescaleDB and PostgreSQL
,
1

I have a very simple query, along the lines of:

SELECT t.time
FROM trades AS t
WHERE t.tradable_asset_pair = 'ARPAUSD'
ORDER BY t.time DESC
LIMIT 1

An index exists for this query, although there are many millions of rows. But that was the entire point of introducing Timescale.

The EXPLAIN reveals that it is hitting over 200 indexes, although it does this with every query and mostly does not experience problems. However, after about a dozen or so of these queries, it just hangs. I have checked pg_locks and there is nothing suspicious in there, although I am about to enable deadlock logging to see if there is anything remiss going on.

Here is the output from EXPLAIN:

QUERY PLAN
Limit  (cost=0.42..0.53 rows=1 width=8)
  ->  Custom Scan (ChunkAppend) on trades t  (cost=0.42..82.70 rows=767 width=8)
"        Order: t.""time"" DESC"
        ->  Index Only Scan using _hyper_20_99_chunk_trades_tradable_asset_pair_time_index on _hyper_20_99_chunk t_93  (cost=0.42..2.28 rows=1 width=8)
        ->  Index Only Scan using _hyper_20_98_chunk_trades_tradable_asset_pair_time_index on _hyper_20_98_chunk t_94  (cost=0.42..2.21 rows=1 width=8)
        ->  Index Only Scan using _hyper_20_97_chunk_trades_tradable_asset_pair_time_index on _hyper_20_97_chunk t_95  (cost=0.42..2.64 rows=1 width=8)
        ->  Index Only Scan using _hyper_20_96_chunk_trades_tradable_asset_pair_time_index on _hyper_20_96_chunk t_96  (cost=0.42..2.16 rows=1 width=8)
        ->  Index Only Scan using _hyper_20_95_chunk_trades_tradable_asset_pair_time_index on _hyper_20_95_chunk t_97  (cost=0.42..2.64 rows=1 width=8)
        ->  Index Only Scan using _hyper_20_94_chunk_trades_tradable_asset_pair_time_index on _hyper_20_94_chunk t_98  (cost=0.42..2.64 rows=1 width=8)
        ->  Index Only Scan using _hyper_20_93_chunk_trades_tradable_asset_pair_time_index on _hyper_20_93_chunk t_99  (cost=0.42..2.64 rows=1 width=8)
        ->  Index Only Scan using _hyper_20_197_chunk_trades_tradable_asset_pair_time_index on _hyper_20_197_chunk t_100  (cost=0.42..2.09 rows=1 width=8)
        ->  Index Only Scan using _hyper_20_196_chunk_trades_tradable_asset_pair_time_index on _hyper_20_196_chunk t_101  (cost=0.42..2.08 rows=1 width=8)
        ->  Index Only Scan using _hyper_20_195_chunk_trades_tradable_asset_pair_time_index on _hyper_20_195_chunk t_102  (cost=0.42..2.64 rows=1 width=8)
        ->  Index Only Scan using _hyper_20_194_chunk_trades_tradable_asset_pair_time_index on _hyper_20_194_chunk t_103  (cost=0.42..2.13 rows=1 width=8)
        ->  Index Only Scan using _hyper_20_193_chunk_trades_tradable_asset_pair_time_index on _hyper_20_193_chunk t_104  (cost=0.42..2.64 rows=1 width=8)
        ->  Index Only Scan using _hyper_20_192_chunk_trades_tradable_asset_pair_time_index on _hyper_20_192_chunk t_105  (cost=0.42..2.07 rows=1 width=8)
        ->  Index Only Scan using _hyper_20_191_chunk_trades_tradable_asset_pair_time_index on _hyper_20_191_chunk t_1  (cost=0.42..82.70 rows=767 width=8)
        ->  Index Only Scan using _hyper_20_190_chunk_trades_tradable_asset_pair_time_index on _hyper_20_190_chunk t_2  (cost=0.42..71.98 rows=556 width=8)
        ->  Index Only Scan using _hyper_20_189_chunk_trades_tradable_asset_pair_time_index on _hyper_20_189_chunk t_3  (cost=0.42..225.01 rows=1664 width=8)
        ->  Index Only Scan using _hyper_20_188_chunk_trades_tradable_asset_pair_time_index on _hyper_20_188_chunk t_4  (cost=0.42..120.36 rows=817 width=8)
        ->  Index Only Scan using _hyper_20_187_chunk_trades_tradable_asset_pair_time_index on _hyper_20_187_chunk t_5  (cost=0.42..121.92 rows=3244 width=8)
        ->  Index Only Scan using _hyper_20_186_chunk_trades_tradable_asset_pair_time_index on _hyper_20_186_chunk t_6  (cost=0.42..197.54 rows=2896 width=8)
        ->  Index Only Scan using _hyper_20_185_chunk_trades_tradable_asset_pair_time_index on _hyper_20_185_chunk t_7  (cost=0.42..124.61 rows=1236 width=8)
        ->  Index Only Scan using _hyper_20_184_chunk_trades_tradable_asset_pair_time_index on _hyper_20_184_chunk t_8  (cost=0.42..63.90 rows=383 width=8)
        ->  Index Only Scan using _hyper_20_183_chunk_trades_tradable_asset_pair_time_index on _hyper_20_183_chunk t_9  (cost=0.42..8.26 rows=60 width=8)
        ->  Index Only Scan using _hyper_20_182_chunk_trades_tradable_asset_pair_time_index on _hyper_20_182_chunk t_10  (cost=0.42..16.66 rows=170 width=8)
        ->  Index Only Scan using _hyper_20_181_chunk_trades_tradable_asset_pair_time_index on _hyper_20_181_chunk t_11  (cost=0.42..168.60 rows=1401 width=8)
        ->  Index Only Scan using _hyper_20_180_chunk_trades_tradable_asset_pair_time_index on _hyper_20_180_chunk t_12  (cost=0.42..64.73 rows=790 width=8)
        ->  Index Only Scan using _hyper_20_179_chunk_trades_tradable_asset_pair_time_index on _hyper_20_179_chunk t_13  (cost=0.42..33.92 rows=220 width=8)
        ->  Index Only Scan using _hyper_20_178_chunk_trades_tradable_asset_pair_time_index on _hyper_20_178_chunk t_14  (cost=0.42..57.79 rows=352 width=8)
        ->  Index Only Scan using _hyper_20_177_chunk_trades_tradable_asset_pair_time_index on _hyper_20_177_chunk t_15  (cost=0.42..120.64 rows=3540 width=8)
        ->  Index Only Scan using _hyper_20_176_chunk_trades_tradable_asset_pair_time_index on _hyper_20_176_chunk t_16  (cost=0.42..207.53 rows=1391 width=8)
        ->  Index Only Scan using _hyper_20_175_chunk_trades_tradable_asset_pair_time_index on _hyper_20_175_chunk t_17  (cost=0.42..88.02 rows=595 width=8)
        ->  Index Only Scan using _hyper_20_174_chunk_trades_tradable_asset_pair_time_index on _hyper_20_174_chunk t_18  (cost=0.42..103.47 rows=696 width=8)
        ->  Index Only Scan using _hyper_20_173_chunk_trades_tradable_asset_pair_time_index on _hyper_20_173_chunk t_19  (cost=0.42..306.48 rows=2082 width=8)
        ->  Index Only Scan using _hyper_20_172_chunk_trades_tradable_asset_pair_time_index on _hyper_20_172_chunk t_20  (cost=0.42..123.61 rows=588 width=8)
        ->  Index Only Scan using _hyper_20_171_chunk_trades_tradable_asset_pair_time_index on _hyper_20_171_chunk t_21  (cost=0.42..34.36 rows=691 width=8)
        ->  Index Only Scan using _hyper_20_170_chunk_trades_tradable_asset_pair_time_index on _hyper_20_170_chunk t_22  (cost=0.42..5.57 rows=118 width=8)
        ->  Index Only Scan using _hyper_20_169_chunk_trades_tradable_asset_pair_time_index on _hyper_20_169_chunk t_23  (cost=0.42..30.65 rows=152 width=8)
        ->  Index Only Scan using _hyper_20_168_chunk_trades_tradable_asset_pair_time_index on _hyper_20_168_chunk t_24  (cost=0.42..4.90 rows=130 width=8)
        ->  Index Only Scan using _hyper_20_167_chunk_trades_tradable_asset_pair_time_index on _hyper_20_167_chunk t_25  (cost=0.42..24.04 rows=87 width=8)
        ->  Index Only Scan using _hyper_20_166_chunk_trades_tradable_asset_pair_time_index on _hyper_20_166_chunk t_26  (cost=0.42..10.76 rows=24 width=8)
        ->  Index Only Scan using _hyper_20_165_chunk_trades_tradable_asset_pair_time_index on _hyper_20_165_chunk t_27  (cost=0.41..7.99 rows=20 width=8)
        ->  Index Only Scan using _hyper_20_164_chunk_trades_tradable_asset_pair_time_index on _hyper_20_164_chunk t_28  (cost=0.42..19.58 rows=70 width=8)
        ->  Index Only Scan using _hyper_20_163_chunk_trades_tradable_asset_pair_time_index on _hyper_20_163_chunk t_29  (cost=0.42..15.32 rows=53 width=8)
        ->  Index Only Scan using _hyper_20_162_chunk_trades_tradable_asset_pair_time_index on _hyper_20_162_chunk t_30  (cost=0.42..19.20 rows=87 width=8)
        ->  Index Only Scan using _hyper_20_161_chunk_trades_tradable_asset_pair_time_index on _hyper_20_161_chunk t_31  (cost=0.42..2.57 rows=60 width=8)
        ->  Index Only Scan using _hyper_20_160_chunk_trades_tradable_asset_pair_time_index on _hyper_20_160_chunk t_32  (cost=0.42..5.50 rows=39 width=8)
        ->  Index Only Scan using _hyper_20_159_chunk_trades_tradable_asset_pair_time_index on _hyper_20_159_chunk t_33  (cost=0.42..4.72 rows=65 width=8)
        ->  Index Only Scan using _hyper_20_158_chunk_trades_tradable_asset_pair_time_index on _hyper_20_158_chunk t_34  (cost=0.42..8.85 rows=136 width=8)
        ->  Index Only Scan using _hyper_20_157_chunk_trades_tradable_asset_pair_time_index on _hyper_20_157_chunk t_35  (cost=0.42..12.54 rows=116 width=8)
        ->  Index Only Scan using _hyper_20_156_chunk_trades_tradable_asset_pair_time_index on _hyper_20_156_chunk t_36  (cost=0.42..8.68 rows=81 width=8)
        ->  Index Only Scan using _hyper_20_155_chunk_trades_tradable_asset_pair_time_index on _hyper_20_155_chunk t_37  (cost=0.42..30.83 rows=536 width=8)
        ->  Index Only Scan using _hyper_20_154_chunk_trades_tradable_asset_pair_time_index on _hyper_20_154_chunk t_38  (cost=0.42..4.42 rows=47 width=8)
        ->  Index Only Scan using _hyper_20_153_chunk_trades_tradable_asset_pair_time_index on _hyper_20_153_chunk t_39  (cost=0.42..8.68 rows=45 width=8)
        ->  Index Only Scan using _hyper_20_152_chunk_trades_tradable_asset_pair_time_index on _hyper_20_152_chunk t_40  (cost=0.42..10.71 rows=60 width=8)
        ->  Index Only Scan using _hyper_20_151_chunk_trades_tradable_asset_pair_time_index on _hyper_20_151_chunk t_41  (cost=0.42..9.36 rows=42 width=8)
        ->  Index Only Scan using _hyper_20_150_chunk_trades_tradable_asset_pair_time_index on _hyper_20_150_chunk t_42  (cost=0.42..18.32 rows=144 width=8)
        ->  Index Only Scan using _hyper_20_149_chunk_trades_tradable_asset_pair_time_index on _hyper_20_149_chunk t_43  (cost=0.42..16.92 rows=132 width=8)
        ->  Index Only Scan using _hyper_20_148_chunk_trades_tradable_asset_pair_time_index on _hyper_20_148_chunk t_44  (cost=0.42..21.92 rows=159 width=8)
        ->  Index Only Scan using _hyper_20_147_chunk_trades_tradable_asset_pair_time_index on _hyper_20_147_chunk t_45  (cost=0.42..50.94 rows=453 width=8)
        ->  Index Only Scan using _hyper_20_146_chunk_trades_tradable_asset_pair_time_index on _hyper_20_146_chunk t_46  (cost=0.42..21.92 rows=298 width=8)
        ->  Index Only Scan using _hyper_20_145_chunk_trades_tradable_asset_pair_time_index on _hyper_20_145_chunk t_47  (cost=0.42..46.45 rows=542 width=8)
        ->  Index Only Scan using _hyper_20_144_chunk_trades_tradable_asset_pair_time_index on _hyper_20_144_chunk t_48  (cost=0.42..81.55 rows=1308 width=8)
        ->  Index Only Scan using _hyper_20_143_chunk_trades_tradable_asset_pair_time_index on _hyper_20_143_chunk t_49  (cost=0.42..2.59 rows=1 width=8)
        ->  Index Only Scan using _hyper_20_142_chunk_trades_tradable_asset_pair_time_index on _hyper_20_142_chunk t_50  (cost=0.42..2.47 rows=1 width=8)
        ->  Index Only Scan using _hyper_20_141_chunk_trades_tradable_asset_pair_time_index on _hyper_20_141_chunk t_51  (cost=0.42..2.47 rows=1 width=8)
        ->  Index Only Scan using _hyper_20_140_chunk_trades_tradable_asset_pair_time_index on _hyper_20_140_chunk t_52  (cost=0.42..2.64 rows=1 width=8)
        ->  Index Only Scan using _hyper_20_139_chunk_trades_tradable_asset_pair_time_index on _hyper_20_139_chunk t_53  (cost=0.42..2.49 rows=1 width=8)
        ->  Index Only Scan using _hyper_20_138_chunk_trades_tradable_asset_pair_time_index on _hyper_20_138_chunk t_54  (cost=0.42..2.64 rows=1 width=8)
        ->  Index Only Scan using _hyper_20_137_chunk_trades_tradable_asset_pair_time_index on _hyper_20_137_chunk t_55  (cost=0.42..2.42 rows=1 width=8)
        ->  Index Only Scan using _hyper_20_136_chunk_trades_tradable_asset_pair_time_index on _hyper_20_136_chunk t_56  (cost=0.42..2.64 rows=1 width=8)
        ->  Index Only Scan using _hyper_20_135_chunk_trades_tradable_asset_pair_time_index on _hyper_20_135_chunk t_57  (cost=0.42..2.48 rows=1 width=8)
        ->  Index Only Scan using _hyper_20_134_chunk_trades_tradable_asset_pair_time_index on _hyper_20_134_chunk t_58  (cost=0.42..2.64 rows=1 width=8)
        ->  Index Only Scan using _hyper_20_133_chunk_trades_tradable_asset_pair_time_index on _hyper_20_133_chunk t_59  (cost=0.42..2.37 rows=1 width=8)
        ->  Index Only Scan using _hyper_20_132_chunk_trades_tradable_asset_pair_time_index on _hyper_20_132_chunk t_60  (cost=0.42..2.35 rows=1 width=8)
        ->  Index Only Scan using _hyper_20_131_chunk_trades_tradable_asset_pair_time_index on _hyper_20_131_chunk t_61  (cost=0.42..2.64 rows=1 width=8)
        ->  Index Only Scan using _hyper_20_130_chunk_trades_tradable_asset_pair_time_index on _hyper_20_130_chunk t_62  (cost=0.42..2.64 rows=1 width=8)
        ->  Index Only Scan using _hyper_20_129_chunk_trades_tradable_asset_pair_time_index on _hyper_20_129_chunk t_63  (cost=0.42..2.22 rows=1 width=8)
        ->  Index Only Scan using _hyper_20_128_chunk_trades_tradable_asset_pair_time_index on _hyper_20_128_chunk t_64  (cost=0.42..2.64 rows=1 width=8)
        ->  Index Only Scan using _hyper_20_127_chunk_trades_tradable_asset_pair_time_index on _hyper_20_127_chunk t_65  (cost=0.42..3.62 rows=10 width=8)
        ->  Index Only Scan using _hyper_20_126_chunk_trades_tradable_asset_pair_time_index on _hyper_20_126_chunk t_66  (cost=0.42..2.64 rows=1 width=8)
        ->  Index Only Scan using _hyper_20_125_chunk_trades_tradable_asset_pair_time_index on _hyper_20_125_chunk t_67  (cost=0.42..2.64 rows=1 width=8)
        ->  Index Only Scan using _hyper_20_124_chunk_trades_tradable_asset_pair_time_index on _hyper_20_124_chunk t_68  (cost=0.42..2.64 rows=1 width=8)
        ->  Index Only Scan using _hyper_20_123_chunk_trades_tradable_asset_pair_time_index on _hyper_20_123_chunk t_69  (cost=0.42..3.20 rows=33 width=8)
        ->  Index Only Scan using _hyper_20_122_chunk_trades_tradable_asset_pair_time_index on _hyper_20_122_chunk t_70  (cost=0.42..2.64 rows=1 width=8)
        ->  Index Only Scan using _hyper_20_121_chunk_trades_tradable_asset_pair_time_index on _hyper_20_121_chunk t_71  (cost=0.42..2.28 rows=1 width=8)
        ->  Index Only Scan using _hyper_20_120_chunk_trades_tradable_asset_pair_time_index on _hyper_20_120_chunk t_72  (cost=0.42..2.64 rows=1 width=8)
        ->  Index Only Scan using _hyper_20_119_chunk_trades_tradable_asset_pair_time_index on _hyper_20_119_chunk t_73  (cost=0.42..2.64 rows=1 width=8)
        ->  Index Only Scan using _hyper_20_118_chunk_trades_tradable_asset_pair_time_index on _hyper_20_118_chunk t_74  (cost=0.42..2.64 rows=1 width=8)
        ->  Index Only Scan using _hyper_20_117_chunk_trades_tradable_asset_pair_time_index on _hyper_20_117_chunk t_75  (cost=0.42..2.64 rows=1 width=8)
        ->  Index Only Scan using _hyper_20_116_chunk_trades_tradable_asset_pair_time_index on _hyper_20_116_chunk t_76  (cost=0.42..2.64 rows=1 width=8)
        ->  Index Only Scan using _hyper_20_115_chunk_trades_tradable_asset_pair_time_index on _hyper_20_115_chunk t_77  (cost=0.42..2.64 rows=1 width=8)
        ->  Index Only Scan using _hyper_20_114_chunk_trades_tradable_asset_pair_time_index on _hyper_20_114_chunk t_78  (cost=0.42..2.64 rows=1 width=8)
        ->  Index Only Scan using _hyper_20_113_chunk_trades_tradable_asset_pair_time_index on _hyper_20_113_chunk t_79  (cost=0.42..2.22 rows=1 width=8)
        ->  Index Only Scan using _hyper_20_112_chunk_trades_tradable_asset_pair_time_index on _hyper_20_112_chunk t_80  (cost=0.42..2.22 rows=1 width=8)
        ->  Index Only Scan using _hyper_20_111_chunk_trades_tradable_asset_pair_time_index on _hyper_20_111_chunk t_81  (cost=0.42..2.18 rows=1 width=8)
        ->  Index Only Scan using _hyper_20_110_chunk_trades_tradable_asset_pair_time_index on _hyper_20_110_chunk t_82  (cost=0.42..2.64 rows=1 width=8)
        ->  Index Only Scan using _hyper_20_109_chunk_trades_tradable_asset_pair_time_index on _hyper_20_109_chunk t_83  (cost=0.42..2.23 rows=1 width=8)
        ->  Index Only Scan using _hyper_20_108_chunk_trades_tradable_asset_pair_time_index on _hyper_20_108_chunk t_84  (cost=0.42..2.25 rows=1 width=8)
        ->  Index Only Scan using _hyper_20_107_chunk_trades_tradable_asset_pair_time_index on _hyper_20_107_chunk t_85  (cost=0.42..2.17 rows=1 width=8)
        ->  Index Only Scan using _hyper_20_106_chunk_trades_tradable_asset_pair_time_index on _hyper_20_106_chunk t_86  (cost=0.42..2.23 rows=1 width=8)
        ->  Index Only Scan using _hyper_20_105_chunk_trades_tradable_asset_pair_time_index on _hyper_20_105_chunk t_87  (cost=0.42..2.64 rows=1 width=8)
        ->  Index Only Scan using _hyper_20_104_chunk_trades_tradable_asset_pair_time_index on _hyper_20_104_chunk t_88  (cost=0.42..2.64 rows=1 width=8)
        ->  Index Only Scan using _hyper_20_103_chunk_trades_tradable_asset_pair_time_index on _hyper_20_103_chunk t_89  (cost=0.42..2.24 rows=1 width=8)
        ->  Index Only Scan using _hyper_20_102_chunk_trades_tradable_asset_pair_time_index on _hyper_20_102_chunk t_90  (cost=0.42..2.33 rows=1 width=8)
        ->  Index Only Scan using _hyper_20_101_chunk_trades_tradable_asset_pair_time_index on _hyper_20_101_chunk t_91  (cost=0.42..2.64 rows=1 width=8)
        ->  Index Only Scan using _hyper_20_100_chunk_trades_tradable_asset_pair_time_index on _hyper_20_100_chunk t_92  (cost=0.42..2.32 rows=1 width=8)
              Index Cond: (tradable_asset_pair = 'ARPAUSD'::text)
              Index Cond: (tradable_asset_pair = 'ARPAUSD'::text)
              Index Cond: (tradable_asset_pair = 'ARPAUSD'::text)
              Index Cond: (tradable_asset_pair = 'ARPAUSD'::text)
              Index Cond: (tradable_asset_pair = 'ARPAUSD'::text)
              Index Cond: (tradable_asset_pair = 'ARPAUSD'::text)
              Index Cond: (tradable_asset_pair = 'ARPAUSD'::text)
              Index Cond: (tradable_asset_pair = 'ARPAUSD'::text)
              Index Cond: (tradable_asset_pair = 'ARPAUSD'::text)
              Index Cond: (tradable_asset_pair = 'ARPAUSD'::text)
              Index Cond: (tradable_asset_pair = 'ARPAUSD'::text)
              Index Cond: (tradable_asset_pair = 'ARPAUSD'::text)
              Index Cond: (tradable_asset_pair = 'ARPAUSD'::text)
              Index Cond: (tradable_asset_pair = 'ARPAUSD'::text)
              Index Cond: (tradable_asset_pair = 'ARPAUSD'::text)
              Index Cond: (tradable_asset_pair = 'ARPAUSD'::text)
              Index Cond: (tradable_asset_pair = 'ARPAUSD'::text)
              Index Cond: (tradable_asset_pair = 'ARPAUSD'::text)
              Index Cond: (tradable_asset_pair = 'ARPAUSD'::text)
              Index Cond: (tradable_asset_pair = 'ARPAUSD'::text)
              Index Cond: (tradable_asset_pair = 'ARPAUSD'::text)
              Index Cond: (tradable_asset_pair = 'ARPAUSD'::text)
              Index Cond: (tradable_asset_pair = 'ARPAUSD'::text)
              Index Cond: (tradable_asset_pair = 'ARPAUSD'::text)
              Index Cond: (tradable_asset_pair = 'ARPAUSD'::text)
              Index Cond: (tradable_asset_pair = 'ARPAUSD'::text)
              Index Cond: (tradable_asset_pair = 'ARPAUSD'::text)
              Index Cond: (tradable_asset_pair = 'ARPAUSD'::text)
              Index Cond: (tradable_asset_pair = 'ARPAUSD'::text)
              Index Cond: (tradable_asset_pair = 'ARPAUSD'::text)
              Index Cond: (tradable_asset_pair = 'ARPAUSD'::text)
              Index Cond: (tradable_asset_pair = 'ARPAUSD'::text)
              Index Cond: (tradable_asset_pair = 'ARPAUSD'::text)
              Index Cond: (tradable_asset_pair = 'ARPAUSD'::text)
              Index Cond: (tradable_asset_pair = 'ARPAUSD'::text)
              Index Cond: (tradable_asset_pair = 'ARPAUSD'::text)
              Index Cond: (tradable_asset_pair = 'ARPAUSD'::text)
              Index Cond: (tradable_asset_pair = 'ARPAUSD'::text)
              Index Cond: (tradable_asset_pair = 'ARPAUSD'::text)
              Index Cond: (tradable_asset_pair = 'ARPAUSD'::text)
              Index Cond: (tradable_asset_pair = 'ARPAUSD'::text)
              Index Cond: (tradable_asset_pair = 'ARPAUSD'::text)
              Index Cond: (tradable_asset_pair = 'ARPAUSD'::text)
              Index Cond: (tradable_asset_pair = 'ARPAUSD'::text)
              Index Cond: (tradable_asset_pair = 'ARPAUSD'::text)
              Index Cond: (tradable_asset_pair = 'ARPAUSD'::text)
              Index Cond: (tradable_asset_pair = 'ARPAUSD'::text)
              Index Cond: (tradable_asset_pair = 'ARPAUSD'::text)
              Index Cond: (tradable_asset_pair = 'ARPAUSD'::text)
              Index Cond: (tradable_asset_pair = 'ARPAUSD'::text)
              Index Cond: (tradable_asset_pair = 'ARPAUSD'::text)
              Index Cond: (tradable_asset_pair = 'ARPAUSD'::text)
              Index Cond: (tradable_asset_pair = 'ARPAUSD'::text)
              Index Cond: (tradable_asset_pair = 'ARPAUSD'::text)
              Index Cond: (tradable_asset_pair = 'ARPAUSD'::text)
              Index Cond: (tradable_asset_pair = 'ARPAUSD'::text)
              Index Cond: (tradable_asset_pair = 'ARPAUSD'::text)
              Index Cond: (tradable_asset_pair = 'ARPAUSD'::text)
              Index Cond: (tradable_asset_pair = 'ARPAUSD'::text)
              Index Cond: (tradable_asset_pair = 'ARPAUSD'::text)
              Index Cond: (tradable_asset_pair = 'ARPAUSD'::text)
              Index Cond: (tradable_asset_pair = 'ARPAUSD'::text)
              Index Cond: (tradable_asset_pair = 'ARPAUSD'::text)
              Index Cond: (tradable_asset_pair = 'ARPAUSD'::text)
              Index Cond: (tradable_asset_pair = 'ARPAUSD'::text)
              Index Cond: (tradable_asset_pair = 'ARPAUSD'::text)
              Index Cond: (tradable_asset_pair = 'ARPAUSD'::text)
              Index Cond: (tradable_asset_pair = 'ARPAUSD'::text)
              Index Cond: (tradable_asset_pair = 'ARPAUSD'::text)
              Index Cond: (tradable_asset_pair = 'ARPAUSD'::text)
              Index Cond: (tradable_asset_pair = 'ARPAUSD'::text)
              Index Cond: (tradable_asset_pair = 'ARPAUSD'::text)
              Index Cond: (tradable_asset_pair = 'ARPAUSD'::text)
              Index Cond: (tradable_asset_pair = 'ARPAUSD'::text)
              Index Cond: (tradable_asset_pair = 'ARPAUSD'::text)
              Index Cond: (tradable_asset_pair = 'ARPAUSD'::text)
              Index Cond: (tradable_asset_pair = 'ARPAUSD'::text)
              Index Cond: (tradable_asset_pair = 'ARPAUSD'::text)
              Index Cond: (tradable_asset_pair = 'ARPAUSD'::text)
              Index Cond: (tradable_asset_pair = 'ARPAUSD'::text)
              Index Cond: (tradable_asset_pair = 'ARPAUSD'::text)
              Index Cond: (tradable_asset_pair = 'ARPAUSD'::text)
              Index Cond: (tradable_asset_pair = 'ARPAUSD'::text)
              Index Cond: (tradable_asset_pair = 'ARPAUSD'::text)
              Index Cond: (tradable_asset_pair = 'ARPAUSD'::text)
              Index Cond: (tradable_asset_pair = 'ARPAUSD'::text)
              Index Cond: (tradable_asset_pair = 'ARPAUSD'::text)
              Index Cond: (tradable_asset_pair = 'ARPAUSD'::text)
              Index Cond: (tradable_asset_pair = 'ARPAUSD'::text)
              Index Cond: (tradable_asset_pair = 'ARPAUSD'::text)
              Index Cond: (tradable_asset_pair = 'ARPAUSD'::text)
              Index Cond: (tradable_asset_pair = 'ARPAUSD'::text)
              Index Cond: (tradable_asset_pair = 'ARPAUSD'::text)
              Index Cond: (tradable_asset_pair = 'ARPAUSD'::text)
              Index Cond: (tradable_asset_pair = 'ARPAUSD'::text)
              Index Cond: (tradable_asset_pair = 'ARPAUSD'::text)
              Index Cond: (tradable_asset_pair = 'ARPAUSD'::text)
              Index Cond: (tradable_asset_pair = 'ARPAUSD'::text)
              Index Cond: (tradable_asset_pair = 'ARPAUSD'::text)
              Index Cond: (tradable_asset_pair = 'ARPAUSD'::text)
              Index Cond: (tradable_asset_pair = 'ARPAUSD'::text)
              Index Cond: (tradable_asset_pair = 'ARPAUSD'::text)
              Index Cond: (tradable_asset_pair = 'ARPAUSD'::text)
              Index Cond: (tradable_asset_pair = 'ARPAUSD'::text)
              Index Cond: (tradable_asset_pair = 'ARPAUSD'::text)

Any suggestions? The performance is now much worse than it was before introducing TimescaleDB.

As I mentioned, these queries often return quite quickly, milliseconds, but then they just hang.

2

Hello Mark!

You need to be careful with WHERE clauses without the time series limit. If you’re looking to real time data, use now() as a reference.

WHERE t.time >= now() - INTERVAL '1 hour'
AND ...

Then it will select only the last chunk.

Also check this very useful tips: 5 ways to efficiently query "last point" data in PostgreSQL and TimescaleDB Hypertables

3

Hi jonatasdp :wave:t2:

Thanks for your response.

I cannot know the time of the last trade though. It might many months ago, or am I missing the point? If the last trade is not in the specified interval, it will return nothing.

In any event, I think the problem was an imperative coding problem. I had neglected to await an insertion operation, producing some sort of lock or other, not sure. It seems to be working now though; although insertions gradually slow down over time.

I am going to investigate this new issue, but the hanging is gone!

4

Hi Mark :wave:

In this case, you can use a continuous aggregates with last date just to make a cache of the last date/price. I’d use something like daily cache just to make sure you can join such information to query only the right chunks.