Re: Planner hints in Postgresql

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