Question 114236 in PostgreSQL Question posted in
The official documentation can be found here.

Is it better to do 2 queries or 1 but big in PostgreSQL?

I’m working on a rights verification system.
In order to verify that the user has access to the documents, I make a request to the database
There are 2 cases when you can get access:

  1. The user is in the access zone of this document
    (for example, a user in the chat where the document was published)
select count(*) > 0 from Document 
   left join Chat
   left join ChatUser
   left join User
   left join Resource
   ...
where ...
  1. The document is public
select count(*) > 0 from Document 
   left join User
   left join Resource
   left join ...
   ...
where ...

I see 2 solutions

  1. Make a request covering both cases (union)

pros of such a solution -> 1 request to the database

cons of such a solution -> every time in 70% of cases I make an extra 5 joins, which can hit the performance of the query in the database

  1. Make 2 requests

First make a request whether the document is in the chat.

If false -> then make 1 more request

Tell me what algorithm is used in such cases?

Tags:

2

Answers


  1. 0

    If you have one-to-one relation then join queries should be used.

    Join queries with one-to-many relation will lead to more memory usage with redundant data.

    If you don’t have memory usage issue then also you should use join queries because in most of the cases, join queries are faster than multiple queries.

    Login or Signup to reply.
  2. 0

    If you think that network-latency will be a deal-breaker, why not offload the ‘if not found at first, try second query’ in a plsql-function? You’d only need to query the function once and it will return you a boolean. Heck, you could easily add another flag indicating whether it found it in the chat or needed to check out the public one too if your app needs that info.

    That said, you probably can gain quite a bit of performance by rewriting your query from

    select count(*) > 0 from Document 
   left join Chat
   left join ChatUser
   left join User
   left join Resource
   ...
where ...

    To

    SELECT (CASE WHEN EXISTS (
                            select * from Document 
                               left join Chat
                               left join ChatUser
                               left join User
                               left join Resource
                               ...
                            where ... ) THEN True ELSE False END)

    This way the system doesn’t need to check all situations that apply but can stop at the first match.

    I also find it strange that you have LEFT OUTER JOINs in there rather than INNER JOINs but that might be simplification for putting it on StackOverflow, right?

    I’m actually wondering if just merging the two queries using WHERE EXISTS() would not make the optimizer do the ‘only if not found at first do the second part’ … the EXPLAIN ANALYZE should make that quite clear. Use https://explain.dalibo.com/ or something like that to make it easier to interpret.

    So, it would become this:

    SELECT (CASE WHEN EXISTS (
                            select * from Document 
                              join Chat
                              join ChatUser
                              join User
                              join Resource
                               ...
                            where ... ) 
               OR EXISTS (              

                            select * from Document 
                              join User
                              join Resource
                              join ...
                               ...
                            where ...   )
                            
            THEN True ELSE False END)
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search