[prev in list] [next in list] [prev in thread] [next in thread] 

List:       postgresql-general
Subject:    Re: [HACKERS] eWeek Poll: Which database is most critical to
From:       F Harvell <fharvell () fts ! net>
Date:       2002-02-28 15:00:40
[Download RAW message or body]

On Wed, 27 Feb 2002 16:24:45 EST, Tom Lane wrote:
> F Harvell <fharvell@fts.net> writes:
> > The query plan is not going to be interested at all in
> > the literal value of the parameters and therefore will be the same for
> > any query of the same form.
> 
> Unfortunately, this is completely false.
>  
> >   For example, from above:
> 
> > SELECT shirt, color, backorder_qty FROM garments WHERE color like 
> > 'BLUE%'
> 
> >   should become something on the order of:
> 
> > SELECT shirt, color, backorder_qty FROM garments WHERE color like 
> > '{param0}%'
> 
> You managed to pick an example that's perfectly suited to demolish your
> assertion.  The query with "color like 'BLUE%'" can be optimized into an
> indexscan (using index quals of the form "color >= 'BLUE' and color <
> 'BLUF'), at least in C locale.  The parameterized query cannot be
> optimized at all, because the planner cannot know whether the
> substituted parameter string will provide a left-anchored pattern.
> What if param0 contains '_FOO' at runtime?  An indexscan will be
> useless in that case.

  Thanks for the feedback.  In the example that was used, it was
important to note that the {param0} was the string literal "BLUE" and
not the % "operator".  This IMHO ties the query to a left anchored
pattern.  I certainly do not think that the "parameter" can be
anything but a literal.  Functions and operators would very likely
affect any query plan.

  Is it true that the optimizer manipulates the literal?  It would
seem that that would require a huge amount of processing (due to
character sets, etc.).  It would appear that it would be more viable
to use a simpler optimization that does not manipulate the literal
such as an index quals of the form "color{0,4} == 'BLUE'" than to
generate a range comparison.  Of course, this is a very simple query
and I am likely missing a critical concept.

Thanks,
F Harvell




---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly
[prev in list] [next in list] [prev in thread] [next in thread]