0

I am using PostgreSQL 14.6 and my goal is to find out if there is simpler, and faster, way to achieve the same results but still using a similar UNION query.

This is the SQL statement I would like to optimize:

SELECT hyobjects_id, hyobjects_globalpriority
  FROM hyobjects, hyobjectypes, actionlltypes
WHERE hyobjects_hyobjectypes = hyobjectypes_id
  AND hyobjectypes_actionlltypes = actionlltypes_id
  AND actionlltypes_id = 1 
UNION
SELECT hyobjects_id, hyobjects_globalpriority
  FROM hyobjects, actionstatuses, actionlltypes
WHERE actionstatuses_id = hyobjects_actionstatuses 
  AND actionstatuses_actionlltypes = actionlltypes_id
  AND actionlltypes_id = 1
EXCEPT
SELECT hyobjects_id, hyobjects_globalpriority
  FROM hyobjects, actionstatuses, actionlltypes
WHERE hyobjects_actionstatuses = actionstatuses_id
  AND actionstatuses_actionlltypes = actionlltypes_id
  AND actionlltypes_id = 2
UNION
SELECT hyobjects_id, hyobjects_globalpriority
  FROM hyobjects, hyobjectypes, actionstatuses, actionlltypes
WHERE hyobjects_hyobjectypes = hyobjectypes_id
  AND hyobjectypes_actionlltypes = actionlltypes_id
  AND actionlltypes_id = 1 
  AND hyobjects_actionstatuses = 1
  ORDER BY hyobjects_globalpriority DESC

Where by the table actionlltypes has actionlltypes_id = 1 which means ACTIVE and actionlltypes_id = 2 which means INACTIVE.

And where column hyobjectypes_actionlltypes references actionlltypes and column hyobjects_actionstatuses references actionlltypes. I need result where actionstatuses_id may be 1 or having actionlltypes_id = 1.

7
  • 1
    Fist, all of your queries are using FROM statements like this: FROM hyobjects, hyobjectypes, actionstatuses, actionlltypes Those are all CROSS JOIN when you do that. Without knowing the full data output goals here, my first instinct is that you use LEFT JOIN instead for a few if not all of these. But also, what do you even my by optimize here? Make the statement simpler? Or faster? Dec 24, 2022 at 17:32
  • Please consider reading this advice
    – mustaccio
    Dec 24, 2022 at 18:34
  • I need result where actionstatuses_id may be 1 or having actionlltypes_id = 1. That's more of a first vague hint than a task disription. Please provide a clear objective, core table definitions, and your Postgres version. Dec 24, 2022 at 20:30
  • To work on optimization, you need the EXPLAIN plan or preferably EXPLAIN (ANALYZE, BUFFERS). In text format, and as plain formatted text.
    – jjanes
    Dec 24, 2022 at 21:18
  • 1
    Thanks to all, I have been learning from you.
    – Jean Louis
    Dec 26, 2022 at 22:29

2 Answers 2

2

I encourage you to learn/use ANSI joins in your queries as they can

  • make your queries less ambiguous
  • make it harder to create unwanted Cartesian products (cross joins)

Looking at the column names and making an absolute guess as to how things link together using ANSI joins might look like:

SELECT hyobjects_id, 
        hyobjects_globalpriority
    FROM hyobjects 
    JOIN hyobjectypes
        ON ( hyobjectypes_id = hyobjects_hyobjectypes )
    JOIN actionlltypes
        ON ( actionlltypes_id = hyobjectypes_actionlltypes )
    WHERE actionlltypes_id = 1 
UNION
SELECT hyobjects_id, 
        hyobjects_globalpriority
    FROM hyobjects
    JOIN actionstatuses
        ON ( actionstatuses_id = hyobjects_actionstatuses )
    JOIN actionlltypes
        ON ( actionlltypes_id = actionstatuses_actionlltypes )
    WHERE actionlltypes_id = 1
EXCEPT
SELECT hyobjects_id, 
        hyobjects_globalpriority
    FROM hyobjects
    JOIN actionstatuses 
        ON ( actionstatuses_id = hyobjects_actionstatuses )
    JOIN actionlltypes
        ON ( actionlltypes_id = actionstatuses_actionlltypes )
    WHERE actionlltypes_id = 2
UNION
SELECT hyobjects_id, 
        hyobjects_globalpriority
    FROM hyobjects
    JOIN hyobjectypes
        ON ( hyobjectypes_id = hyobjects_hyobjectypes )
    JOIN actionstatuses
        ON ( )
    JOIN actionlltypes
        ON ( actionlltypes_id = hyobjectypes_actionlltypes )
    WHERE actionlltypes_id = 1 
        AND hyobjects_actionstatuses = 1
    ORDER BY hyobjects_globalpriority DESC ;

One thing to notice is in the fourth query, there do not appear to be any columns joining the actionstatuses table to anything else,resulting in a cross join. Is this intentional, or an accident?

Looking at the first query:

SELECT hyobjects_id, 
        hyobjects_globalpriority
    FROM hyobjects 
    JOIN hyobjectypes
        ON ( hyobjectypes_id = hyobjects_hyobjectypes )
    JOIN actionlltypes
        ON ( actionlltypes_id = hyobjectypes_actionlltypes )
    WHERE actionlltypes_id = 1 

Which is equivalent to

SELECT hyobjects_id, 
        hyobjects_globalpriority
    FROM hyobjects 
    JOIN hyobjectypes
        ON ( hyobjectypes_id = hyobjects_hyobjectypes )
    JOIN actionlltypes
        ON ( actionlltypes_id = hyobjectypes_actionlltypes )
    WHERE hyobjectypes_actionlltypes = 1 

If, and only if, my guess about the structure of things and naming of columns is correct then the only two columns in the result are from the hyobjects table, no? If so then is there any need to be joining the hyobjectypes and actionlltypes tables? If so, then the first query should reduce to:

SELECT hyobjects_id, 
        hyobjects_globalpriority
    FROM hyobjects 
    WHERE hyobjectypes_actionlltypes = 1

The second query could be reduced from

SELECT hyobjects_id, 
        hyobjects_globalpriority
    FROM hyobjects
    JOIN actionstatuses
        ON ( actionstatuses_id = hyobjects_actionstatuses )
    JOIN actionlltypes
        ON ( actionlltypes_id = actionstatuses_actionlltypes )
    WHERE actionlltypes_id = 1

to

SELECT hyobjects_id, 
        hyobjects_globalpriority
    FROM hyobjects
    JOIN actionstatuses
        ON ( actionstatuses_id = hyobjects_actionstatuses )
    WHERE actionstatuses_actionlltypes = 1

Query three

SELECT hyobjects_id, 
        hyobjects_globalpriority
    FROM hyobjects
    JOIN actionstatuses 
        ON ( actionstatuses_id = hyobjects_actionstatuses )
    JOIN actionlltypes
        ON ( actionlltypes_id = actionstatuses_actionlltypes )
    WHERE actionlltypes_id = 2
    

becomes

SELECT hyobjects_id, 
        hyobjects_globalpriority
    FROM hyobjects
    JOIN actionstatuses 
        ON ( actionstatuses_id = hyobjects_actionstatuses )
    WHERE actionstatuses_actionlltypes = 2

And query four

SELECT hyobjects_id, 
        hyobjects_globalpriority
    FROM hyobjects
    JOIN hyobjectypes
        ON ( hyobjectypes_id = hyobjects_hyobjectypes )
    JOIN actionstatuses
        ON ( )
    JOIN actionlltypes
        ON ( actionlltypes_id = hyobjectypes_actionlltypes )
    WHERE actionlltypes_id = 1 
        AND hyobjects_actionstatuses = 1
        

might become

SELECT hyobjects_id, 
        hyobjects_globalpriority
    FROM hyobjects
    JOIN hyobjectypes
        ON ( hyobjectypes_id = hyobjects_hyobjectypes )
    WHERE hyobjectypes_actionlltypes = 1
        AND hyobjectypes_actionlltypes = 1

Putting it all together then could result in

SELECT hyobjects_id, 
        hyobjects_globalpriority
    FROM hyobjects 
    WHERE hyobjectypes_actionlltypes = 1
UNION
SELECT hyobjects_id, 
        hyobjects_globalpriority
    FROM hyobjects
    JOIN actionstatuses
        ON ( actionstatuses_id = hyobjects_actionstatuses )
    WHERE actionstatuses_actionlltypes = 1
EXCEPT
SELECT hyobjects_id, 
        hyobjects_globalpriority
    FROM hyobjects
    JOIN actionstatuses 
        ON ( actionstatuses_id = hyobjects_actionstatuses )
    WHERE actionstatuses_actionlltypes = 2
UNION
SELECT hyobjects_id, 
        hyobjects_globalpriority
    FROM hyobjects
    JOIN hyobjectypes
        ON ( hyobjectypes_id = hyobjects_hyobjectypes )
    WHERE hyobjectypes_actionlltypes = 1
        AND hyobjectypes_actionlltypes = 1

But then, this is pure speculation based on my interpretation of the table and column names.

Now, what you're trying to actually optimize for is unclear so I'll leave off here...

3
  • 1
    Are those parenthesis like here really needed: JOIN hyobjectypes ON ( hyobjectypes_id = hyobjects_hyobjectypes ) Can’t the same query run like this? JOIN hyobjectypes ON hyobjectypes_id = hyobjects_hyobjectypes. To my eyes, the parenthesis seems to be excessive and redundant? Dec 26, 2022 at 18:00
  • 1
    Thanks, I have been learning from there.
    – Jean Louis
    Dec 26, 2022 at 22:29
  • 1
    @Giacomo1968 No the parenthesis in the joins aren't really needed, that's just my style. For single column joins it really doesn't matter but for multi-column joins I very much prefer it as my formatting utility uses the parens to get the indentation to my liking.
    – gsiems
    Dec 27, 2022 at 14:19
2

Thanks to all, for good pointer and references. I have been learning and trying it out.

My original statement works well, it is just bit slow. EXPLAIN ANALYZE give me following result for UNION based statement:

 Planning Time: 0.898 ms
 Execution Time: 182.354 ms

By looking into answers of skilled people here, I came out with this result:

SELECT hyobjects_id, hyobjects_globalpriority
  FROM hyobjects
  JOIN actionstatuses
  ON (hyobjects_actionstatuses = actionstatuses_id)
  JOIN hyobjectypes
  ON (hyobjectypes_id = hyobjects_hyobjectypes 
      AND ((hyobjectypes_actionlltypes = 1 
        AND (actionstatuses_actionlltypes = 1 OR hyobjects_actionstatuses = 1))
        OR ((hyobjectypes_actionlltypes = 2 AND (actionstatuses_actionlltypes = 1)))));

which gives me same result with following speed:

 Planning Time: 0.628 ms
 Execution Time: 57.047 ms
1
  • 1
    Wow! Great example of you learning and applying that knowledge here. Good work! Dec 27, 2022 at 1:11

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.