Mixing
Does the 'quality' of query optimization differ by database provider?
Clash Royale CLAN TAG#URR8PPP
.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty margin-bottom:0;
up vote
2
down vote
favorite
I don't have a clear understanding of query optimization in any database, other than that 'it happens'. Now I've just seen a "medium" post that discusses the pitfalls of using a PostgreSQL CTE in terms of optimization since the CTE is only evaluated once and that any optimizations that may be applied in terms of how the CTE is used, the database just can't apply.
But the example in the blog post seems trivial to optimize - i.e. the CTE is:
SELECT * FROM foo WHERE id = 500000;
vs
WITH cte AS (
SELECT * FROM foo
)
SELECT * FROM cte WHERE id = 500000;
If calculation of the CTE is done lazily on first requirement then I would imagine that these two queries could be optimized in the same way (I think at least).
And that made me wonder... Would SQL Server be able to optimize such a query better than postgres?
Are there known differences between databases to the extent/ability they have in optimizing queries?
Which are the best and which are the worst?
sql-server postgresql optimization cte
asked 1 hour ago
Zach Smith
79611026
add a comment |Â
up vote
2
down vote
favorite
I don't have a clear understanding of query optimization in any database, other than that 'it happens'. Now I've just seen a "medium" post that discusses the pitfalls of using a PostgreSQL CTE in terms of optimization since the CTE is only evaluated once and that any optimizations that may be applied in terms of how the CTE is used, the database just can't apply.
But the example in the blog post seems trivial to optimize - i.e. the CTE is:
SELECT * FROM foo WHERE id = 500000;
vs
WITH cte AS (
SELECT * FROM foo
)
SELECT * FROM cte WHERE id = 500000;
If calculation of the CTE is done lazily on first requirement then I would imagine that these two queries could be optimized in the same way (I think at least).
And that made me wonder... Would SQL Server be able to optimize such a query better than postgres?
Are there known differences between databases to the extent/ability they have in optimizing queries?
Which are the best and which are the worst?
sql-server postgresql optimization cte
asked 1 hour ago
Zach Smith
79611026
add a comment |Â
up vote
2
down vote
favorite
up vote
2
down vote
favorite
I don't have a clear understanding of query optimization in any database, other than that 'it happens'. Now I've just seen a "medium" post that discusses the pitfalls of using a PostgreSQL CTE in terms of optimization since the CTE is only evaluated once and that any optimizations that may be applied in terms of how the CTE is used, the database just can't apply.
But the example in the blog post seems trivial to optimize - i.e. the CTE is:
SELECT * FROM foo WHERE id = 500000;
vs
WITH cte AS (
SELECT * FROM foo
)
SELECT * FROM cte WHERE id = 500000;
If calculation of the CTE is done lazily on first requirement then I would imagine that these two queries could be optimized in the same way (I think at least).
And that made me wonder... Would SQL Server be able to optimize such a query better than postgres?
Are there known differences between databases to the extent/ability they have in optimizing queries?
Which are the best and which are the worst?
sql-server postgresql optimization cte
asked 1 hour ago
Zach Smith
79611026
I don't have a clear understanding of query optimization in any database, other than that 'it happens'. Now I've just seen a "medium" post that discusses the pitfalls of using a PostgreSQL CTE in terms of optimization since the CTE is only evaluated once and that any optimizations that may be applied in terms of how the CTE is used, the database just can't apply.
But the example in the blog post seems trivial to optimize - i.e. the CTE is:
SELECT * FROM foo WHERE id = 500000;
vs
WITH cte AS (
SELECT * FROM foo
)
SELECT * FROM cte WHERE id = 500000;
If calculation of the CTE is done lazily on first requirement then I would imagine that these two queries could be optimized in the same way (I think at least).
And that made me wonder... Would SQL Server be able to optimize such a query better than postgres?
Are there known differences between databases to the extent/ability they have in optimizing queries?
Which are the best and which are the worst?
sql-server postgresql optimization cte
sql-server postgresql optimization cte
asked 1 hour ago
Zach Smith
79611026
asked 1 hour ago
Zach Smith
79611026
asked 1 hour ago
Zach Smith
79611026
asked 1 hour ago
Zach Smith
79611026
asked 1 hour ago
Zach Smith
79611026
79611026
add a comment |Â
add a comment |Â
2 Answers
2
active
oldest
votes
up vote
1
down vote
A question asking if something is "better" or "worse" is subject to personal opinions, so take this with a grain of salt. Asking about the behaviour of the query optimizer is usually extremely broad as the behave differently in complex situations compared to extremely simple queries as the one in your question.
For Postgres a CTE is (as of Postgres 11) a so called "optimization fence". That means Postgres will optimize the CTE independently of the overall query. There are cases where this results in a "better" (=faster) plan, and then there are cases where this will result in a "bad" (=slower) plan (note that recently the discussion around this design decision started again, so maybe this will change in future versions after Postgres 11).
The example in your question is an example where this approach results in a "bad" plan as the condition of the outer query is not pushed into the CTE.
SQL Server optimizes the whole query including the CTE, so for your example the execution plan in SQL Server would qualify as "better.
But I have also seen the opposite in Postgres: when moving parts of a (non-trivial) query into a CTE the resulting plan was better because operations where not pushed down which resulted in better estimates and a much "better" (=faster) execution plans. As I hardly use SQL Server, I can't tell if there are similar situations there.
answered 1 hour ago
a_horse_with_no_name
35.9k769108
add a comment |Â
up vote
1
down vote
Yes, query optimization is different not just between database vendors (Oracle, SQL Server, Postgres, MySQL, etc), but also between different versions of the same database. For example, SQL Server 2017 has different capabilities than SQL Server 2016.
answered 1 hour ago
Brent Ozar
32.6k1896224
add a comment |Â
2 Answers
2
active
oldest
votes
2 Answers
2
active
oldest
votes
active
oldest
votes
active
oldest
votes
up vote
1
down vote
A question asking if something is "better" or "worse" is subject to personal opinions, so take this with a grain of salt. Asking about the behaviour of the query optimizer is usually extremely broad as the behave differently in complex situations compared to extremely simple queries as the one in your question.
For Postgres a CTE is (as of Postgres 11) a so called "optimization fence". That means Postgres will optimize the CTE independently of the overall query. There are cases where this results in a "better" (=faster) plan, and then there are cases where this will result in a "bad" (=slower) plan (note that recently the discussion around this design decision started again, so maybe this will change in future versions after Postgres 11).
The example in your question is an example where this approach results in a "bad" plan as the condition of the outer query is not pushed into the CTE.
SQL Server optimizes the whole query including the CTE, so for your example the execution plan in SQL Server would qualify as "better.
But I have also seen the opposite in Postgres: when moving parts of a (non-trivial) query into a CTE the resulting plan was better because operations where not pushed down which resulted in better estimates and a much "better" (=faster) execution plans. As I hardly use SQL Server, I can't tell if there are similar situations there.
answered 1 hour ago
a_horse_with_no_name
35.9k769108
add a comment |Â
up vote
1
down vote
A question asking if something is "better" or "worse" is subject to personal opinions, so take this with a grain of salt. Asking about the behaviour of the query optimizer is usually extremely broad as the behave differently in complex situations compared to extremely simple queries as the one in your question.
For Postgres a CTE is (as of Postgres 11) a so called "optimization fence". That means Postgres will optimize the CTE independently of the overall query. There are cases where this results in a "better" (=faster) plan, and then there are cases where this will result in a "bad" (=slower) plan (note that recently the discussion around this design decision started again, so maybe this will change in future versions after Postgres 11).
The example in your question is an example where this approach results in a "bad" plan as the condition of the outer query is not pushed into the CTE.
SQL Server optimizes the whole query including the CTE, so for your example the execution plan in SQL Server would qualify as "better.
But I have also seen the opposite in Postgres: when moving parts of a (non-trivial) query into a CTE the resulting plan was better because operations where not pushed down which resulted in better estimates and a much "better" (=faster) execution plans. As I hardly use SQL Server, I can't tell if there are similar situations there.
answered 1 hour ago
a_horse_with_no_name
35.9k769108
add a comment |Â
up vote
1
down vote
up vote
1
down vote
A question asking if something is "better" or "worse" is subject to personal opinions, so take this with a grain of salt. Asking about the behaviour of the query optimizer is usually extremely broad as the behave differently in complex situations compared to extremely simple queries as the one in your question.
For Postgres a CTE is (as of Postgres 11) a so called "optimization fence". That means Postgres will optimize the CTE independently of the overall query. There are cases where this results in a "better" (=faster) plan, and then there are cases where this will result in a "bad" (=slower) plan (note that recently the discussion around this design decision started again, so maybe this will change in future versions after Postgres 11).
The example in your question is an example where this approach results in a "bad" plan as the condition of the outer query is not pushed into the CTE.
SQL Server optimizes the whole query including the CTE, so for your example the execution plan in SQL Server would qualify as "better.
But I have also seen the opposite in Postgres: when moving parts of a (non-trivial) query into a CTE the resulting plan was better because operations where not pushed down which resulted in better estimates and a much "better" (=faster) execution plans. As I hardly use SQL Server, I can't tell if there are similar situations there.
answered 1 hour ago
a_horse_with_no_name
35.9k769108
A question asking if something is "better" or "worse" is subject to personal opinions, so take this with a grain of salt. Asking about the behaviour of the query optimizer is usually extremely broad as the behave differently in complex situations compared to extremely simple queries as the one in your question.
For Postgres a CTE is (as of Postgres 11) a so called "optimization fence". That means Postgres will optimize the CTE independently of the overall query. There are cases where this results in a "better" (=faster) plan, and then there are cases where this will result in a "bad" (=slower) plan (note that recently the discussion around this design decision started again, so maybe this will change in future versions after Postgres 11).
The example in your question is an example where this approach results in a "bad" plan as the condition of the outer query is not pushed into the CTE.
SQL Server optimizes the whole query including the CTE, so for your example the execution plan in SQL Server would qualify as "better.
But I have also seen the opposite in Postgres: when moving parts of a (non-trivial) query into a CTE the resulting plan was better because operations where not pushed down which resulted in better estimates and a much "better" (=faster) execution plans. As I hardly use SQL Server, I can't tell if there are similar situations there.
answered 1 hour ago
a_horse_with_no_name
35.9k769108
answered 1 hour ago
a_horse_with_no_name
35.9k769108
answered 1 hour ago
a_horse_with_no_name
35.9k769108
answered 1 hour ago
a_horse_with_no_name
35.9k769108
35.9k769108
add a comment |Â
add a comment |Â
up vote
1
down vote
Yes, query optimization is different not just between database vendors (Oracle, SQL Server, Postgres, MySQL, etc), but also between different versions of the same database. For example, SQL Server 2017 has different capabilities than SQL Server 2016.
answered 1 hour ago
Brent Ozar
32.6k1896224
add a comment |Â
up vote
1
down vote
Yes, query optimization is different not just between database vendors (Oracle, SQL Server, Postgres, MySQL, etc), but also between different versions of the same database. For example, SQL Server 2017 has different capabilities than SQL Server 2016.
answered 1 hour ago
Brent Ozar
32.6k1896224
add a comment |Â
up vote
1
down vote
up vote
1
down vote
Yes, query optimization is different not just between database vendors (Oracle, SQL Server, Postgres, MySQL, etc), but also between different versions of the same database. For example, SQL Server 2017 has different capabilities than SQL Server 2016.
answered 1 hour ago
Brent Ozar
32.6k1896224
Yes, query optimization is different not just between database vendors (Oracle, SQL Server, Postgres, MySQL, etc), but also between different versions of the same database. For example, SQL Server 2017 has different capabilities than SQL Server 2016.
answered 1 hour ago
Brent Ozar
32.6k1896224
answered 1 hour ago
Brent Ozar
32.6k1896224
answered 1 hour ago
Brent Ozar
32.6k1896224
answered 1 hour ago
Brent Ozar
32.6k1896224
32.6k1896224
add a comment |Â
add a comment |Â
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f217788%2fdoes-the-quality-of-query-optimization-differ-by-database-provider%23new-answer', 'question_page');
);
Post as a guest
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
