Re: Query planner isn't using my indices

On Tue, 8 Jan 2002, Stephan Szabo wrote:

> See past discussions, you'll need to either explicitly cast the
> constant to bigint or quote it, because otherwise it prematurely
> casts the constant into an int4.

But isn't that the point? The fact that it casts it to int4 isn't the
problem. The fact that int4 and int8 *should* be compatible fields *is*.
You'd think the query optimizer would do this:

"Hmmm, I have a field composed of numbers. Are there any indexes
corresponding to this field? Are the types compatible? They're pretty
close, let me just recast this... there."

Oracle does this, Mysql does this, Sybase does this... why must Postgres's
query optimizer assume all types are spot-on identical? Similarly, why
do I have to cast my char column to text when doing uncasted quoted-string
concatenation? Quoted strings are character data, and char columns are
character data, right? Wouldn't it logically follow that the
two types are compatible? Unless for some reason the database engine is
mapping the database column types directly to C types, there is no
reason for this.

Postgres's type-checking abilities are sorely lacking to such a degree
that it not only hurts database performance unless the DBA writes every
damn application, but it forces any application to be postgres specific
due to all of the unnecessary casting.

Doesn't this bother anyone? Isn't the query optimizer important enough to
be made as "smart" as possible, since it affects the performance of the
entire database? Should a company hosting postgresql services really be
held ransom by customers who write "bad" queries because they don't know
every little quirk of the database? Bueller? McFly?

Can anyone explain this to me? Anyone at all? I mean, what logical
reason is there to leave the database so crippled?

--
+-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-+
| Shaun M. Thomas INN Database Programmer |
| Phone: (309) 743-0812 Fax : (309) 743-0830 |
| Email: sthomas(at)townnews(dot)com AIM : trifthen |
| Web : www.townnews.com |
| |
| "Most of our lives are about proving something, either to |
| ourselves or to someone else." |
| -- Anonymous |
+-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-+