Re: [HACKERS] Planner hints in Postgresql

2014-03-18 Thread Jeff Janes
On Monday, March 17, 2014, Atri Sharma atri.j...@gmail.com wrote: On Mon, Mar 17, 2014 at 10:58 PM, Stephen Frost sfr...@snowman.netjavascript:_e(%7B%7D,'cvml','sfr...@snowman.net'); wrote: * Atri Sharma (atri.j...@gmail.comjavascript:_e(%7B%7D,'cvml','atri.j...@gmail.com');) wrote:

Re: [HACKERS] Planner hints in Postgresql

2014-03-18 Thread Jeff Janes
On Monday, March 17, 2014, Tom Lane t...@sss.pgh.pa.us wrote: Claudio Freire klaussfre...@gmail.com javascript:; writes: On Mon, Mar 17, 2014 at 7:01 PM, Jim Nasby j...@nasby.net javascript:; wrote: Even better would be if the planner could estimate how bad a plan will become if we made

Re: [HACKERS] Planner hints in Postgresql

2014-03-18 Thread Claudio Freire
On Mon, Mar 17, 2014 at 8:47 PM, Tom Lane t...@sss.pgh.pa.us wrote: Claudio Freire klaussfre...@gmail.com writes: On Mon, Mar 17, 2014 at 7:01 PM, Jim Nasby j...@nasby.net wrote: Even better would be if the planner could estimate how bad a plan will become if we made assumptions that turn

Re: [HACKERS] Planner hints in Postgresql

2014-03-18 Thread Merlin Moncure
On Tue, Mar 18, 2014 at 11:53 AM, Claudio Freire klaussfre...@gmail.com wrote: On Mon, Mar 17, 2014 at 8:47 PM, Tom Lane t...@sss.pgh.pa.us wrote: Claudio Freire klaussfre...@gmail.com writes: On Mon, Mar 17, 2014 at 7:01 PM, Jim Nasby j...@nasby.net wrote: Even better would be if the

Re: [HACKERS] Planner hints in Postgresql

2014-03-18 Thread Claudio Freire
On Tue, Mar 18, 2014 at 4:48 PM, Merlin Moncure mmonc...@gmail.com wrote: That alone could improve things considerably, and statistical info could be propagated along expressions to make it possible to model uncertainty in complex expressions as well. But how would that work? I see no

Re: [HACKERS] Planner hints in Postgresql

2014-03-18 Thread Atri Sharma
That's precisely what risk estimation was about. Yeah. I would like to see the planner's cost estimates extended to include some sort of uncertainty estimate, whereupon risk-averse people could ask it to prefer low-uncertainty plans over high-uncertainty ones (the plans we typically

Re: [HACKERS] Planner hints in Postgresql

2014-03-17 Thread Atri Sharma
On Mon, Mar 17, 2014 at 9:22 PM, Rajmohan C csrajmo...@gmail.com wrote: I am implementing Planner hints in Postgresql to force the optimizer to select a particular plan for a query on request from sql input. I am having trouble in modifying the planner code. I want to create a path node of

Re: [HACKERS] Planner hints in Postgresql

2014-03-17 Thread David Johnston
Atri Sharma wrote On Mon, Mar 17, 2014 at 9:22 PM, Rajmohan C lt; csrajmohan@ gt; wrote: I am implementing Planner hints in Postgresql to force the optimizer to select a particular plan for a query on request from sql input. I am having trouble in modifying the planner code. I want to

Re: [HACKERS] Planner hints in Postgresql

2014-03-17 Thread Tom Lane
David Johnston pol...@yahoo.com writes: Need to discuss the general why before any meaningful help on the how is going to be considered by hackers. Possibly worth noting is that in past discussions, we've concluded that the most sensible type of hint would not be use this plan at all, but

Re: [HACKERS] Planner hints in Postgresql

2014-03-17 Thread Atri Sharma
On Mon, Mar 17, 2014 at 9:45 PM, Tom Lane t...@sss.pgh.pa.us wrote: David Johnston pol...@yahoo.com writes: Need to discuss the general why before any meaningful help on the how is going to be considered by hackers. Possibly worth noting is that in past discussions, we've concluded that

Re: [HACKERS] Planner hints in Postgresql

2014-03-17 Thread Tom Lane
Atri Sharma atri.j...@gmail.com writes: On Mon, Mar 17, 2014 at 9:45 PM, Tom Lane t...@sss.pgh.pa.us wrote: Possibly worth noting is that in past discussions, we've concluded that the most sensible type of hint would not be use this plan at all, but here's what to assume about the selectivity

Re: [HACKERS] Planner hints in Postgresql

2014-03-17 Thread Stephen Frost
* Atri Sharma (atri.j...@gmail.com) wrote: Isnt using a user given value for selectivity a pretty risky situation as it can horribly screw up the plan selection? Why not allow the user to specify an alternate plan and have the planner Uh, you're worried about the user given us a garbage

Re: [HACKERS] Planner hints in Postgresql

2014-03-17 Thread David Johnston
Atri Sharma wrote On Mon, Mar 17, 2014 at 9:45 PM, Tom Lane lt; tgl@.pa gt; wrote: David Johnston lt; polobo@ gt; writes: Need to discuss the general why before any meaningful help on the how is going to be considered by hackers. Possibly worth noting is that in past

Re: [HACKERS] Planner hints in Postgresql

2014-03-17 Thread Atri Sharma
On Mon, Mar 17, 2014 at 10:58 PM, Stephen Frost sfr...@snowman.net wrote: * Atri Sharma (atri.j...@gmail.com) wrote: Isnt using a user given value for selectivity a pretty risky situation as it can horribly screw up the plan selection? Why not allow the user to specify an alternate plan

Re: [HACKERS] Planner hints in Postgresql

2014-03-17 Thread Stephen Frost
* Atri Sharma (atri.j...@gmail.com) wrote: Of course, this is not a nice hack. Specifically after our discussion on IRC the other day, I am against planner hints, but if we are just discussing how it could be done, I could think of some ways which I listed. There's lots of ways to implement

Re: [HACKERS] Planner hints in Postgresql

2014-03-17 Thread Atri Sharma
The larger question to answer first is whether we want to implement something that is deterministic... How about just dropping the whole concept of hinting and provide a way for someone to say use this plan, or die trying. Maybe require it be used in conjunction with named PREPAREd

Re: [HACKERS] Planner hints in Postgresql

2014-03-17 Thread Atri Sharma
There's lots of ways to implement planner hints, but I fail to see the point in discussing how to implement something we actively don't want. +1. The original poster wanted a way to implement it as a personal project or something ( I think he only replied to me, not the entire list). Planner

Re: [HACKERS] Planner hints in Postgresql

2014-03-17 Thread Atri Sharma
On Mon, Mar 17, 2014 at 10:54 PM, Tom Lane t...@sss.pgh.pa.us wrote: Atri Sharma atri.j...@gmail.com writes: On Mon, Mar 17, 2014 at 9:45 PM, Tom Lane t...@sss.pgh.pa.us wrote: Possibly worth noting is that in past discussions, we've concluded that the most sensible type of hint would not

Re: [HACKERS] Planner hints in Postgresql

2014-03-17 Thread Merlin Moncure
On Mon, Mar 17, 2014 at 11:15 AM, Tom Lane t...@sss.pgh.pa.us wrote: David Johnston pol...@yahoo.com writes: Need to discuss the general why before any meaningful help on the how is going to be considered by hackers. Possibly worth noting is that in past discussions, we've concluded that the

Re: [HACKERS] Planner hints in Postgresql

2014-03-17 Thread Stephen Frost
* Merlin Moncure (mmonc...@gmail.com) wrote: Yeah -- the most common case I see is outlier culling where several repeated low non-deterministic selectivity quals stack reducing the row count estimate to 1. For example: SELECT * FROM foo WHERE length(bar) = 1000 AND length(bar) = 2; This is

Re: [HACKERS] Planner hints in Postgresql

2014-03-17 Thread Atri Sharma
There's a big difference between saying to the planner, Use plan X vs Here's some information describing the data supporting choosing plan X intelligently. The latter allows for better plans in the face of varied/changing data, integrates with the planner in natural way, and encourages users

Re: [HACKERS] Planner hints in Postgresql

2014-03-17 Thread Merlin Moncure
On Mon, Mar 17, 2014 at 12:57 PM, Atri Sharma atri.j...@gmail.com wrote: There's a big difference between saying to the planner, Use plan X vs Here's some information describing the data supporting choosing plan X intelligently. The latter allows for better plans in the face of

Re: [HACKERS] Planner hints in Postgresql

2014-03-17 Thread Atri Sharma
On Mon, Mar 17, 2014 at 11:50 PM, Merlin Moncure mmonc...@gmail.com wrote: On Mon, Mar 17, 2014 at 12:57 PM, Atri Sharma atri.j...@gmail.com wrote: There's a big difference between saying to the planner, Use plan X vs Here's some information describing the data supporting choosing plan X

Re: [HACKERS] Planner hints in Postgresql

2014-03-17 Thread Pavel Stehule
2014-03-17 19:35 GMT+01:00 Atri Sharma atri.j...@gmail.com: On Mon, Mar 17, 2014 at 11:50 PM, Merlin Moncure mmonc...@gmail.comwrote: On Mon, Mar 17, 2014 at 12:57 PM, Atri Sharma atri.j...@gmail.com wrote: There's a big difference between saying to the planner, Use plan X vs Here's

Re: [HACKERS] Planner hints in Postgresql

2014-03-17 Thread Tom Lane
Atri Sharma atri.j...@gmail.com writes: Wont this have scaling issues and issues over time as the data in the table changes? It can't possibly have worse problems of that sort than explicitly specifying a plan does. regards, tom lane -- Sent via pgsql-hackers

Re: [HACKERS] Planner hints in Postgresql

2014-03-17 Thread Merlin Moncure
On Mon, Mar 17, 2014 at 1:45 PM, Pavel Stehule pavel.steh...@gmail.com wrote: I don't believe so SELECTIVITY can work well too. Slow queries are usually related to some strange points in data. I am thinking so well concept should be based on validity of estimations. Some plans are based on

Re: [HACKERS] Planner hints in Postgresql

2014-03-17 Thread Jim Nasby
On 3/17/14, 12:58 PM, Stephen Frost wrote: * Merlin Moncure (mmonc...@gmail.com) wrote: Yeah -- the most common case I see is outlier culling where several repeated low non-deterministic selectivity quals stack reducing the row count estimate to 1. For example: SELECT * FROM foo WHERE

Re: [HACKERS] Planner hints in Postgresql

2014-03-17 Thread Merlin Moncure
On Mon, Mar 17, 2014 at 2:02 PM, Jim Nasby j...@nasby.net wrote: Just being able to detect that something has possibly gone wrong would be useful. We could log that to alert the DBA/user of a potential bad plan. We could even format this in such a fashion that it's suitable for emailing the

Re: [HACKERS] Planner hints in Postgresql

2014-03-17 Thread Martijn van Oosterhout
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

Re: [HACKERS] Planner hints in Postgresql

2014-03-17 Thread Jim Nasby
On 3/17/14, 3:32 PM, Merlin Moncure wrote: On Mon, Mar 17, 2014 at 2:02 PM, Jim Nasby j...@nasby.net wrote: Just being able to detect that something has possibly gone wrong would be useful. We could log that to alert the DBA/user of a potential bad plan. We could even format this in such a

Re: [HACKERS] Planner hints in Postgresql

2014-03-17 Thread Claudio Freire
On Mon, Mar 17, 2014 at 7:01 PM, Jim Nasby j...@nasby.net wrote: Even better would be if the planner could estimate how bad a plan will become if we made assumptions that turn out to be wrong. That's precisely what risk estimation was about. Something like SELECT * FROM wherever WHEN id

Re: [HACKERS] Planner hints in Postgresql

2014-03-17 Thread Jim Nasby
On 3/17/14, 2:16 PM, Merlin Moncure wrote: On Mon, Mar 17, 2014 at 1:45 PM, Pavel Stehulepavel.steh...@gmail.com wrote: I don't believe so SELECTIVITY can work well too. Slow queries are usually related to some strange points in data. I am thinking so well concept should be based on validity

Re: [HACKERS] Planner hints in Postgresql

2014-03-17 Thread Claudio Freire
On Mon, Mar 17, 2014 at 7:09 PM, Jim Nasby j...@nasby.net wrote: On 3/17/14, 2:16 PM, Merlin Moncure wrote: On Mon, Mar 17, 2014 at 1:45 PM, Pavel Stehulepavel.steh...@gmail.com wrote: I don't believe so SELECTIVITY can work well too. Slow queries are usually related to some strange

Re: [HACKERS] Planner hints in Postgresql

2014-03-17 Thread Jim Nasby
On 3/17/14, 5:07 PM, Claudio Freire wrote: On Mon, Mar 17, 2014 at 7:01 PM, Jim Nasby j...@nasby.net mailto:j...@nasby.net wrote: Even better would be if the planner could estimate how bad a plan will become if we made assumptions that turn out to be wrong. That's precisely what risk

Re: [HACKERS] Planner hints in Postgresql

2014-03-17 Thread Jim Nasby
On 3/17/14, 5:12 PM, Claudio Freire wrote: On Mon, Mar 17, 2014 at 7:09 PM, Jim Nasby j...@nasby.net mailto:j...@nasby.net wrote: On 3/17/14, 2:16 PM, Merlin Moncure wrote: On Mon, Mar 17, 2014 at 1:45 PM, Pavel Stehulepavel.stehule@gmail.__com mailto:pavel.steh...@gmail.com

Re: [HACKERS] Planner hints in Postgresql

2014-03-17 Thread Josh Berkus
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

Re: [HACKERS] Planner hints in Postgresql

2014-03-17 Thread Vik Fearing
On 03/17/2014 06:28 PM, Stephen Frost wrote: a value passed in as part of the query, with a high likelihood of users figuring out how to use it to say use my plan forever and always.. Last time this came up, I said on irc that if we ever do implement hints, I'd like them to be tied to a major

Re: [HACKERS] Planner hints in Postgresql

2014-03-17 Thread Vik Fearing
On 03/17/2014 11:15 PM, Josh Berkus wrote: 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

Re: [HACKERS] Planner hints in Postgresql

2014-03-17 Thread Claudio Freire
On Mon, Mar 17, 2014 at 7:12 PM, Jim Nasby j...@nasby.net wrote: On 3/17/14, 5:07 PM, Claudio Freire wrote: On Mon, Mar 17, 2014 at 7:01 PM, Jim Nasby j...@nasby.net mailto: j...@nasby.net wrote: Even better would be if the planner could estimate how bad a plan will become if we made

Re: [HACKERS] Planner hints in Postgresql

2014-03-17 Thread Merlin Moncure
On Mon, Mar 17, 2014 at 5:15 PM, Josh Berkus j...@agliodbs.com wrote: 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

Re: [HACKERS] Planner hints in Postgresql

2014-03-17 Thread Tom Lane
Claudio Freire klaussfre...@gmail.com writes: On Mon, Mar 17, 2014 at 7:01 PM, Jim Nasby j...@nasby.net wrote: Even better would be if the planner could estimate how bad a plan will become if we made assumptions that turn out to be wrong. That's precisely what risk estimation was about.

Re: [HACKERS] Planner hints in Postgresql

2014-03-17 Thread Atri Sharma
On Tue, Mar 18, 2014 at 12:46 AM, Merlin Moncure mmonc...@gmail.com wrote: On Mon, Mar 17, 2014 at 1:45 PM, Pavel Stehule pavel.steh...@gmail.com wrote: I don't believe so SELECTIVITY can work well too. Slow queries are usually related to some strange points in data. I am thinking so well