xtdb-lucene

xtdb-lucene.clj

;; dataset size: 10 users, 200 clients, 30k invoices

(time
    (let [client-ids
          (->>
            (xt/q (db)
                  '{:find [?client]
                    :where [[(lucene-text-search
                               "client\\/first-name: %1$s* OR client\\/last-name: %1$s* OR client\\/company-name: %1$s*"
                               ?s-str)
                             [[?client]]]]
                    :in [?s-str]}
                  "Bird")
            time (map first))

          user-ids
          (->>
            (xt/q (db)
                  '{:find [?user]
                    :where [[(lucene-text-search "user\\/name: %1$s*" ?s-str)
                             [[?user]]]]
                    :in [?s-str]}
                  "Jim")
            time (map first))]
      (time
        (xt/q
          (db)
          {:find
           '[(pull ?invoice
                   [*
                    {:inv/client [:xt/id :client/first-name :client/last-name]}
                    {:inv/user [:xt/id :user/name]}])
             ?ix]

           :limit 20
           :order-by '[[?ix :asc]]

           :where
           '[(or [?invoice :inv/user ?users]
                 [?invoice :inv/client ?clients]

                 [(lucene-text-search "invoice\\/note: %1$s* OR invoice\\/reference: %1$s*" ?s-str)
                  [[?invoice]]])

             [(get-attr ?invoice :fts/x) ?ix]]

           :in '[?s-str [?users] [?clients]]}
          "Bird" user-ids client-ids))
      ))
;; runs in 10-20ms

;; one query run in ~200ms
(time
    (xt/q
      (db)
      {:find
       ;; '[(count ?invoice)]
       '[(pull ?invoice
               [*
                {:inv/client [:xt/id :client/first-name :client/last-name]}
                {:inv/user [:xt/id :user/name]}])
         ?ix]

       :limit 20
       :order-by '[[?ix :asc]]

       :where
       '[(or-join
           [?invoice ?s-str]

           (and
             [(lucene-text-search
                "client\\/first-name: %1$s* OR client\\/last-name: %1$s* OR client\\/company-name: %1$s*"
                ?s-str)
              [[?client]]]
             [?invoice :inv/client ?client])

           (and
             [(lucene-text-search "user\\/name: %1$s*" ?s-str)
              [[?user]]]
             [?invoice :inv/user ?user])

           [(lucene-text-search "invoice\\/note: %1$s* OR invoice\\/reference: %1$s*" ?s-str)
            [[?invoice]]])

         [(get-attr ?invoice :fts/x) ?ix]]

       :in '[?s-str]}
      "Bird"))