Re: Planner hints in Postgresql
От | Josh Berkus |
---|---|
Тема | Re: Planner hints in Postgresql |
Дата | |
Msg-id | 532773EA.4000308@agliodbs.com обсуждение исходный текст |
Ответ на | Planner hints in Postgresql (Rajmohan C <csrajmohan@gmail.com>) |
Ответы |
Re: Planner hints in Postgresql
(Vik Fearing <vik.fearing@dalibo.com>)
Re: Planner hints in Postgresql (Merlin Moncure <mmoncure@gmail.com>) |
Список | pgsql-hackers |
On 03/17/2014 01:57 PM, Martijn van Oosterhout wrote: > On Mon, Mar 17, 2014 at 01:20:47PM -0500, Merlin Moncure wrote: >> A query plan is a complicated thing that is the result of detail >> analysis of the data. I bet there are less than 100 users on the >> planet with the architectural knowledge of the planner to submit a >> 'plan'. What users do have is knowledge of the data that the database >> can't effectively gather for some reason. Looking at my query above, >> what it would need (assuming the planner could not be made to look >> through length()) would be something like: >> >> SELECT * FROM foo WHERE >> length(bar) <= 1000 WITH SELECTIVITY 0.999 >> AND length(bar) >= 2 WITH SELECTIVITY 0.999; So, if we're going to support query decorators, we might as well go all the way and just offer Oracle-style "use this index". Speaking as someone who is often called on to fix performance issues in other people's databases, I find major issues with query decorators: 1. they are impossible to maintain since they're scattered all over the application code. 2. they eventually become a barrier to upgrading, once the performance of the DB engine changes in a way that makes older query decorators crippling and/or erroneous. Because they are scattered all around the code, it then becomes a major refactoring effort to fix them. 3. There's no obvious way to collect cumulative query hints in order to supply data for database-level tuning, or for improving the postgresql query planner. 4. There's no obvious way to use query decorators with ORMs, making them useless to 95% of our users. 5. Application developers will add them without telling the DBAs, and vice-versa. Hilarity ensues. Given that, I would strongly prefer a different mechanism for ad-hoc query plan adjustment. Possible other mechanisms would include: a) ability to set selectivity for database objects, possibly including per-column selectivity (i.e. selectivity for columns a & b & f is 0.01), and save it. b) ability to execute a query using a presupplied plan. This would include ability to edit the plan using some intermediate format, like JSON or XML. c) ability to finely adjust costs of specific query operations (e.g. bitmapscan_cost = 0.02), possibly for specific database objects. d) ability to save selectivity estimates for specific expressions (e.g. "selectivity on log15 ( session_id =, log_time BETWEEN ) = 0.03". Personally, (b) is my favorite version of this feature, becuase it allows me to test the query executor itself, and it raises the bar for clobbering the query planner to people who are willing to spend a little time on it. It would also make a fantastic learning tool for learning about database planning and optimization, and might open the door for more people hacking on our planner. -- Josh Berkus PostgreSQL Experts Inc. http://pgexperts.com
В списке pgsql-hackers по дате отправления: