Re: [HACKERS] Optimizing prepared statements

2006-09-04 Thread Martijn van Oosterhout
On Mon, Sep 04, 2006 at 11:12:13AM +0700, Jeroen T. Vermeulen wrote: As I've said before, all this falls down if there is a significant cost to keeping one or two extra plans per prepared statement. You mentioned something about tracking plans. I don't know what that means, but it sounded

Re: [HACKERS] Optimizing prepared statements

2006-09-04 Thread Tom Lane
Jeroen T. Vermeulen [EMAIL PROTECTED] writes: On Sun, September 3, 2006 23:52, Tom Lane wrote: What exactly do you mean by optimize away a parameter? The way you described the mechanism, there are no parameters that are optimized away, you've merely adjusted selectivity predictions using some

Re: [HACKERS] Optimizing prepared statements

2006-09-04 Thread Jeroen T. Vermeulen
On Mon, September 4, 2006 23:03, Tom Lane wrote: Ah. I think you're confusing the spectators by using predict when you should say match. You're looking for previously generated plans that have assumed parameter values matching the current query --- saying that the plan predicts a parameter

Re: [HACKERS] Optimizing prepared statements

2006-09-04 Thread Josh Berkus
Jeroen, So with that out of the way, can anyone think of some good real-life examples of prepared statement usage that I can test against? Suggestions I've had include TPC, DBT2 (based on TPC-C), and pgbench, but what I'm really looking for is traces of invocations by real applications.

[HACKERS] Optimizing prepared statements

2006-09-03 Thread Jeroen T. Vermeulen
I've rigged up a simple simulator for the scheme I described for detecting pseudo-constant parameters to prepared statements. It withstands simple tests, and it neatly picks up cases where some parameters are pseudo-constants and others aren't--even if some of them are more pseudo while others

Re: [HACKERS] Optimizing prepared statements

2006-09-03 Thread Gregory Stark
Jeroen T. Vermeulen [EMAIL PROTECTED] writes: For now, I'll summarize some results I got from randomized input data. I used very simple traces, with 11 prepared statements, each taking a different number of parameters (0 through 10, inclusive). All calls were uniformly randomized. I used

Re: [HACKERS] Optimizing prepared statements

2006-09-03 Thread Jeroen T. Vermeulen
On Sun, September 3, 2006 18:41, Gregory Stark wrote: I'm confused, what exactly are you trying to predict? Whether each parameter will be some cached value? Or whether the cached plan was correct? That's described in more detail in a separate thread (prepared statements considered harmful).

Re: [HACKERS] Optimizing prepared statements

2006-09-03 Thread Gregory Stark
Jeroen T. Vermeulen [EMAIL PROTECTED] writes: On Sun, September 3, 2006 18:41, Gregory Stark wrote: I'm confused, what exactly are you trying to predict? Whether each parameter will be some cached value? Or whether the cached plan was correct? That's described in more detail in a separate

Re: [HACKERS] Optimizing prepared statements

2006-09-03 Thread Jeroen T. Vermeulen
On Sun, September 3, 2006 21:52, Gregory Stark wrote: I read that but apparently I misunderstood it since it would not have been doable the way I understood it. I thought you wanted the predictor bits to correspond to particular plans. If a plan was wrong then you marked it as a bad guess. I

Re: [HACKERS] Optimizing prepared statements

2006-09-03 Thread Jeroen T. Vermeulen
On Sun, September 3, 2006 23:28, Jeroen T. Vermeulen wrote: On Sun, September 3, 2006 21:52, Gregory Stark wrote: I read that but apparently I misunderstood it since it would not have been doable the way I understood it. I thought you wanted the predictor bits to correspond to particular

Re: [HACKERS] Optimizing prepared statements

2006-09-03 Thread Tom Lane
Jeroen T. Vermeulen [EMAIL PROTECTED] writes: If multiple cached plans can be applied to a given call, we prefer the one that optimizes away the most parameters. What exactly do you mean by optimize away a parameter? The way you described the mechanism, there are no parameters that are

Re: [HACKERS] Optimizing prepared statements

2006-09-03 Thread Gregory Stark
Jeroen T. Vermeulen [EMAIL PROTECTED] writes: Oh, sorry--I guess I haven't been too systematic about it. In the algorithm's current incarnation, ... Thanks, that cleared things up enormously. I'm trying to figure how it would react to some of the queries I've written in the past. In

Re: [HACKERS] Optimizing prepared statements

2006-09-03 Thread Jeroen T. Vermeulen
On Sun, September 3, 2006 23:52, Tom Lane wrote: What exactly do you mean by optimize away a parameter? The way you described the mechanism, there are no parameters that are optimized away, you've merely adjusted selectivity predictions using some assumed values. I'm using optimized away as

Re: [HACKERS] Optimizing prepared statements

2006-09-03 Thread Jeroen T. Vermeulen
On Mon, September 4, 2006 03:56, Gregory Stark wrote: Thanks, that cleared things up enormously. I'm trying to figure how it would react to some of the queries I've written in the past. In particular I'm thinking of queries like WHERE (? OR category = ?) AND (? OR cost ?) AND (? OR