4 ms·

I would be very surprised if LEAD and LAG couldn't profit from indexing, if you design your query / window function in a way that it actually can profit. Perha

I would be very surprised if LEAD and LAG couldn't profit from indexing, if you design your query / window function in a way that it actually can profit. Perhaps you're hinting at the idea that it's too easy to screw this up as a user, for it to be inefficient

deleted 10y ago
[deleted]
jnordwick 10y ago
The doesn't help that much since you are probably already using an index for filter criteria, the table is probably unkeyed (fact table). The case it does work is that you are ordered on timestamp (for example), the query planner properly figures to use any filtering indexes and sequential indexes, ... magic occurs Now the job is making an SQL statement that the query planner can actual understand and optimize since SQL lacks so much of the machinery for order-dependent processing (this was a feature when SQL was conceived remember).
- deleted 10y ago
  [deleted]
- Jweb_Guru 10y ago
  Eh, there's been work done on this, e.g. order dependencies. Not sure how much of it has made it into commercial SQL engines, though.
  - jnordwick 10y ago
    link?
    - Jweb_Guru 10y ago
      Here's one: https://arxiv.org/pdf/1208.0084v1.pdf https://arxiv.org/pdf/1208.0084v1.pdf (Note the involvement of IBM. Dunno whether this has made it into DB2, but a lot of really interesting research has; also keep in mind that whatever IBM wants to put in DB2 has a high probability of making it into the SQL standard).
- lukaseder 10y ago
  What if I'm designing a covering index to cover for both my filtering and ordering needs? The SQL engine should pick that up in my opinion and avoid needing to re-order anything. Of course, SQL is by far more general purpose than producing ordered stuff. In fact, ordering is a completely non-relational thing that was even criticised in SQL in early days (such as offsets, limits, duplication, etc.). But SQL has learned to shoehorn foreign concepts into the language / platform for a long time, so I'm just curious about a concrete example where SQL window functions fail (and couldn't be fixed) in SQL for you.
  - jnordwick 10y ago
    Try doing a VWAP in a group by 5000 stock symbol over a LAG of last 30000 - basically a sample every 10 millis for 5 minutes. This is the simple case where you can actually use LAG of a fixed amount. (When the query returns tomorrow morning you realize that LAG was actually useless because your observation arrivals weren't periodic and you need to deal with / bucket for that to actually get usable results). We used to run a lot of LEAD/LAG queries on a very, very large Oracle install could never get the performance we wanted out of it. Basically turned it into a very expensive file server that spat out HDF that we then processed by a mix of shell and python. Should not have been faster than the overpriced heater in the corner we called a database but was.
    - thom 10y ago
      Just to clarify, when you say LEAD/LAG here, do you actually mean ROWS/RANGE? So, approximately: sum(price * quantity) over w / sum(quantity) over w as VWAP ⋮ window w as (partition by stock order by time range 30000 preceding) Totally willing to believe first-hand experience, but surprised and disappointed if it's impossible to make that fast. (Apologies if I'm totally misunderstanding the use case).
      - jnordwick 10y ago
        Is there really that much of a difference between LEAD/LAG and OVER+RANGE when using an aggregation function? I'm sure there is, but it has been a few years since I had to deal with some of this. There are probably also difference between OVER when used in TSQL and PLSQL. Last time I had to do this was actually with real-time advertising bidding, and it was across keywords, not ticker symbols (many more, much sparser). We had a 20 millisecond budget, and had to break the queries up to at least get partial results to push out bid even if we didn't have all the results back in from the database that we wanted. We used OVER+RANGE for average price, but needed LAG for first derivative (is the price trending up or down). We had to start doing roll ups of the data hourly and be constantly trimming to keep the real-time performance high enough, but that meant we needed to have have a separate off-line analytical system. Common to do, but still I think it can be done without it if you use the correct tech stack.

deleted 10y ago

[deleted]

jnordwick 10y ago

The doesn't help that much since you are probably already using an index for filter criteria, the table is probably unkeyed (fact table). The case it does work is that you are ordered on timestamp (for example), the query planner properly figures to use any filtering indexes and sequential indexes, ... magic occurs Now the job is making an SQL statement that the query planner can actual understand and optimize since SQL lacks so much of the machinery for order-dependent processing (this was a feature when SQL was conceived remember).

deleted 10y ago
[deleted]
Jweb_Guru 10y ago
Eh, there's been work done on this, e.g. order dependencies. Not sure how much of it has made it into commercial SQL engines, though.
- jnordwick 10y ago
  link?
  - Jweb_Guru 10y ago
    Here's one: https://arxiv.org/pdf/1208.0084v1.pdf https://arxiv.org/pdf/1208.0084v1.pdf (Note the involvement of IBM. Dunno whether this has made it into DB2, but a lot of really interesting research has; also keep in mind that whatever IBM wants to put in DB2 has a high probability of making it into the SQL standard).
lukaseder 10y ago
What if I'm designing a covering index to cover for both my filtering and ordering needs? The SQL engine should pick that up in my opinion and avoid needing to re-order anything. Of course, SQL is by far more general purpose than producing ordered stuff. In fact, ordering is a completely non-relational thing that was even criticised in SQL in early days (such as offsets, limits, duplication, etc.). But SQL has learned to shoehorn foreign concepts into the language / platform for a long time, so I'm just curious about a concrete example where SQL window functions fail (and couldn't be fixed) in SQL for you.
- jnordwick 10y ago
  Try doing a VWAP in a group by 5000 stock symbol over a LAG of last 30000 - basically a sample every 10 millis for 5 minutes. This is the simple case where you can actually use LAG of a fixed amount. (When the query returns tomorrow morning you realize that LAG was actually useless because your observation arrivals weren't periodic and you need to deal with / bucket for that to actually get usable results). We used to run a lot of LEAD/LAG queries on a very, very large Oracle install could never get the performance we wanted out of it. Basically turned it into a very expensive file server that spat out HDF that we then processed by a mix of shell and python. Should not have been faster than the overpriced heater in the corner we called a database but was.
  - thom 10y ago
    Just to clarify, when you say LEAD/LAG here, do you actually mean ROWS/RANGE? So, approximately: sum(price * quantity) over w / sum(quantity) over w as VWAP ⋮ window w as (partition by stock order by time range 30000 preceding) Totally willing to believe first-hand experience, but surprised and disappointed if it's impossible to make that fast. (Apologies if I'm totally misunderstanding the use case).
    - jnordwick 10y ago
      Is there really that much of a difference between LEAD/LAG and OVER+RANGE when using an aggregation function? I'm sure there is, but it has been a few years since I had to deal with some of this. There are probably also difference between OVER when used in TSQL and PLSQL. Last time I had to do this was actually with real-time advertising bidding, and it was across keywords, not ticker symbols (many more, much sparser). We had a 20 millisecond budget, and had to break the queries up to at least get partial results to push out bid even if we didn't have all the results back in from the database that we wanted. We used OVER+RANGE for average price, but needed LAG for first derivative (is the price trending up or down). We had to start doing roll ups of the data hourly and be constantly trimming to keep the real-time performance high enough, but that meant we needed to have have a separate off-line analytical system. Common to do, but still I think it can be done without it if you use the correct tech stack.