Message-ID: <3D455635.75B4D75D@finskog.com.pl>
Date: Mon, 29 Jul 2002 16:50:29 +0200
From: Milosz Krajewski <Milosz.Krajewski@FinSkog.com.pl>
Organization: Fin Skog
MIME-Version: 1.0
To: pgsql-sql@postgresql.org
Subject: How to optimize SQL query ?
Content-Type: text/plain; charset=iso-8859-2
Content-Transfer-Encoding: 7bit

How to optimize query or just force postgre to do it my way ?

Example:
  table continets (
    id numeric, ..., active numeric
  );

  table countries (
    id numeric, id_continent numeric, ..., active numeric
  );

  table cities (
    id numeric, id_country numeric, ..., active numeric
  );

relations:
  cities.id_county are in countries.id
  countries.id_continent are on continents.id

Query:
  table temp_cities (
    id_city numeric;
  );


temp_cities is temp table which holds few (~20) id of cities, to
show them.

so:
  select * from cities 
    where cities.id in (select id_city from temp_cities);
or:
  select * from cities, temp_cities tmp
    where cities.id = tmp.id_city;
works fine.

But the problem starts here:

  select * from cities, coutries, continets
    where 
      (cities.id in (select id_city from temp_cities)) and
      (cities.id_county = countries.id) and
      (countries.id_continent = continents.id) and
      (cities.active = 1) and (coutries.active = 1) and
      (continents.active = 1)

(active means is row active or archive, many of them are active,
but I have to check it)

Posgre is planning it like this:
  joins cities with coutries
  joins countries with continents
  selects active
  filtering with cities.id (with temp_cities)

If I could force it to filter cities.id first 
(I can do this with Oracle by changing 
"select id_city from temp_cities" to 
"select id_city from temp_cities group by id_city")
it will work much (1000x) faster.

Can I force postgre do it my way ?
-- 
[ Milosz "Krashan" Krajewski ][ mail: vilge@mud.org.pl, UIN: 1319535 ]
[ inet: Vilge, Vilgefortz    ][ www: http://www.ds2.pg.gda.pl/~krash ]