Skip site navigation (1) Skip section navigation (2)

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 (view raw or flat)
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

pgsql-novice by date

Next:From: Verena RuffDate: 2006-07-19 13:07:23
Subject: case-insensitive Index
Previous:From: Devrim GUNDUZDate: 2006-07-19 12:35:31
Subject: Re: Installation

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group