Fork me on GitHub
#sql
<
2023-03-31
>
Martynas Maciulevičius06:03:00

Hey. Does anyone know how to execute subselects in an efficient way? I want to have a query generator that would generate queries which would mostly have similar lists of tables with same joined attributes but I found in a search that PostgreSQL would simply run all subqueries multiple times even if they're the same query. Example (this won't be valid SQL, more like a pseudo code):

SELECT A.*, B.*
FROM (
  SELECT *
  FROM a, b, c, d, e
  WHERE ... 
  AS A,

  SELECT *
  FROM a, b, c, d, f
  WHERE ... 
  AS B,
)
So both A and B subselects share the tables a, b, c, d, but the result is slightly different from both. https://postgrespro.com/list/thread-id/2046322 I don't ask about PosgreSQL but this is a more general question. What I wanted to do with these subqueries is to get some initial data in the first query, then use that data in the second query and so on. Yes, if the generated query would change the predicates on a,b,c,d tables then it would have to rerun, but I want to know whether it would not rerun if only predicate on f table changed. Is this a possibility?

Martynas Maciulevičius06:03:35

I could probably rewrite it as

SELECT A.*
FROM (
  SELECT *
  FROM (
    SELECT *
    FROM a, b, c, d, f
    WHERE ... 
  ) as B, e
  WHERE ... 
  AS A,
)
But then it's harder to reason about it and it can always have only one reuse path :thinking_face: i.e. only a, b, c, d tables are going to be reused and if it would need a in a second branch then it would need to requery it again :thinking_face:
SELECT A.*
FROM (
  SELECT *
  FROM (
    SELECT *
    FROM a, b, c, d, f
    WHERE ... 
  ) as B
  SELECT *
  FROM (
    SELECT *
    FROM a, b, g
    WHERE ... 
  ) as C, e
  WHERE ... 
  AS A,
)
The second approach could still be rewritten as the first one but I try to think what's the best way. Probably it's best to not do this at all :thinking_face:

kolstae08:03:09

Sounds like you could take a look at https://learnsql.com/blog/what-is-common-table-expression/. I've only used this in PostgreSQL

slipset11:03:35

I agree with using CTE’s but you could also consider (materialized) views

slipset11:03:42

Or even tmp tables.

Martynas Maciulevičius04:04:00

I only want it to be short-lived. I mostly want to understand what I'm dealing with :thinking_face:

seancorfield19:03:35

A bug fix, a compatibility fix, and an enhancement!