From: Milosz Krajewski <Milosz.Krajewski@FinSkog.com.pl>
To: pgsql-sql@postgresql.org
Subject: How to optimize SQL query ?
Date: Mon, 29 Jul 2002 16:50:29 +0200
Message-ID: <3D455635.75B4D75D@finskog.com.pl> (raw)
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 ]
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: Milosz.Krajewski@FinSkog.com.pl
Subject: Re: How to optimize SQL query ?
In-Reply-To: <3D455635.75B4D75D@finskog.com.pl>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox