From: Cédric Dufour (Cogito Ergo Soft) <cedric.dufour@cogito-ergo-soft.com>
To: Milosz Krajewski <Milosz.Krajewski@FinSkog.com.pl>
To: 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> (raw)
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.
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: cedric.dufour@cogito-ergo-soft.com, Milosz.Krajewski@FinSkog.com.pl
Subject: Re: How to optimize SQL query ?
In-Reply-To: <NDBBIFNBODNADCAOFDOAIEJFCDAA.cedric.dufour@cogito-ergo-soft.com>
* 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