Determine if ecto has_many association is nil

Questions / Help
,
1

Hello!

Currently I have an ecto query with the following general sql structure:

SELECT t1_attr1
FROM table1
WHERE condition1
  AND condition2
  AND t1_attr1 NOT IN(
    SELECT t2_attr1
    FROM table2 
    WHERE condition 3
         # super slow nested subquery
         AND (
           SELECT COUNT(1)
            FROM table3 INNER JOIN table 2
            WHERE t3_attr2 == t2_attr2
         ) > 0
  )

The nested subquery that I commented makes this query super slow.

I am wondering, since the schema for table2 “has_many” (via ecto has_many association) records in table 3, is there an ecto equivalent of !is_nil(table_1.table_2s) that would be faster than executing a nested subquery?

If there are other faster approaches, I am open to those as well!

Thanks! :smiley:

2

:wave:

I am wondering, since the schema for table2 “has_many” (via ecto has_many association) records in table 3, is there an ecto equivalent of !is_nil(table_1.table_2s) that would be faster than executing a nested subquery?

You might be able to use preloads and move part of your SQL logic into the app, i.e. you can build the query conditionally on the preloads you already have available.

If there are other faster approaches, I am open to those as well!

I think it largely depends on your database (I assume PostgreSQL), your data, and your schemas.

But some general ideas on how to speed it up might be:

  • try using https://explain.depesz.com to visualize the problematic parts a bit better
  • try using EXIST rather than COUNT, as it would short circut on the first match (I guess)
  • try using JOINs
1 Like