3 ms·

You can't write query plans directly because you would have to manually take into account a number of factors that the query planner considers for you automatic

You can't write query plans directly because you would have to manually take into account a number of factors that the query planner considers for you automatically, such as size of the table, statistics on the distribution of values, whether there are indexes that could be used to speed the query, and so on. Some SQL dialects (like Microsoft's T-SQL) do give you some ability to influence the decisions the query planner makes, though, like forcing it to use specific indexes, or forcing it to use scans or seeks.

marcosdumay 6y ago
And then comes Oracle and insists on applying equality filters first, because, duh, they are fast, and never take any of those other details into consideration. Honestly, Postgres does it right - you can enforce your query plan to any level of detail you want.
- paulryanrogers 6y ago
  Does PostgreSQL have plan hints now? I thought they were opposed to them for fear they become unmaintainable and hard to read.
  - lfittl 6y ago
    If you really want to, the pg_hint_plan extension can be used for this - though I would use it very sparingly, if at all: https://pghintplan.osdn.jp/pg_hint_plan.html https://pghintplan.osdn.jp/pg_hint_plan.html (available on some cloud providers as well)
- yen223 6y ago
  How do you do that? Postgres doesn't even offer a way to enforce that specific indexes get used during a query as far as I know.
perl4ever 6y ago
As a practical matter, when you can't control the optimizer or affect how the DBAs configure things, you break your huge query into multiple ones with temporary tables. This is from the perspective of trying to get queries to run in 5 minutes instead of 30 minutes instead of hours or days or forever, not brief transactions measured in milliseconds. And it's not something I figured out on my own, but by paying attention to the guy who never talked but was consistently 10x faster in producing reports than anyone. The thing you should not do, that I also saw people do, is use procedural PL/SQL or T-SQL to process things in a loop - that can be orders of magnitude slower.

marcosdumay 6y ago

And then comes Oracle and insists on applying equality filters first, because, duh, they are fast, and never take any of those other details into consideration. Honestly, Postgres does it right - you can enforce your query plan to any level of detail you want.

paulryanrogers 6y ago
Does PostgreSQL have plan hints now? I thought they were opposed to them for fear they become unmaintainable and hard to read.
- lfittl 6y ago
  If you really want to, the pg_hint_plan extension can be used for this - though I would use it very sparingly, if at all: https://pghintplan.osdn.jp/pg_hint_plan.html https://pghintplan.osdn.jp/pg_hint_plan.html (available on some cloud providers as well)
yen223 6y ago
How do you do that? Postgres doesn't even offer a way to enforce that specific indexes get used during a query as far as I know.

perl4ever 6y ago

As a practical matter, when you can't control the optimizer or affect how the DBAs configure things, you break your huge query into multiple ones with temporary tables. This is from the perspective of trying to get queries to run in 5 minutes instead of 30 minutes instead of hours or days or forever, not brief transactions measured in milliseconds. And it's not something I figured out on my own, but by paying attention to the guy who never talked but was consistently 10x faster in producing reports than anyone. The thing you should not do, that I also saw people do, is use procedural PL/SQL or T-SQL to process things in a loop - that can be orders of magnitude slower.