sql - TimescaleDB:有效地选择最后一行

Question

我有一个带有 timescaledb 扩展名的 postgres 数据库。

我的主索引是一个时间戳，我想选择最新的一行。

如果我碰巧知道最新的行发生在某个时间之后，那么我可以使用如下查询:

query = 'select * from prices where time > %(dt)s'

这里我指定了一个日期时间，并使用 psycopg2 执行查询:

# 2018-01-10 11:15:00
dt = datetime.datetime(2018,1,10,11,15,0)

with psycopg2.connect(**params) as conn:
    cur = conn.cursor()
    # start timing
    beg = datetime.datetime.now()
    # execute query
    cur.execute(query, {'dt':dt})
    rows = cur.fetchall()
    # stop timing
    end = datetime.datetime.now()

print('took {} ms'.format((end-beg).total_seconds() * 1e3))

定时输出:

took 2.296 ms

但是，如果我不知道输入上述查询的时间，我可以使用如下查询:

query = 'select * from prices order by time desc limit 1'

我以类似的方式执行查询

with psycopg2.connect(**params) as conn:
    cur = conn.cursor()
    # start timing
    beg = datetime.datetime.now()
    # execute query
    cur.execute(query)
    rows = cur.fetchall()
    # stop timing
    end = datetime.datetime.now()

print('took {} ms'.format((end-beg).total_seconds() * 1e3))

定时输出:

took 19.173 ms

所以这慢了 8 倍以上。

我不是 SQL 方面的专家，但我认为查询规划器会发现“限制 1”和“按主索引排序”等同于 O(1) 操作。

问题:

是否有更有效的方法来选择表格中的最后一行？

如果它有用，这里是我的表的描述:

# \d+ prices

                                           Table "public.prices"
 Column |            Type             | Collation | Nullable | Default | Storage | Stats target | Description 
--------+-----------------------------+-----------+----------+---------+---------+--------------+-------------
 time   | timestamp without time zone |           | not null |         | plain   |              | 
 AAPL   | double precision            |           |          |         | plain   |              | 
 GOOG   | double precision            |           |          |         | plain   |              | 
 MSFT   | double precision            |           |          |         | plain   |              | 
Indexes:
    "prices_time_idx" btree ("time" DESC)
Child tables: _timescaledb_internal._hyper_12_100_chunk,
              _timescaledb_internal._hyper_12_101_chunk,
              _timescaledb_internal._hyper_12_102_chunk,
              ...

Answer 1

在 TimescaleDB 中获取最后/第一条记录的有效方法:

第一条记录:

SELECT <COLUMN>, time FROM <TABLE_NAME> ORDER BY time ASC LIMIT 1 ;

最后记录:

SELECT <COLUMN>, time FROM <TABLE_NAME> ORDER BY time DESC LIMIT 1 ;

问题已经回答了，但我相信如果人们来到这里，它可能会有用。在 TimescaleDB 中使用 first() 和 last() 需要更长的时间。