Potential performance optimization max(timestamp) as a function arg #6633
Description
Is your feature request related to a problem?
On the QuestDB demo box, run these three queries. The first two execute in microseconds, the last one in a few millis.
SELECT dateadd('m', -15, pickup_datetime)
FROM trips
LIMIT -1
;
WITH m as
(select max(pickup_datetime) as pickup_datetime from trips)
SELECT dateadd('m', -15, pickup_datetime)
FROM m
;
SELECT dateadd('m', -15, max(pickup_datetime))
FROM trips
; Explain plans (in order)
Limit value: -1 skip-rows: 1634599312 take-rows: 1
VirtualRecord
functions: [dateadd('m',-15,pickup_datetime)]
PageFrame
Row forward scan
Frame forward scan on: trips
VirtualRecord
functions: [dateadd('m',-15,pickup_datetime)]
Limit value: 1 skip-rows: 0 take-rows: 1
PageFrame
Row backward scan
Frame backward scan on: trips
VirtualRecord
functions: [dateadd('m',-15,max)]
GroupBy vectorized: true workers: 46
values: [max_designated(pickup_datetime)]
PageFrame
Row forward scan
Frame forward scan on: trips
Describe the solution you'd like.
Ideally, the same optimizations would be used when max(timestamp) is used as an arg
Describe alternatives you've considered.
No response
Full Name:
Javier
Affiliation:
QuestDB
Additional context
No response