Re: another index question

Tom Lane wrote:

> OK, indeed this is a bug --- the planner has a bad interaction for LIMIT
> clauses used in "IN" subselects. Will fix.
>
> You could avoid the bug, and get a better plan at the top level anyway,
> by writing
>
> SELECT frm,rcpt,subject FROM mesg_headers WHERE mesgnum = (SELECT mesgnum
> FROM mesg_headers ORDER BY mesgnum DESC LIMIT 1);
>
> Since you know the subselect is going to produce exactly one tuple,
> there's no need to use IN.

I don't know if this is a generic database issue, an SQL one, of pgsql's;
but wasn't relational invented so that we shouldn't need to fine tune
access paths?

More to the point, shouldn't the statistics on tables plus the syntax be
enough for the planner to get this (not so complex) query a good plan,
even if not optimal?

And if needed shouldn't we get away with hints or something like that
instead of rewriting?

Sorry for the basic questions, I'm trying to learn. No criticism of
anyone's work intended, just a general question.

--
_
/ \ Leandro Guimarães Faria Corsetti Dutra +55 (11) 3040 8913
\ / Amdocs at Bell Canada +1 (514) 786 87 47
X Support Center, São Paulo, Brazil mailto:adbaamd(at)bell(dot)ca
/ \ http://terravista.pt./Enseada/1989/ mailto:leandrod(at)amdocs(dot)com