Home / Questions / 3333
Accepted
Ariel Octavio D'Alfeo
Asked: 2020-01-29 11:58:34 +0800 CST 2020-01-29 11:58:34 +0800 CST

What is the alternative to SQL Server's OPTION (FORCE ORDER) for PostgreSql?

  • 772

I was looking for an option similar to OPTION (FORCE ORDER)SQL Server, for and I didn't find anything. Exists? I have used it in SQL Server in large Queries and they go from taking 5min to 0.5sec.

I leave a review for those who do not know what it is for OPTION (FORCE ORDER)in Sql Server:

When this statement is added to the query, SQL Server is told that when the statement is executed, the order of the JOINs in the query should not change. It will join the tables in the exact order that is specified in the query. Typically, the SQL Server optimizer rearranges the order that it thinks will be optimal for your search.

To use the FORCE ORDER query hint, you will need to add this to the end of the query.

sql-server

1 Answers

  1. Best Answer

    Yes, you can do the same thing in PostgreSQL with the join_collapse_limit = 1.

    You can read about explicit joins (English) , or specifically about join_collapse_limit .

    For example:

    SET join_collapse_limit = 1;
SELECT *
FROM foo
JOIN bar ON bar.foo_id = foo.id
RIGHT JOIN baz ON baz.bar_id = bar.id;
    • 3