How to use joins correctly

[KantorSerhiy]

hey @EvgSkv !

how are you?

I have a question for you about logicа, and I hope you can help me.

for example, I have the following logica code -->

@NoInject(People);
People(
    person_id:p.id,
    name:p.name,
    age:p.age,
    city:p.city
) :-
  people_info(..p),
  p.is_deleted == false;


@NoInject(Salary);
Salary(
    person_id:s.person_id,
    сurrency: s.currency,
    amount: s.amount
) :-
  salary_info(..s);


@NoInject(Job);
Job(
    person_id: j.person_id,
    total_hours: j.total_hours,
    some_field: j.some_field,
    is_active: j.is_active
) :-
    total_jobs(..j);


HasSalary(person:, salary:) :-
    Salary(..salary),
    person.person_id == salary.person_id;

HasJob(person:, job:) :-
    Job(..job),
    person.person_id == job.person_id;


test-2(person2:, salary:) :-
  HasSalary(person:person2, salary:salary);

test-3(person3:, job:) :-
  HasJob(person:person3, job:job);

MainPredicate(
    person_id:person.id,
    name:person.name,
    age:person.age,
    city:person.city
) distinct :-
   People(..person),
    person.name == "Bob",
    test-2(person2:person, salary:salary),
    test-3(person3:person, job:job);

which generates the following sql ->

WITH t_0_People AS (SELECT
  people_info.id AS person_id,
  people_info.name AS name,
  people_info.age AS age,
  people_info.city AS city
FROM
  people_info
WHERE
  (people_info.is_deleted = false)),
t_1_Salary AS (SELECT
  salary_info.person_id AS person_id,
  salary_info.currency AS сurrency,
  salary_info.amount AS amount
FROM
  salary_info),
t_2_Job AS (SELECT
  total_jobs.person_id AS person_id,
  total_jobs.total_hours AS total_hours,
  total_jobs.some_field AS some_field,
  total_jobs.is_active AS is_active
FROM
  total_jobs)
SELECT
  People.id AS person_id,
  People.name AS name,
  People.age AS age,
  People.city AS city
FROM
  t_0_People AS People, t_1_Salary AS Salary, t_2_Job AS Job
WHERE
  (People.name = "Bob") AND
  (People.person_id = Salary.person_id) AND
  (People.person_id = Job.person_id)
GROUP BY person_id, name, age, city;

(this is a very simple example.)

On relatively small data, it works well.
But when there is a lot of data, sql takes a long time to execute.
As far as I understand, the logica does not support the direct use of joins.
Perhaps you can suggest how to get around this nicely (I mean joins) and optimise the logica code for more efficient use?

P.S.
Logica is amazing, I get excited every time I use it=)

[EvgSkv]

Hi @KantorSerhiy ,
your code looks reasonable, but you are extracting whole rows as records often and I don't have a good intuition on whether it's hard for engine. I tried to run your code in SQLite and SQLite can't even handle this record creation. So I adjusted it a little: CoLab.

On 100K input data the final query runs in 198ms for me:

╥╥     ▚  Job (152 ms)          
║║╥╥   ▚  Salary (111 ms)          
║║╨║╥  ▚  HasSalary (55 ms)          
╨║╥║║  ▚  HasJob (58 ms)          
╥║║║║╥ ▚  People (53 ms)          
╨╨║╨║║ ▚  Workflow (13 ms)          
  ╨ ╨╨ ▚  MainPredicate (198 ms)          
[     Execution complete.      ]     

Is this what you observe too? Do you consider this slow? :-) How big is you data?
Please let me know.

And thank you for your kind words!

[KantorSerhiy]

hey @EvgSkv !

thanks for your reply!

i think my tables are between 500k and 5m records.

I must have missed an important point, on some lines we do json aggregation, something like that — >
(A little bit of updated code)

JsonDistinctAgg(field) = SqlExpr(
  "JSON_AGG(DISTINCT {field})",
  {field:});
  
  
  MainPredicate(
    person_id:person.id,
    name:person.name,
    age:person.age,
    city:person.city,
    some_aggregated_data? JsonDistinctAgg = JsonbBuildObject("some_field1", salary.currency, "some_field2", salary.amount, "some_field3", person.name),
    some_aggregated_data2? JsonDistinctAgg = JsonbBuildObject("some_field1", job.total_hours, "some_field2", job.some_field)
    ) distinct :-
       People(..person),
        person.name == "Bob",
        test-2(person2:person, salary:salary),
        test-3(person3:person, job:job);

and part of sql -->

SELECT
  (People).id AS person_id,
  (People).name AS name,
  (People).age AS age,
  (People).city AS city,
  JSON_AGG(DISTINCT JSONB_BUILD_OBJECT('some_field1', (Salary).currency, 'some_field2', (Salary).amount, 'some_field3', (People).name)) AS some_aggregated_data,
  JSON_AGG(DISTINCT JSONB_BUILD_OBJECT('some_field1', (Job).total_hours, 'some_field2', (Job).some_field)) AS some_aggregated_data2
FROM
  t_1_People AS People, t_2_Salary AS Salary, t_3_Job AS Job
WHERE
  ((People).name = 'Bob') AND
  ((People).person_id = (Salary).person_id) AND
  ((People).person_id = (Job).person_id)
GROUP BY 1, 2, 3, 4;

we use the psql dialect (of course, a little bit extended, for example, jsonb functions)

[kamahen]

I might be stating the obvious; but the performance of joins depends heavily on how indexes are set up (I once was asked to fix a performance problem on a MySQL database and, after I did a few "EXPLAIN" commands and "CREATE INDEX" commands, an overloaded server became a server that was sleeping most of the time).

[EvgSkv]

Yeah, that's a great point, @kamahen !
@KantorSerhiy indexes should help indeed.
The JSON agg shouldn't be impacting time too much.

I've updated the CoLab to run on Postgres and use 5M rows. Here are new times:

╥╥      ▚  People (12511 ms)          
║║╥╥    ▚  Job (10388 ms)          
║║║║╥╥  ▚  Salary (10381 ms)          
║║╨║║║╥ ▚  HasJob (9823 ms)          
║║╥║╨║║ ▚  HasSalary (9701 ms)          
╨║║╨ ╨║ ▚  Workflow (3772 ms)          
╥║║   ╨ ▚  Test3 (8867 ms)          
╨╨╨     ▚  MainPredicate (10551 ms)          
[     Execution complete. 

Are they inline with what you see?
Given that creating data takes 12 seconds, reading it in 10 seems about what we should expect.
To go faster we'd need to set up indices as @kamahen suggested and then use only indexed columns to do the join (i.e. have that column to be comon when we call predicates).

[KantorSerhiy]

Everything is fine with the indixes =)

As far as I understand, the problem is that the logic puts everything together, I mean the WHERE clause, and before that it gets all the data from the tables. like here -->

FROM
  t_1_People AS People, t_2_Salary AS Salary, t_3_Job AS Job
WHERE
  ((People).name = 'Bob') AND
  ((People).person_id = (Salary).person_id) AND
  ((People).person_id = (Job).person_id)
GROUP BY 1, 2, 3, 4;

And I can't write in the logic so that these conditions are slightly separated. (at the moment when I make a query to one of the tables)

for example --

WITH t_0_People AS (SELECT
  people_info.id AS person_id,
  people_info.name AS name,
  people_info.age AS age,
  people_info.city AS city
FROM
  people_info
WHERE
  (people_info.is_deleted = false) AND people_info.name = 'bob'),
t_1_Salary AS (SELECT
  salary_info.person_id AS person_id,
  salary_info.currency AS сurrency,
  salary_info.amount AS amount
FROM
  salary_info,
  t_0_People as people_info
 WHERE 
 (people_info.person_id = (salary_info).person_id)
  ),

[EvgSkv]

@KantorSerhiy If you do need part of the query separated into a with statement you can achieve it with @With Logica imperative like so:

@With(WithBob);
WithBob(person_id:, name:, age:, city:) :-
   People(person_id:, name:, age:, city:),
   name == "Bob";

MainPredicateWithBob(person_id:,
                     name:,
                     age:,
                     city:,
                     salary? List= salary,
                     job? List= job) distinct :-
  WithBob(person_id:, name:, age:, city:),
  Test2(person2:person_id, salary:salary),
  Test3(person3:person_id, job:job);

Sometimes it can probably ineed make a difference. In this particular case I have added indexes and tested options of doing the join. The join happens within 5 milliseconds (when indexes are present) whether we use with, store intermediate in the database, or query directly: CoLab

╥╥╥╥         ▚  logica_home.People (input data)          
║║║║         ▚  T (2 ms)          
║║║║╥╥╥╥     ▚  logica_home.Test2 (input data)          
║║║║║║║║╥╥╥  ▚  logica_home.Test3 (input data)          
║║║║║║║║║║║╥ ▚  pg_indexes (input data)          
╨║║║╨║║║╨║║║ ▚  MainPredicateWithBob (5 ms)          
 ║║║ ║║║ ║║╨ ▚  Indexes (18 ms)          
 ╨║║ ╨║║ ║║  ▚  MainPredicateGroundedBobV0 (4 ms)          
  ╨║  ╨║ ╨║  ▚  MainPredicateGroundedBob (5 ms)          
   ╨   ╨  ╨  ▚  MainPredicateNaive (5 ms)          
[     Execution complete.      ]                              

I wonder why are your querries slow for you then... Could you maybe modify my colab, adding whatever aggregations you want to do and see if gets slow? Then we can figure out how to speed up your particular usecase.

We may need to add ability to execute indexing in Logica, that would be fine. But first I need to understand what is the reason for your querries being slow. It doesn't seem to be the with separation.

[KantorSerhiy]

Thank you for your quick response!

Yes, that sounds great, I'll try to reproduce my problem in colab tomorrow and I'll let you know!

[KantorSerhiy]

@EvgSkv I managed to roughly reproduce it))

I'm not sure what you can see in colab, so I'll duplicate it here again.

%%logica Workflow, MainPredicate

@Engine("psql");

N() = 10000;
@Ground(People);
People(person_id: i,
       name:,
       age: 20 + i % 100,
       city: "CityNumber" ++ ToString(i % 15)) :-
  name = (
      if i % 2 == 0 then
        "Bob"
      else if i % 3 == 0 then
        "Alex"
      else if i % 4 == 0 then
        "Ser"
      else if i % 5 == 0 then
        "Ivan"
      else
        "Person" ++ ToString(i)
  ),
  i in Range(N());

@Ground(AdditionalInfo);
AdditionalInfo(
    person_id: i,
    surname:,
    months_in_company: 3 + i % 100
) :-
  surname = (
        if i % 2 == 0 then
          "Smith"
        else if i % 3 == 0 then
          "Gray"
        else if i % 4 == 0 then
          "Blue"
        else if i % 3 == 0 then
          "Yellow"
        else
          "Surrrr" ++ ToString(i)
    ),
    i in Range(N());

@Ground(Salary);
Salary(person_id: (i + 30000) % N(),
       currency: "Cur" ++ ToString(i % 5),
       amount: 10000 + i % 20000) :-
  i in Range(N());

@Ground(Job);
Job(person_id: (i + 10000) % N(),
    total_hours: 20 + i % 20,
    another_id: (i + 10000) % N(),
    some_field: "field" ++ ToString(i % 13)) :-
  i in Range(N());

Workflow() += 1 :-
  People() | Salary() | Job();

HasSalary(person_id:,
          salary: {person_id:, currency:, amount:} ) :-
    Salary(person_id:, currency:, amount:);

HasJob(person_id:, job: {person_id:, total_hours:, some_field:}) :-
    Job(person_id:, total_hours:, some_field:);

SOMEANOTHERPREDICATE(person_id_1:, person_id_2:) :-
    People(person_id:person_id_2, name:, age:, city:),
    Job(person_id:person_id_2, another_id:person_id_1);

HasAddInfo(person_id:, data:{person_id:, surname:,months_in_company: }) :-
  AdditionalInfo(person_id:, surname:,months_in_company:);

Test2(person2:, salary:, data: ) :-
  HasSalary(person_id:person2, salary:salary),
  HasAddInfo(person_id:person2, data:data);

Test3(person3:, person:) :-
  SOMEANOTHERPREDICATE(person_id_1:person3, person_id_2:person);


MainPredicate(
    person_id:,
    name:,
    age:,
    city:,
    add_info? List= data,
    salary? List= salary
    ) distinct :-
   People(person_id:, name:, age:, city:),
    name == "Bob",
    Test2(person2:person_id, salary:salary, data:data),
    Test3(person3:person_id);

Yes, I apologize for its quality, for these names of predicates, but I think everything should be clear here.

I think (I'm not 100% sure) that the problem is in this sql ->

FROM
  logica_home.People AS People, logica_home.Salary AS Salary, logica_home.AdditionalInfo AS AdditionalInfo, logica_home.People AS t_0_People, logica_home.Job AS Job
WHERE
  (People.name = 'Bob') AND
  // here can be smth t_0_People.age = 22, ect
  (AdditionalInfo.person_id = People.person_id) AND
  (People.person_id = Salary.person_id) AND
  (Job.person_id = t_0_People.person_id) AND
  (People.person_id = Job.another_id)

because you can see that he is trying to pull out the same table in the same place.
we have the option that one table can have several different queries, and the main question is whether it can be built in a better way.

In the collab, it happens relatively quickly, but the real data in the table is much larger and slightly different=)

[EvgSkv]

Hello @KantorSerhiy
I got to investigate this.

It looks like Bobs are half the People table :-) I increased the table size to 1M to make it more challenging and I am getting hald a million Bobs. That's quite a bit and is expected to take a couple of seconds.
Is this intended, or can we put back Bob being only one row of People?

[KantorSerhiy]

hi @EvgSkv
I did this so that the table did not contain only BOB's, but a larger sample of names)

I don't think it matters, because I'm interested in the FROM at the end.

[kamahen]

The distribution of values will affect how useful any indexes are in speeding up the join (if half the entries are BOB, the index becomes more-or-less useless in a join; but you'd have to look at the EXPLAIN output to be sure).

[EvgSkv]

Right, if it's just one Bob, then indices should have it run fast.

I have grounded TestX predicates and now the predicate runs under 10ms (feels almost too good to be true, hope I am not confused):

╥╥       ▚  Test3 (input data)          
║║╥╥╥    ▚  Test2 (input data)          
║║║║║╥╥╥ ▚  People (input data)          
║║╨║║╨║║ ▚  MainPredicateFirstStage (5 ms)          
╨║ ╨║ ╨║ ▚  MainPredicateTwoStagesSecondStage (6 ms)          
 ╨  ╨  ╨ ▚  MainPredicate (6 ms)          
[     Execution complete.      ]    

So we first create and ground tables with data. I assume that Test are also some input tables.

%%logica Workflow
@Engine("psql");

N() = 1000000;
@Ground(People);
People(person_id: i,
       name:,
       age: 20 + i % 100,
       city: "CityNumber" ++ ToString(i % 15)) :-
  name = (
      if i == 42042 then
        "Main Bob"
      else if i == 37037 then
        "Another Bob"
      else if i % 2 == 0 then
        "Bob"
      else if i % 3 == 0 then
        "Alex"
      else if i % 4 == 0 then
        "Ser"
      else if i % 5 == 0 then
        "Ivan"
      else
        "Person" ++ ToString(i)
  ),
  i in Range(N());

@Ground(AdditionalInfo);
AdditionalInfo(
    person_id: i,
    surname:,
    months_in_company: 3 + i % 100
) :-
  surname = (
        if i % 2 == 0 then
          "Smith"
        else if i % 3 == 0 then
          "Gray"
        else if i % 4 == 0 then
          "Blue"
        else if i % 3 == 0 then
          "Yellow"
        else
          "Surrrr" ++ ToString(i)
    ),
    i in Range(N());

@Ground(Salary);
Salary(person_id: (i + 30000) % N(),
       currency: "Cur" ++ ToString(i % 5),
       amount: 10000 + i % 20000) :-
  i in Range(N());

@Ground(Job);
Job(person_id: (i + 10000) % N(),
    total_hours: 20 + i % 20,
    another_id: (i + 10000) % N(),
    some_field: "field" ++ ToString(i % 13)) :-
  i in Range(N());




HasSalary(person_id:,
          salary: {person_id:, currency:, amount:} ) :-
    Salary(person_id:, currency:, amount:),
    person_id ~ Num, currency ~ Str, amount ~ Num;

HasJob(person_id:, job: {person_id:, total_hours:, some_field:}) :-
    Job(person_id:, total_hours:, some_field:),
    person_id ~ Num, total_hours ~ Num, some_field ~ Str;

SOMEANOTHERPREDICATE(person_id_1:, person_id_2:) :-
    People(person_id:person_id_2, name:, age:, city:),
    Job(person_id:person_id_2, another_id:person_id_1);

HasAddInfo(person_id:, data:{person_id:, surname:,months_in_company: }) :-
  AdditionalInfo(person_id:, surname:,months_in_company:),
  months_in_company ~ Num,
  surname ~ Str;

@Ground(Test2);
Test2(person2:, salary:, data: ) :-
  HasSalary(person_id:person2, salary:salary),
  HasAddInfo(person_id:person2, data:data);

@Ground(Test3);
Test3(person3:, person:) :-
  SOMEANOTHERPREDICATE(person_id_1:person3, person_id_2:person);



Workflow() += 1 :-
  People() | Salary() | Job() | AdditionalInfo() | Test2() | Test3();

Then we create indexes:

import psycopg2

connection = psycopg2.connect(host='localhost', database='logica', user='logica', password='logica')
connection.autocommit = True
c = connection.cursor()
c.execute('drop index if exists logica_home.people_index; create index people_index on logica_home.People(person_id);')
c.execute('drop index if exists logica_home.people_names_index; create index people_names_index on logica_home.People(name);')
c.execute('drop index if exists logica_home.myindex2; create index myindex2 on logica_home.Test2(person2);')
c.execute('drop index if exists logica_home.myindex3; create index myindex3 on logica_home.Test3(person3);')
c.execute('drop index if exists logica_home.myindex4; create index myindex4 on logica_home.AdditionalInfo(person_id);')
c.execute('drop index if exists logica_home.myindex5; create index myindex5 on logica_home.Job(person_id);')
c.execute('drop index if exists logica_home.myindex6; create index myindex6 on logica_home.Salary(person_id);')

And finally run the main predicate:

MainPredicate(
    person_id:,
    name:,
    age:,
    city:,
    add_info? List= data,
    salary? List= salary
    ) distinct :-
   People(person_id:, name:, age:, city:),
    name == "Main Bob",
    Test2(person2:person_id, salary:salary, data:data),
    Test3(person3:person_id);

I have also a version that breaks query in two, it runs at same speed:

MainPredicateTwoStagesSecondStage(person_id:,
                                  name:,
                                  age:,
                                  city:,
                                  add_info:,
                                  salary:) :-
  MainPredicateFirstStage(person_id:, name:, age:, city:, salary:, add_info:, salary:),
  Test3(person3: person_id);

@With(MainPredicateFirstStage);
MainPredicateFirstStage(person_id:, name:, age:, city:,
                        add_info? List= data, salary? List= salary) distinct :-
  People(person_id:, name:, age:, city:),
  name == "Main Bob",
  Test2(person2:person_id, salary:salary, data:data);

As far as I understood you were concerned about FROM clause having too many tables at once. The MainPredicateTwoStagesSecondStage predicate breaks it up.

WITH t_0_MainPredicateFirstStage AS (SELECT
  People.person_id AS person_id,
  People.name AS name,
  People.age AS age,
  People.city AS city,
  ARRAY_AGG(Test2.data) AS add_info,
  ARRAY_AGG(Test2.salary) AS salary
FROM
  logica_home.People AS People, logica_home.Test2 AS Test2
WHERE
  (People.name = 'Main Bob') AND
  (Test2.person2 = People.person_id)
GROUP BY People.person_id, People.name, People.age, People.city)
SELECT
  MainPredicateFirstStage.person_id AS person_id,
  MainPredicateFirstStage.name AS name,
  MainPredicateFirstStage.age AS age,
  MainPredicateFirstStage.city AS city,
  MainPredicateFirstStage.add_info AS add_info,
  MainPredicateFirstStage.salary AS salary
FROM
  t_0_MainPredicateFirstStage AS MainPredicateFirstStage, logica_home.Test3 AS Test3
WHERE
  (MainPredicateFirstStage.salary = MainPredicateFirstStage.salary) AND
  (Test3.person3 = MainPredicateFirstStage.person_id);

CoLab

Does this answer your question? :-)

[EvgSkv]

A meta-point: I don't mean to dodge your question, I want to enable you to write the efficient Logica predicates specifically for the case you care about. But it won't necessarily match the SQL structure that you may be used to. If we need to change compiler to make your usecase reasonably efficient we can look into this, but first I want to make sure that existing state of compiler doesn't cover your usecase.