Skip site navigation (1) Skip section navigation (2)

Re: Avoiding bad prepared-statement plans.

From: Alex Hunsaker <badalex(at)gmail(dot)com>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: Robert Haas <robertmhaas(at)gmail(dot)com>, Jeroen Vermeulen <jtv(at)xs4all(dot)nl>, Greg Stark <gsstark(at)mit(dot)edu>, Bart Samwel <bart(at)samwel(dot)tk>, Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>, pgsql-hackers(at)postgresql(dot)org
Subject: Re: Avoiding bad prepared-statement plans.
Date: 2010-02-26 06:29:41
Message-ID: 34d269d41002252229o2381fa19wbd898db756709d3a@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-hackers
On Thu, Feb 25, 2010 at 22:11, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Alex Hunsaker <badalex(at)gmail(dot)com> writes:
> Uh, no, this isn't about saving either parse time or bandwidth.
> The discussion is about when to expend more planning time in hopes
> of getting better plans.

This is what im tripping over:

> > Bruce's suggestion that we should provide some user control over whether we plan at bind time or execute time

Let me see if I can sum up what I was trying to say:

Prepared plans + bind plan (what we have now):
  Use: when you have a query that takes a long time to plan
  Problems: if you use parameters you might no get a good plan
  Solution: if you have stable parameters dont pass them as such, inline them
  Better:  If we could figure out and make  we could make better plans
on the fly and use them

[ aka if you have a good driver you can easily control if its a
prepared statement or not without changing how you quote or inline
your sql ]

Prepared plans + exec plan (new guc/ protocol thing):
  Use: not quite sure
  Problems: slow because it would replan every time
  Solutions: use a prepared plan with the appropriate things not
parametrized...?

[ aka we already have this, its called dont use a prepared statement ]

Whats the benefit of prepare + exec plan vs just inlining?  What do
you save?  Some parse time?  Some bandwidth? Yeah if its a smart knob
that does things like "Oh, I see I have an mvc for that param, ill
give you a better plan".  OK, But the knob no longer means plan at
execute time.  It more in the realm of what Tom is suggesting IMHO.
Anyway I feel like im probably just violently agreeing.

In response to

Responses

pgsql-hackers by date

Next:From: Mark MielkeDate: 2010-02-26 08:13:31
Subject: Re: Avoiding bad prepared-statement plans.
Previous:From: Gokulakannan SomasundaramDate: 2010-02-26 06:19:12
Subject: Re: A thought on Index Organized Tables

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group