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: [EMAIL PROTECTED], UIN: 1319535 ]
[ inet: Vilge, Vilgefortz ][ www: http://www.ds2.pg.gda.pl/~krash ]
---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
