https://linen.dev logo
Join Slack
Powered by
What is the best SQL expression to use to retrieve...
# general
q
What is the best SQL expression to use to retrieve all the rows from the latest scrape (i.e. the rows that have the most recent timestamp)? I tried the obvious
Copy code
SELECT * FROM observation WHERE greptime_timestamp = (SELECT greptime_timestamp FROM observation ORDER BY greptime_timestamp DESC LIMIT 1);
but it appears to be doing a full table scan and takes many seconds. I'll attach an 
explain analyze verbose
in a thread.
export_sql_19_greptimedb.csv
export_sql_19_greptimedb.csv
d
oh there is a bug related to scalar equality join in datafusion, which use the large table for build table, fixing in progress, meanwhile a workaround is explictly join smaller table on left side so planner knows which is build table, like this:
Copy code
SELECT
    t.*
FROM (
    SELECT
        greptime_timestamp
    FROM
        <table_name>
    ORDER BY
        greptime_timestamp DESC
    LIMIT
        1
) AS latest
JOIN <table_name> AS t
    ON t.greptime_timestamp = latest.greptime_timestamp;
q
Thanks, that is indeed much much faster.
What about fetching the latest value for each time series independently? (i.e. I think what a bare PromQL 
metric_name
would fetch) I tried
Copy code
SELECT greptime_timestamp, dst_port FROM netflow_raw WHERE src = '172.30.96.104' AND rank() OVER (PARTITION BY dst_port ORDER BY greptime_timestamp DESC) = 1 ORDER BY greptime_timestamp DESC LIMIT 10;
to get the latest row for each value of the 
dst_port
tag, but that produces an error
Copy code
This feature is not implemented: Physical plan does not support logical expression WindowFunction(WindowFunction { fun: WindowUDF(WindowUDF { inner: Rank { name: "rank", signature: Signature { type_signature: Nullary, volatility: Immutable, parameter_names: None }, rank_type: Basic } }), params: WindowFunctionParams { args: [], partition_by: [Column(Column { relation: Some(Bare { table: "netflow_raw" }), name: "dst_port" })], order_by: [Sort { expr: Column(Column { relation: Some(Bare { table: "netflow_raw" }), name: "greptime_timestamp" }), asc: false, nulls_first: true }], window_frame: WindowFrame { units: Range, start_bound: Preceding(IntervalMonthDayNano("NULL")), end_bound: CurrentRow, is_causal: false }, filter: None, null_treatment: None, distinct: false } })
Obviously for this trivial query I could use
Copy code
SELECT MAX(greptime_timestamp), dst_port FROM netflow_raw WHERE src = '172.30.96.104' GROUP BY dst_port ORDER BY MAX(greptime_timestamp) DESC LIMIT 10;
but I want to access all the fields of the matching row, not just the timestamp value.
Maybe this is
Copy code
SELECT DISTINCT ON (dst_port) * FROM netflow_raw WHERE src = '172.30.96.104'  ORDER BY dst_port, greptime_timestamp DESC LIMIT 10;
with an added 
WHERE greptime_timestamp > (now() - interval '1 day')
to control the lookback window?
d
oh for best optimize you need to rewrite the query to something like this:
Copy code
SELECT
    dst_port,
    last_value(greptime_timestamp ORDER BY greptime_timestamp) AS latest_ts,
    last_value(dst_addr ORDER BY greptime_timestamp) AS dst_addr,
    last_value(byte_count ORDER BY greptime_timestamp) AS byte_count,
FROM netflow_raw
WHERE src = '172.30.96.104'
  AND greptime_timestamp > now() - INTERVAL '1 day'
GROUP BY dst_port
ORDER BY latest_ts DESC
LIMIT 10;
here is a longer explain:
DISTINCT ON is a valid and concise way to express the query, but for better performance in GreptimeDB, especially on large time-series tables, prefer the ordered aggregate form with last_value(... ORDER BY greptime_timestamp). It can use the last-row selector optimization. The tradeoff is that the columns to return must be listed explicitly instead of using SELECT *.
q
last_value
can only operate on one column at a time, though
so I have no way to access the corresponding timestamp for the row it selects
right?
d
you can do this then:
Copy code
SELECT *
FROM (
    SELECT DISTINCT ON (dst_port) *
    FROM netflow_raw
    WHERE src = '172.30.96.104'
      AND greptime_timestamp > now() - INTERVAL '1 day'
    ORDER BY dst_port, greptime_timestamp DESC
) latest_per_port
ORDER BY greptime_timestamp DESC
LIMIT 10;
or like this:
Copy code
SELECT
    greptime_timestamp,
    src,
    dst_port,
    dst_addr,
    byte_count,
    flow_id
FROM (
    SELECT
        *,
        rank() OVER (
            PARTITION BY dst_port
            ORDER BY greptime_timestamp DESC
        ) AS r
    FROM netflow_raw
    WHERE src = '172.30.96.104'
      AND greptime_timestamp > now() - INTERVAL '1 day'
) t
WHERE r = 1
ORDER BY greptime_timestamp DESC
LIMIT 10;
2 Views
Open in Slack
PreviousNext
Powered by