Query optimization / automatic re-ordering of tables

From: "Verena Ruff" <lists(at)triosolutions(dot)at>
To: pgsql-novice(at)postgresql(dot)org
Subject: Query optimization / automatic re-ordering of tables
Date: 2006-07-19 12:53:37
Message-ID: 19547.86.59.55.246.1153313617.squirrel@v2830.vanager.de
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-novice

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

Responses

Browse pgsql-novice by date

  From Date Subject
Next Message Verena Ruff 2006-07-19 13:07:23 case-insensitive Index
Previous Message Devrim GUNDUZ 2006-07-19 12:35:31 Re: Installation