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
withcities.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 ]
