PostgreSQL SQL - broken join optimization? (8.0)

Author

broken join optimization? (8.0)

chester c young

2005-10-27, 8:08 am

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

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql
.org so that your
message can get through to the mailing list cleanly

Michael Fuhr

2005-10-27, 8:08 am

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

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org

Tom Lane

2005-10-27, 8:08 am

Michael Fuhr <mike@fuhr.org> writes:
> On Tue, Oct 25, 2005 at 04:56:11PM -0700, chester c young wrote:
[color=darkred]
> 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 this should not happen for
* top-level clauses, because query_planner() special-cases them. But it
* will happen for variable-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

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql
.org so that your
message can get through to the mailing list cleanly

Richard Huxton

2005-10-27, 8:08 am

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

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org

chester c young

2005-10-27, 8:09 am

> Tom Lane <tgl@sss.pgh.pa.us> wrote:
>
>
> 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.

>
> 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

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql
.org so that your
message can get through to the mailing list cleanly

Tom Lane

2005-10-27, 8:09 am

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

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

chester c young

2005-10-27, 8:09 am

> Tom Lane <tgl@sss.pgh.pa.us> wrote:

> chester c young <chestercyoung@yahoo.com> writes:
> 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

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

Sponsored Links