Performance Slowdown With One Additional Column #2359

Activity

1. EmmettM commented on Jul 28, 2022

   

   EmmettM

   on Jul 28, 2022

   Last edited by EmmettM

   Author

   More actions

   Table Schema:

   {'column': 'ts', 'type': 'TIMESTAMP', 'indexed': False, 'indexBlockCapacity': 0, 'symbolCached': False, 'symbolCapacity': 0, 'designated': True}
   {'column': 'pair', 'type': 'SYMBOL', 'indexed': True, 'indexBlockCapacity': 0, 'symbolCached': True, 'symbolCapacity': 32, 'designated': False}
   {'column': 'exchange', 'type': 'SYMBOL', 'indexed': True, 'indexBlockCapacity': 0, 'symbolCached': True, 'symbolCapacity': 32, 'designated': False}
   {'column': 'open', 'type': 'DOUBLE', 'indexed': False, 'indexBlockCapacity': 0, 'symbolCached': False, 'symbolCapacity': 0, 'designated': False}
   {'column': 'high', 'type': 'DOUBLE', 'indexed': False, 'indexBlockCapacity': 0, 'symbolCached': False, 'symbolCapacity': 0, 'designated': False}
   {'column': 'low', 'type': 'DOUBLE', 'indexed': False, 'indexBlockCapacity': 0, 'symbolCached': False, 'symbolCapacity': 0, 'designated': False}
   {'column': 'close', 'type': 'DOUBLE', 'indexed': False, 'indexBlockCapacity': 0, 'symbolCached': False, 'symbolCapacity': 0, 'designated': False}
   {'column': 'volume', 'type': 'DOUBLE', 'indexed': False, 'indexBlockCapacity': 0, 'symbolCached': False, 'symbolCapacity': 0, 'designated': False}
   

   Row count: 62587165

   Distinct pair: 190
   Distinct exchange: 5
2. bluestreak01 commented on Jul 28, 2022

   

   bluestreak01

   on Jul 28, 2022

   Member

   More actions

   hey Emmett, looking into it, bear with us. I got a hunch that you might want to drop those indexes on pair and exchange. But let me validate this thesis first
3. EmmettM commented on Jul 28, 2022

   

   EmmettM

   on Jul 28, 2022

   Author

   More actions

   @bluestreak01 no worries– yes I have already dropped those indexes and it did decrease latency from 6000ms to ~1200ms. Just documenting here in case this is something you all want to look at
4. bluestreak01 commented on Jul 28, 2022

   

   bluestreak01

   on Jul 28, 2022

   Last edited by bluestreak01

   Member

   More actions

   could you try these things (all of them):

   • replace in with =
   • upgrade to 6.4.3, we fixed perf issue there fix(sql): improve performance of certain SAMPLE BY queries #2277

   in doesn't use JIT (we will fix that), = does
5. bluestreak01 commented on Jul 28, 2022

   

   bluestreak01

   on Jul 28, 2022

   Member

   More actions

   @EmmettM the date range is 6 months, how many rows would you get with this SQL:

   select count() from 'ohlc_exchanges' 
   where pair = 'btc-usd'
         and exchange = 'coinbase' 
         and ts >= cast(1608864755 * 1000000L AS TIMESTAMP) 
         and ts <= cast(1656125555 * 1000000L AS TIMESTAMP)
6. EmmettM commented on Jul 31, 2022

   

   EmmettM

   on Jul 31, 2022

   Author

   More actions

   @bluestreak01 the result of that query is 157,847. Trying the other fixes now.
7. EmmettM commented on Jul 31, 2022

   

   EmmettM

   on Jul 31, 2022

   Last edited by EmmettM

   Author

   More actions

   Just updated the rest of the code– I initially thought there were performance improvements, but my queries are actually taking longer now (18+ seconds). Just to recap:

   QuestDB 6.4.3
   Updated query:
   select ts, last(close) from 'ohlc_exchanges_historical' where pair='btc-usd' and exchange='CPRO' and ts >= cast(0 * 1000000L AS TIMESTAMP) and ts <= cast(1656125555 * 1000000L AS TIMESTAMP) sample by 1d limit 5000
   This query in particular took 18.689 seconds to return 1705 records. There must be something wrong.

   Schema (removed both indexes):
   {'column': 'ts', 'type': 'TIMESTAMP', 'indexed': False, 'indexBlockCapacity': 0, 'symbolCached': False, 'symbolCapacity': 0, 'designated': True} {'column': 'pair', 'type': 'SYMBOL', 'indexed': False, 'indexBlockCapacity': 0, 'symbolCached': True, 'symbolCapacity': 256, 'designated': False} {'column': 'exchange', 'type': 'SYMBOL', 'indexed': False, 'indexBlockCapacity': 0, 'symbolCached': True, 'symbolCapacity': 256, 'designated': False} {'column': 'open', 'type': 'DOUBLE', 'indexed': False, 'indexBlockCapacity': 0, 'symbolCached': False, 'symbolCapacity': 0, 'designated': False} {'column': 'high', 'type': 'DOUBLE', 'indexed': False, 'indexBlockCapacity': 0, 'symbolCached': False, 'symbolCapacity': 0, 'designated': False} {'column': 'low', 'type': 'DOUBLE', 'indexed': False, 'indexBlockCapacity': 0, 'symbolCached': False, 'symbolCapacity': 0, 'designated': False} {'column': 'close', 'type': 'DOUBLE', 'indexed': False, 'indexBlockCapacity': 0, 'symbolCached': False, 'symbolCapacity': 0, 'designated': False} {'column': 'volume', 'type': 'DOUBLE', 'indexed': False, 'indexBlockCapacity': 0, 'symbolCached': False, 'symbolCapacity': 0, 'designated': False}

   This is a new table with 120,289,785 rows.

   There are no error messages in the logs that would indicate that there is some kind of error.