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

Re: eWeek Poll: Which database is most critical to

From: "Dann Corbit" <DCorbit(at)connx(dot)com>
To: "Tom Lane" <tgl(at)sss(dot)pgh(dot)pa(dot)us>, "F Harvell" <fharvell(at)fts(dot)net>
Cc: "Neil Conway" <nconway(at)klamath(dot)dyndns(dot)org>,<pgsql-hackers(at)postgresql(dot)org>
Subject: Re: eWeek Poll: Which database is most critical to
Date: 2002-02-27 21:33:40
Message-ID: D90A5A6C612A39408103E6ECDD77B82920CC29@voyager.corporate.connx.com (view raw or flat)
Thread:
Lists: pgsql-hackers
-----Original Message-----
From: Tom Lane [mailto:tgl(at)sss(dot)pgh(dot)pa(dot)us]
Sent: Wednesday, February 27, 2002 1:25 PM
To: F Harvell
Cc: Dann Corbit; Neil Conway; pgsql-hackers(at)postgresql(dot)org
Subject: Re: [HACKERS] eWeek Poll: Which database is most critical to 


F Harvell <fharvell(at)fts(dot)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.

In general, Postgres' query plans *do* depend on the values of
constants, and it's not always possible to produce an equally good plan
that doesn't assume anything about constants.  This is why I think it's
a lousy idea for the system to try to automatically abstract a
parameterized query plan from the actual queries it sees.  On the other
hand, an application programmer will have a very good idea of which
parts of a repeated query are really constant and which are parameters.
So what we really need is preparable parameterized queries, wherein the
application tells us what to parameterize, rather than having to guess
about it.
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
Using the data to enhance the plan is quite a brilliant strategy.
I was not aware that PostgreSQL could do that.

Rdb has a very nice feature -- it allows you to *edit* the plan.
Obviously, you can get some real disasters that way, but for advanced
users, it is very nice.
<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<

pgsql-hackers by date

Next:From: Teodor SigaevDate: 2002-02-27 21:41:20
Subject: Re: Arrays vs separate system catalogs
Previous:From: Tom LaneDate: 2002-02-27 21:24:45
Subject: Re: eWeek Poll: Which database is most critical to

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