Query optimization / automatic re-ordering of tables

Hi,

I have a question about query optimization.

There is this query, which is really slow:

SELECT firma_name_1, firma_name_2, pers_anrede, pers_titel, pers_vorname,
pers_nachname,
pers_titel_nach_name, pers_stand_position, adr_strasse, adr_land,
adr_plz, adr_ort, adr_strasse,
adr_land, adr_plz, adr_ort, kundepersonhc.pers_id,
kundefirmahc.firma_id, stand_id, pershc_titel_lang
FROM kundefirmahc
LEFT OUTER JOIN standorthc ON kundefirmahc.firma_id=stand_firma_id
LEFT OUTER JOIN adresse ON stand_adr_id=adr_id
LEFT OUTER JOIN personstandort ON standorthc.stand_id=pers_stand_stand_id
LEFT OUTER JOIN kundepersonhc ON pers_stand_pers_id=kundepersonhc.pers_id
WHERE ( UPPER(kundepersonhc.pers_nachname) LIKE UPPER('me%') )
LIMIT 20;

If I change it to tis (only the order of the tables are changed), it works
quite good:

SELECT firma_name_1, firma_name_2, pers_anrede, pers_titel, pers_vorname,
pers_nachname,
pers_titel_nach_name, pers_stand_position, adr_strasse, adr_land,
adr_plz, adr_ort, adr_strasse,
adr_land, adr_plz, adr_ort, kundepersonhc.pers_id,
kundefirmahc.firma_id, stand_id ,pershc_titel_lang
FROM kundepersonhc
LEFT OUTER JOIN personstandort ON kundepersonhc.pers_id=pers_stand_pers_id
LEFT OUTER JOIN standorthc ON pers_stand_stand_id=stand_id
LEFT OUTER JOIN kundefirmahc ON kundefirmahc.firma_id=stand_firma_id
LEFT OUTER JOIN adresse ON stand_adr_id=adr_id
WHERE ( UPPER(kundepersonhc.pers_nachname) LIKE UPPER('me%') )
LIMIT 20;

The WHERE clause is created dynamically and may contain fields of each
table which is part of the join, so I can't just change the order of the
tables in my code to get the best result. Is there a way to make those
optimizations (re-ordering of tables) automatically?

Regards,
Verena

"Verena Ruff" <lists(at)triosolutions(dot)at> writes:
> The WHERE clause is created dynamically and may contain fields of each
> table which is part of the join, so I can't just change the order of the
> tables in my code to get the best result. Is there a way to make those
> optimizations (re-ordering of tables) automatically?

CVS HEAD (PG 8.2 to be) is reasonably bright about re-ordering left
joins, but no current release tries to do it at all...

regards, tom lane

Tom Lane wrote:
> CVS HEAD (PG 8.2 to be) is reasonably bright about re-ordering left
> joins, but no current release tries to do it at all...
Thanks for this info, I'll have a look on the upcoming version.
Regards,
Verena Ruff