Date: Tue, 25 Oct 2005 20:42:11 -0600
From: Michael Fuhr <mike@fuhr.org>
To: chester c young <chestercyoung@yahoo.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: broken join optimization? (8.0)
Message-ID: <20051026024211.GA98628@winnie.fuhr.org>
References: <20051025235612.20767.qmail@web54301.mail.yahoo.com>
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: <20051025235612.20767.qmail@web54301.mail.yahoo.com>

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, ie, pull a single row with each attribute's
> value is null.  I use the query (dual is a table of one row ala
> Oracle):
> 
> select m.* from dual
> left join mytable m on( false );

Have you considered "SELECT * FROM mytable LIMIT 0"?  APIs typically
allow you to find out the row structure even if no rows were returned.
In recent versions of PHP, for example, you can use pg_num_fields(),
pg_field_name(), pg_field_type(), etc., or perhaps the experimental
pg_meta_data().

> this works every time, but if mytable is big, then takes a long time. 

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.

-- 
Michael Fuhr