Thread: broken join optimization? (8.0)
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 );
this works every time, but if mytable is big, then takes a long time.
needed to rewrite the query to:
select m.* from dual
left join (select * from mytable limit 1) m on( false );
this works as it should - pulls empty row but fast.
it seems to me that a full table scan should not be necessary if the
join condition is false.
__________________________________
Start your day with Yahoo! - Make it your home page!
http://www.yahoo.com/r/hs
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
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
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 ); Out of curiosity, why do it this way? Does "rownum" not get set if there are no rows returned? Actually, even if it doesn't why not use: SELECT * FROM mytable WHERE true=false Surely your client interface returns the types/column-names then? It should - that's a set of 0 rows. -- Richard Huxton Archonet Ltd
> Tom Lane <tgl@sss.pgh.pa.us> wrote: >> 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 > >> table without any data, > > > Have you considered "SELECT * FROM mytable LIMIT 0"? > > Indeed. i think i misled: the goal is to retrieve _one_ row where the value of each attribute is null. this can be done laborously using meta data, but is done quite niftily using a left join against one row. > > 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? again, the goal is a quick way to retrieve one row from a table where each attribute value is null, NOT to get the table structure. > I can't think of any realistic scenarios for a constant-false join > clause. i would like a better idea on how to retrieve one row from a table where the value of each attribute is null - i felt this a perfectly good use of sql. __________________________________________________ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com
chester c young <chestercyoung@yahoo.com> writes:
> i think i misled: the goal is to retrieve _one_ row where the value of
> each attribute is null.
Er, what for? There's no data content in that, by definition. Why not
retrieve zero rows and look at the metadata anyway?
regards, tom lane
> Tom Lane <tgl@sss.pgh.pa.us> wrote: > chester c young <chestercyoung@yahoo.com> writes: > > i think i misled: the goal is to retrieve _one_ row where the value > of each attribute is null. > > Er, what for? There's no data content in that, by definition. Why > not retrieve zero rows and look at the metadata anyway? > with a form that is used for CRUD, values are filled in from a record (either an object or array). when creating, you want an empty record so that form.item values are set to null. makes for much easier programming and ensures all variables are defined. retrieving the metadata and then creating the record seems like a lot of work when the whole thing can be done with one select (which would needed in any case to get the metadata). __________________________________________________ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com