Skip to content

Instantly share code, notes, and snippets.

@miohtama
Created June 13, 2021 19:07
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
Star You must be signed in to star a gist
Save miohtama/59ea6a5c918ab5b07716ef2a4b6d803b to your computer and use it in GitHub Desktop.
Fast approximate_row_count with TimescaleDB and SQLAlchemy
import logging
import time
import timeit
from typing import Type
from sqlalchemy.orm import Session
from sqlalchemy.sql import text
from dex_ohlcv.db import get_real_database
from dex_ohlcv.models.uniswap import Base, UniswapLikeCandle, Pair
from dex_ohlcv.processcontext import ProcessContext
ProcessContext.setup_logging(logging.DEBUG)
scoper = get_real_database()
def slow_query_2(dbsession):
"""Normal PSQL table"""
pair_count = dbsession.query(Pair).count()
return pair_count
def slow_query(dbsession):
"""TimescaleDB hypertable"""
candle_count = dbsession.query(UniswapLikeCandle.timestamp).count()
return candle_count
def timescaledb_fast_count(dbsession: Session, model: Type[Base]):
"""Get the fast count of rows in a TimescaleDB hypertable.
https://stackoverflow.com/a/67955064/315168
"""
table = model.__tablename__
# https://chartio.com/resources/tutorials/how-to-execute-raw-sql-in-sqlalchemy/
statement = text("""SELECT * FROM approximate_row_count(:table);""")
result = dbsession.execute(statement, {"table": table})
# (0,)
out = result.next()[0]
return out
with scoper as dbsession:
start = time.time()
print(slow_query(dbsession))
print("Slow 1", time.time() - start)
start = time.time()
print(slow_query_2(dbsession))
print("Slow 2", time.time() - start)
start = time.time()
print("Fast result", timescaledb_fast_count(dbsession, UniswapLikeCandle))
print("Fast", time.time() - start)
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment