xtdb 2024-04-15 | Slack Archive

Sagar Vrajalal11:04:46

I've been playing around with V2 and I'd like to know if there is a way to efficiently paginate without realizing the entire result set. I'm using a combination of order-by and where (keyset pagination) but I'm not sure how to analyze if the entire result set is being realized. My thinking is, since every attribute is indexed, does XT leverage an indexed order by (https://use-the-index-luke.com/sql/sorting-grouping/indexed-order-by)? Would I approach pagination in XT the same way as I would in, say, Postgres?

jarohen12:04:30

Hey @U01B1CZQ9PF - not yet I'm afraid, but it'll be on the roadmap 🙂

jarohen12:04:14

But yes, broadly speaking, if you're frequently doing this kind of top-n query, it'll be worth maintaining a secondary index on your sort column(s) (when we have them)

Sagar Vrajalal12:04:25

Ah bummer! Thanks @U050V1N74 🙂

Sagar Vrajalal12:04:04

Do we have a solution for efficient pagination on V1?

Sagar Vrajalal12:04:48

My use case is to be able to sort on any attribute/column. Since every attribute is indexed, I assumed it was possible.

jarohen12:04:20

It depends on the rest of your query, tbh - if your filtering it'll currently always take those in preference to an order-by. Not ideal, admittedly 😕

Panel14:04:40

https://docs.xtdb.com/reference/main/xtql/queries.html#_offset > The 'offset' operator skips the first N rows that would have otherwise been returned by the query. Without an explicit preceding https://docs.xtdb.com/reference/main/xtql/queries.html#_order_by, the rows selected for return are undefined. Does that mean if you don't have a order by preceding the offset, the returned rows ordering is not going to be consistent ?

jarohen15:04:07

It's not necessarily going to be consistent, yep - chances are you'll get the same default ordering every time, but it's not guaranteed, so best to use order-by if you need it to be.

👍 1

Panel14:04:29

I cannot pass args to offset or limit, is this expected ?

(defn all-organisations [offset limit]
  (xt/q (db/db) '(-> (from :organisations [organisation/description,
                                           organisation/id ,
                                           organisation/name ,
                                           organisation/public-name,
                                           xt/id])
                     (offset $offset)
                     (limit $limit))
        {:args {:offset 1
                :limit 2}}))

(all-organisations 1 10)

1. Unhandled java.lang.ClassCastException
   class clojure.lang.Symbol cannot be cast to class java.lang.Number
   (clojure.lang.Symbol is in unnamed module of loader 'app'; java.lang.Number
   is in module java.base of loader 'bootstrap')

jarohen15:04:29

This sounds like a todo on our part, yep 🙂

❤️ 1

Panel15:04:03

Thanks for the fast reply and for working on this, looking forward to release. In the meantime i should be able to do something with syntax quoting / splicing ? I’ll try

refset16:04:51

> syntax quoting / splicing yep that should unblock you for time being, and the only impact is a slight overhead in query planning (since :args takes advantage of query plan caching, in theory anyway)

Panel01:04:38

I got it to work like so :

(xt/q (db/db) (list '-> 
                      (list 'from :organisations 
                            '[organisation/description
                              organisation/id
                              organisation/name
                              organisation/public-name
                              xt/id])
                      (list 'offset offset)
                      (list 'limit limit)))

Is there a more concise way to achieve the quoting ?

jarohen07:04:09

I'd give xt/template a go - it's like syntax-quote but without the symbol resolution, so you'll just need to unquote ~offset and ~limit (we pulled it in from the 'backtick' library)

👍 1

2024-04-15

Channels