From: =?iso-8859-2?Q?C=E9dric_Dufour_=28Cogito_Ergo_Soft=29?=
	<cedric.dufour@cogito-ergo-soft.com>
To: "Milosz Krajewski" <Milosz.Krajewski@FinSkog.com.pl>,
	<pgsql-sql@postgresql.org>
Subject: Re: How to optimize SQL query ?
Date: Fri, 2 Aug 2002 15:17:29 +0200
Message-ID: <NDBBIFNBODNADCAOFDOAIEJFCDAA.cedric.dufour@cogito-ergo-soft.com>
MIME-Version: 1.0
Content-Type: text/plain;
	charset="iso-8859-2"
Content-Transfer-Encoding: 7bit
Importance: Normal
In-Reply-To: <3D455635.75B4D75D@finskog.com.pl>

> -----Original Message-----
> From: pgsql-sql-owner@postgresql.org
> [mailto:pgsql-sql-owner@postgresql.org]On Behalf Of Milosz Krajewski
> Sent: Monday, July 29, 2002 16:50
> To: pgsql-sql@postgresql.org
> Subject: [SQL] How to optimize SQL query ?
>
>
> 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);

AVOID: indexes (which you should have defined on primary keys [implicitely
defined by PostgreSQL] and foreign keys [must be defined explicitely]) are
not used

> or:
>   select * from cities, temp_cities tmp
>     where cities.id = tmp.id_city;

BETTER ;-)

> 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 ?

Use the explicit JOIN syntax and join each table one after another in the
order you feel is the more adequate for your query. PostgreSQL will respect
this order.