Re: broken join optimization? (8.0)

Michael Fuhr <mike@fuhr.org> writes:
> On Tue, Oct 25, 2005 at 04:56:11PM -0700, chester c young wrote:
>> in php (for example) it's frequently nice to get the structure of a
>> table without any data,

> Have you considered "SELECT * FROM mytable LIMIT 0"?

Indeed.

> I see the same behavior in the latest 8.1beta code.  Maybe one of
> the developers will comment on whether optimizing that is a simple
> change, a difficult change, not worth changing because few people
> find a use for it, or a behavior that can't be changed because of
> something we're not considering.

Not worth changing --- why should we expend cycles (even if it only
takes a few, which isn't clear to me offhand) on every join query, to
detect what's simply a brain-dead way of finding out table structure?
I can't think of any realistic scenarios for a constant-false join
clause.

The relevant bit of code is in initsplan.c:
   /*    * If the clause is variable-free, we force it to be evaluated at its    * original syntactic level.  Note that
thisshould not happen for    * top-level clauses, because query_planner() special-cases them.  But it    * will happen
forvariable-free JOIN/ON clauses.  We don't have to be    * real smart about such a case, we just have to be correct.
*/   if (bms_is_empty(relids))       relids = qualscope;
 

Possibly you could get the planner to generate a gating Result node for
such a case, the way it does for constant-false top level WHERE clauses,
but I really doubt it's worth any extra cycles at all to make this
happen.  The proposed example is quite unconvincing ... why would anyone
want to depend on the existence of a "dual" table rather than LIMIT 0?
        regards, tom lane