Replies: 11 comments 53 replies
-
|
@odanoburu Yes, I think benchmarking Logica with TPC-H queries makes total sense. And for recursion: initial support is there, e.g. this script already runs: If you want to discuss details in depth I'd be happy to. |
Beta Was this translation helpful? Give feedback.
-
|
ok, thanks for the answer! I'll get on with translating TPC-H queries to Logica (while learning the language at the same time). I did see the implementation of recursion, but was a bit scared off by the comments and by the heavier syntax (compared to datalog). I'd love to look into this in the future, but for now I think I'll focus on the benchmark! I'm sure to have a few questions while doing this, and I'll try to open a new discussion whenever I see a clear topic. If don't really know how to pose the question I'll ask here, is that ok with you? |
Beta Was this translation helpful? Give feedback.
-
|
Yes, this is great! |
Beta Was this translation helpful? Give feedback.
-
|
The question I would have here is Logica is converting its code to sql . As explained by EvgSkv earlier, the heavy lifting would be done by the respective DB engines. So was just curious from a performance perspective what can Logica do other than forming the right sql query - which is actually dependent on how the end user writes the predicates. Or am I missing something #JustCuriousPlease |
Beta Was this translation helpful? Give feedback.
-
|
Hello @jedi1992, you are absolutely right that the RDBMS will be doing the heavy lifting, but question would be whether Logica is producing the optimal SQL queries given a Logica query. As an example, recursive Logica queries could be compiled to loops (not sure if actually part of standard, but part of some implementations) or recursive SQL queries. Granted, this might be way less useful if for every SQL query I manage to write a Logica query that compiles to basically the same SQL query as the original, in which case I'm basically testing whether Logica has implemented all the SQL constructs used in the TPC-H benchmark, which is a very different question and maybe one which @EvgSkv already knows how to answer. If that's the case (is it @EvgSkv ?), I probably should rethink my approach. |
Beta Was this translation helpful? Give feedback.
-
|
Okay, I've been working on the first TPC-H query (and learning Logica at the same time :P), and I had two difficulties:
|
Beta Was this translation helpful? Give feedback.
-
|
Hi, |
Beta Was this translation helpful? Give feedback.
-
Yeah, since I'm not a SQL expert I'm not sure about the equivalence of both queries, my guess was that the current solution would count only distinct rows, while About the INTERVAL part, I just checked and indeed apparently sqlite does not support INTERVAL, while Postgres does. So I'll try to find a solution that supports both, and it won't use INTERVAL, just as yours doesn't. |
Beta Was this translation helpful? Give feedback.
-
|
Another query (TPC-H Q2), another question(s):
|
Beta Was this translation helpful? Give feedback.
-
|
Here is CoLab with my Version3 implementation: It runs in about 10 seconds, but I don't know if the scale is the same as in your tests. |
Beta Was this translation helpful? Give feedback.
-
|
@EvgSkv Very cool! Although the query plan seems more complicated, it does run some 3 times faster. There's a typo in the queries you posted ("requesets" -> "requests"), but this does not affect much the timing (maybe it would for a larger database? I'm using the one with scale factor of 1, you seem to be using the 0.01 one from https://github.com/lovasoa/TPCH-sqlite, right?). The result queries also miss the order by, which for sqlite doesn't seem to make any difference because the results come in proper order (or is it something else that causes this?) Can I check if my understanding of the Logica you wrote is correct? I'm sure what's happening when you write something --Run Time: real 2.937 user 1.791109 sys 1.136740
--QUERY PLAN
--|--CO-ROUTINE 4
--| |--CO-ROUTINE 2
--| | `--COMPOUND QUERY
--| | |--LEFT-MOST SUBQUERY
--| | | |--SCAN TABLE orders
--| | | `--SEARCH TABLE customer USING INTEGER PRIMARY KEY (rowid=?)
--| | `--UNION ALL
--| | `--SCAN TABLE customer AS t_2_customer
--| |--SCAN SUBQUERY 2 AS UNUSED_TABLE_NAME
--| `--USE TEMP B-TREE FOR GROUP BY
--|--SCAN SUBQUERY 4 AS OrdersByCustomer
--|--USE TEMP B-TREE FOR GROUP BY
--`--USE TEMP B-TREE FOR ORDER BY
WITH t_1_OrdersByCustomer_MultBodyAggAux AS (SELECT * FROM (
SELECT
customer.c_custkey AS col0,
1 AS logica_value
FROM
customer, orders
WHERE
NOT (orders.o_comment LIKE '%special%requests%') AND
(orders.o_custkey = customer.c_custkey)
UNION ALL
SELECT
t_2_customer.c_custkey AS col0,
0 AS logica_value
FROM
customer AS t_2_customer
) AS UNUSED_TABLE_NAME ),
t_0_OrdersByCustomer AS (SELECT
OrdersByCustomer_MultBodyAggAux.col0 AS col0,
SUM(OrdersByCustomer_MultBodyAggAux.logica_value) AS logica_value
FROM
t_1_OrdersByCustomer_MultBodyAggAux AS OrdersByCustomer_MultBodyAggAux
GROUP BY col0)
SELECT
OrdersByCustomer.logica_value AS c_count,
SUM(1) AS custdist
FROM
t_0_OrdersByCustomer AS OrdersByCustomer
GROUP BY c_count
ORDER BY custdist DESC,
c_count DESC;
|
Beta Was this translation helpful? Give feedback.
-
|
Oh, nice! I'm glad it's faster! 😄 Thanks for testing! And yes, I used the 0.1 scale dataset locally, but the full on in CoLab. As for used syntax:
Which is interpreted simply as:
Multiple rules work as union / disjunction. If the rule is non-aggregating then resulting table consists of all rows that matched first rule and all rows that matched the second rule. For the aggregating rule, the rows are first unioned and then aggregation kicks in. In Version 3 the second rule adds 0 to all customers, which is also happening for customers that pass the first rule, but it's noop. In Version 3 we explicitly add 1 only for customers that don't have orders by calling the propositional negation |
Beta Was this translation helpful? Give feedback.
-
|
Hi, in case it helps with this initiative at all, we translated the 22 TPC-H queries to "edn-flavoured Datalog" for https://opencrux.com, see https://github.com/juxt/crux/blob/15dda1ea65ece1cfd418bb5ea46a3a164607e411/crux-test/src/crux/fixtures/tpch.clj#L118-L639 |
Beta Was this translation helpful? Give feedback.
-
|
Thanks, @refset ! I'll take a look at them! |
Beta Was this translation helpful? Give feedback.
-
|
Hi @EvgSkv, I've been out a bit because of other stuff, but I've managed to build some benchmarking code that I could share if you'd like (I'm not posting it publicly before checking that I may share the TPC-H queries publicly!) Working on Q8 of the benchmark, I've had problems again with calling datetime functions. SQLite seems to not follow the standard very thoroughly here, so what would the best practice for handling vendor-specific built-in functions? I can't manage to use the extract built-in (which wouldn't work on sqlite, AFAIK) either (apparently that's still in development). I'm trying to translate strftime('%Y', o_orderdate) -- SQLite
-- extract(year from o_orderdate) -- original queryThere's a Thanks for the answers on the other thread, btw! I get it now. |
Beta Was this translation helpful? Give feedback.
-
|
Hi @odanoburu , For the missing functions: I think the practical thing to do now is to simply write it reflecting semantics as close as possible, potentially using Thank you for your work! |
Beta Was this translation helpful? Give feedback.
-
|
Thanks, @EvgSkv! I came up with |
Beta Was this translation helpful? Give feedback.
-
Yeah, this is a neat implementation! 👍 |
Beta Was this translation helpful? Give feedback.
-
|
@EvgSkv, on query 4 of the benchmark, there is an use of exists: select o_orderpriority,
count(*) as order_count
from orders
where o_orderdate >= date('1993-07-01') -- QDB
and o_orderdate < date('1993-07-01', '+3 month') -- QDB
and exists
(select *
from lineitem
where l_orderkey = o_orderkey
and l_commitdate < l_receiptdate )
group by o_orderpriority
order by o_orderpriority;What would the best way of writing this in Logica be? I can think of counting or using the negation of the subquery, but these will be probably generate less efficient queries. One can also use something akin to |
Beta Was this translation helpful? Give feedback.
-
|
@odanoburu , that's an interesting question! For now no Logica construct compiles to Let me know if you need help with writing the query, or optimizing it. |
Beta Was this translation helpful? Give feedback.
-
|
I see, thank you @EvgSkv ! I came up with this version: in which I use the Min aggregator to get the disjunction I need, although I fear this is not short-circuiting (it doesn't stop at the first match, but finds all matches, unlike EXISTS, I assume). Could you help me improve it? Counting also works, of course, but it's slower: For reference, the original query runs faster than both versions above: |
Beta Was this translation helpful? Give feedback.
-
|
This is an interesting result! |
Beta Was this translation helpful? Give feedback.
-
|
@odanoburu , you said:
What is your concern about posting the queries publicly? Do you mean original queries, or your program? |
Beta Was this translation helpful? Give feedback.
-
|
It's just that I'm unsure if I have a license to distribute the original TPC-H queries myself (and the queries are currently part of the repository). I couldn't any specific info about this, only about the use of the TPC trademark, and making sure that we can't say we used the TPC-H benchmark, but a derivation of it. It's probably fine, though. |
Beta Was this translation helpful? Give feedback.
-
|
@odanoburu , thanks for explaining! Could you give me a link to the repo you refer to? |
Beta Was this translation helpful? Give feedback.
-
|
Sure! It's at https://github.com/odanoburu/logica-tpc/, I'll give you access now. If anything in the README and sparse documentation is unclear, do let me know! |
Beta Was this translation helpful? Give feedback.
-
|
Hi, @EvgSkv! While translating query 11 (see the original below), I had two problems. -- original TPC-H query
select ps_partkey,
sum(ps_supplycost * ps_availqty) as value
from partsupp,
supplier,
nation
where ps_suppkey = s_suppkey
and s_nationkey = n_nationkey
and n_name = 'GERMANY' -- QDB
group by ps_partkey
having sum(ps_supplycost * ps_availqty) >
(select sum(ps_supplycost * ps_availqty) * 0.0001 -- QDB
from partsupp,
supplier,
nation
where ps_suppkey = s_suppkey
and s_nationkey = n_nationkey
and n_name = 'GERMANY') -- QDB
order by value desc;Unifying two similar subqueriesThis query is composed of two very similar subqueries, which could be defined as a single Logica predicate-function with an optional argument (I think, I wasn't able to do it). Currently I have the two predicates below: I tried making the part argument in the latter predicate an optional argument, so that I could scrub the former predicate. I tried changing the output not accepted by SQLiteThe query as I have it looks like which outputs the following SQL WITH t_0_TotalPartsValueInCountry AS (SELECT
nation.n_name AS country,
SUM(((t_1_partsupp.ps_supplycost) * (t_1_partsupp.ps_availqty))) AS value
FROM
partsupp AS t_1_partsupp, supplier, nation
WHERE
(t_1_partsupp.ps_suppkey = supplier.s_suppkey) AND
(supplier.s_nationkey = nation.n_nationkey)
GROUP BY country),
t_2_TotalPartValueInCountry AS (SELECT
t_5_nation.n_name AS country,
t_3_partsupp.ps_partkey AS part,
SUM(((t_3_partsupp.ps_supplycost) * (t_3_partsupp.ps_availqty))) AS value
FROM
partsupp AS t_3_partsupp, supplier AS t_4_supplier, nation AS t_5_nation
WHERE
(t_3_partsupp.ps_suppkey = t_4_supplier.s_suppkey) AND
(t_4_supplier.s_nationkey = t_5_nation.n_nationkey)
GROUP BY country, part)
SELECT
partsupp.ps_partkey AS ps_partkey,
TotalPartValueInCountry.value AS value
FROM
partsupp, t_0_TotalPartsValueInCountry AS TotalPartsValueInCountry, t_2_TotalPartValueInCountry AS TotalPartValueInCountry
WHERE
(TotalPartValueInCountry.value > ((0.0001) * (TotalPartsValueInCountry.value))) AND
(TotalPartValueInCountry.country = TotalPartsValueInCountry.country) AND
(TotalPartValueInCountry.part = partsupp.ps_partkey) AND
(TotalPartsValueInCountry.country = 'GERMANY')
GROUP BY ps_partkey, value ORDER BY value desc;which SQLite does not accept, complaining Thank you for the help! |
Beta Was this translation helpful? Give feedback.
-
|
Sorry for delay, it took me time to understand what's going on here. Turns out SQLite prefers to interpret GROUP BY items as expressions rather than column names. |
Beta Was this translation helpful? Give feedback.
-
|
Hi @EvgSkv, is there a way to use the IN and BETWEEN operators from Logica? E.g., to write something akin to and p_container in ('SM CASE', 'SM BOX', 'SM PACK', 'SM PKG')
and p_size between 1 and 5without desugaring to and (p_container = 'SM CASE' or p_container == 'SM BOX' or p_container == 'SM PACK' or p_container =='SM PKG')
and p_size >= 1 and p_size <= 5In the case of IN, I tried something like |
Beta Was this translation helpful? Give feedback.
-
|
Hello @odanoburu ! |
Beta Was this translation helpful? Give feedback.
-
|
@odanoburu This is great news! Thank you very much! As for query 15, maybe it can be added to the list, using an auxiliary predicate instead of a view? It serves the same purpose for the user, so for a reader non-familiar with Logica a statement that Logica doesn't support views could be misleading. Huge thanks for doing this! |
Beta Was this translation helpful? Give feedback.
-
It might be the query I wrote (I hope it's clear in the text that that may be the cause!), but that's also the query that uses
I'm not 100% sure I follow you here, so I'll just expound my thoughts and you can correct me as needed. We can of course translate the underlying query of the view, and even string-substitute it inside the |
Beta Was this translation helpful? Give feedback.
-
|
I'm trying to say something very simple :-) Views in SQL are used to abstract away logic to be reused. This is what predicates are used in Logica for. In the text explaining the results it would be said that Logica doesn't support compilation of the views and this query was compiled in a single statement. Its just a minor technical comment though. |
Beta Was this translation helpful? Give feedback.
-
|
Ok, after reading your comment and looking some things up I see that I was confusing views with materialized views (which would indeed be different, right?) So I did understand what you meant after all, but I thought it couldn't be equivalent to (materialized) views, but indeed it is equivalent to a regular view (modulo something small like the size of the query sent through the connection). So I'll update the post after translating and benchmarking query 15 :) |
Beta Was this translation helpful? Give feedback.
-
|
Great, thank you! |
Beta Was this translation helpful? Give feedback.
-
|
Hi @EvgSkv , I'm having trouble translating Q15 (maybe I'm rusty after all this time). I'd say the problem is similar to the one I had with Q2, but your proposed solution for that problem (link) uses intra-record aggregation, which only allows me to get the variable we are aggregating from the 'subquery', but I need it's ID (too). In the case of Q2 I had an ID and I wanted a minimum related to it in some way, but here what I need is the maximum and the ID that pertains to it. I tried a custom aggregation function that would store the ID and the value, but that didn't work (and introduced JSON in the produced query, which I'd rather avoid). The closest example I found on the docs uses ArgMax and arrow syntax (where can I read more about it?), but it doesn't work for SQLite (is it a bigquery thing?) I also tried finding the maximum with negation, and it worked, but it's slower. query plans and timings: The original query, of course, is: |
Beta Was this translation helpful? Give feedback.
-
|
For Your implementation via |
Beta Was this translation helpful? Give feedback.
-
|
Thanks for the help, @EvgSkv !
Indeed! It's much easier to arrive at than other solutions. But I wonder: do you see a way of writing the query that would result in the original one? Although ArgMax is great, it does change the benchmark a bit in that all other queries compile to SQL and then get executed, while this one would be executed by Logica through Python. |
Beta Was this translation helpful? Give feedback.
-
|
@odanoburu to avoid use of Let me know if you discover any issues while trying this. |
Beta Was this translation helpful? Give feedback.
-
|
Thanks @EvgSkv! I tried this idea, but didn't manage to get it to work for some reason. I ended up with I've updated the repository and the report at https://odanoburu.github.io/research-log/logica-tpc.html. (I also fixed the mention of query Q04 being the only dramatically slower one, since Q16 is pretty slower too; Q16 at least I think can be more easily improved than Q04.) |
Beta Was this translation helpful? Give feedback.
-
|
@odanoburu thanks a lot for updating the doc! In my opinion some queries being slow isn't a problem at all for this report. Identifying problematic cases is important. We can have a followup later explaining the reason for query being slow and if we find a way to improve them, give the guidance on best practices for writing efficient queries. And the program in your post looks good to me. What error are you getting? Or is it just super slow? For debugging you can add This way the program will split into multiple queries and the area of debugging will be narrowed. |
Beta Was this translation helpful? Give feedback.
-
|
@EvgSkv my message was a bit confusing! I meant that I tried this idea previously and couldn't get it work! (I now see I was messing up the aggregation) The version I posted is a modification of the one you provided, and it does work (as you saw yourself). Sorry about that 😅 |
Beta Was this translation helpful? Give feedback.
-
|
@odanoburu oh ok, thank you, I understand now! 🙂 |
Beta Was this translation helpful? Give feedback.
-
Hi @EvgSkv, how are you thinking of tackling a benchmark of Logica (#38)?
After some consideration, I thought that since recursion as a primitive is not supported, we should focus on traditional relational benchmarks like the ones from TPC. (After recursion is added, we could try to compare Logica + BigQuery versus, say, Neo4j + Cypher, or SPARQL + Allegrograph)
Specifically, as a first attempt, I thought of translating the queries in TPC-H http://tpc.org/tpch/default5.asp to Logica, and then comparing the execution time between the original queries and the ones compiled from Logica. Do you think this is a fruitful approach?
Beta Was this translation helpful? Give feedback.
All reactions