Re: Pb de perf sur une grosse base

From: Valerie Schneider DSI/DEV <Valerie(dot)Schneider(at)meteo(dot)fr>
To: pgsql-fr-generale(at)postgresql(dot)org
Subject: Re: Pb de perf sur une grosse base
Date: 2004-08-04 14:13:16
Message-ID: 200408041413.i74EDGO19581@mu.meteo.fr
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-fr-generale


>Date: Wed, 04 Aug 2004 15:18:51 +0200
>From: Jean-Max Reymond <jmreymond(at)free(dot)fr>
>User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.7)
Gecko/20040616
>X-Accept-Language: en-us, en
>MIME-Version: 1.0
>To: Valerie Schneider DSI/DEV <Valerie(dot)Schneider(at)meteo(dot)fr>
>Cc: pgsql-fr-generale-subscribe(at)postgresql(dot)org
>Subject: Re: [pgsql-fr-generale] Pb de perf sur une grosse base
>Content-Transfer-Encoding: 8bit
>
>Valerie Schneider DSI/DEV wrote:
>
>> D'autre part les différentes requêtes du bench sont "simples" : pas
>> de jointures, sous-interrogation, ... et utilisent les index (testées
>> avec un explain pour en être sûre) :
>> Q1 select_court : access to about 700 rows : 1 "num_poste" et 1 mois
>> (using PK : num_poste=p1 and dat between p2 and p3)
>> Q2 select_moy : access to about 7000 rows : 10 "num_poste" et 1 mois
>> (using PK : num_poste between p1 and p1+10 and dat between p2 and p3)
>> Q3 select_long : about 250 000 rows : 2 "num_poste"
>> (using PK : num_poste in (p1,p1+2))
>> Q4 select_tres_long : about 3 millions rows : 25 "num_poste"
>> (using PK : num_poste between p1 and p1 + 25)
>
>peut tu fournir un explain de chacune des requêtes Q1, Q2, Q3 et Q4 ?
>merci,
>
>
>--
>Jean-Max Reymond
>CKR Solutions
>http://www.ckr-solutions.com

Q1 :
bench=> explain analyze select 'Q1',min(td),max(u) from data where
num_poste=1000 and dat between
(date_trunc('month',to_timestamp('31012004','ddmmyyyy')-interval '2000
days'))::timestamp and
(date_trunc('month',to_timestamp('31012004','ddmmyyyy')-interval '2000 days') +
interval '1 month' - interval '1 hour')::timestamp;


QUERY PLAN
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
----------------------------------------------------------------------
Aggregate (cost=2501.90..2501.90 rows=1 width=21) (actual
time=581.460..581.461 rows=1 loops=1)
-> Index Scan using pk_data on data (cost=0.00..2498.80 rows=619 width=21)
(actual time=92.986..579.089 rows=744 loops=1)
Index Cond: ((num_poste = 1000::numeric) AND (dat >=
(date_trunc('month'::text, (to_timestamp('31012004'::text, 'ddmmyyyy'::text) -
'2000 days'::interval)))::timestamp without time zone) AND (dat <=
(((date_trunc('month'::text, (to_timestamp('31012004'::text, 'ddmmyyyy'::text) -
'2000 days'::interval)) + '1 mon'::interval) - '01:00:00'::interval))::timestamp
without time zone))
Total runtime: 609.149 ms
(4 rows)

Q2 :
bench=> explain analyze select 'Q2',count(*) from data where num_poste between
100 and 100+10 and dat between
(date_trunc('month',to_timestamp('31012004','ddmmyyyy')-interval '3000
days'))::timestamp and
(date_trunc('month',to_timestamp('31012004','ddmmyyyy')-interval '3000 days') +
interval '1 month' - interval '1 hour')::timestamp;


QUERY PLAN
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
----------------------
Aggregate (cost=23232.05..23232.05 rows=1 width=0) (actual
time=5678.849..5678.850 rows=1 loops=1)
-> Index Scan using pk_data on data (cost=0.00..23217.68 rows=5747 width=0)
(actual time=44.408..5669.387 rows=7920 loops=1)
Index Cond: ((num_poste >= 100::numeric) AND (num_poste <=
110::numeric) AND (dat >= (date_trunc('month'::text,
(to_timestamp('31012004'::text, 'ddmmyyyy'::text) - '3000
days'::interval)))::timestamp without time zone) AND (dat <=
(((date_trunc('month'::text, (to_timestamp('31012004'::text, 'ddmmyyyy'::text) -
'3000 days'::interval)) + '1 mon'::interval) - '01:00:00'::interval))::timestamp
without time zone))
Total runtime: 5679.059 ms
(4 rows)

Q3 :
bench=> explain analyze select 'Q3',sum(rr1),count(ff) from data where num_poste
in (50,50+2);
QUERY PLAN
--------------------------------------------------------------------------------
------------------------------------------------------------------
Aggregate (cost=986770.56..986770.56 rows=1 width=17) (actual
time=75401.030..75401.031 rows=1 loops=1)
-> Index Scan using pk_data, pk_data on data (cost=0.00..985534.43
rows=247225 width=17) (actual time=35.823..74885.689 rows=250226 loops=1)
Index Cond: ((num_poste = 50::numeric) OR (num_poste = 52::numeric))
Total runtime: 75405.666 ms
(4 rows)

Q4 :
bench=> explain analyze select 'Q4',count(*) from data where num_poste between
600 and 625;
QUERY PLAN
--------------------------------------------------------------------------------
--------------------------------------------------------------
Aggregate (cost=12166763.62..12166763.62 rows=1 width=0) (actual
time=1162090.302..1162090.303 rows=1 loops=1)
-> Index Scan using pk_data on data (cost=0.00..12159021.19 rows=3096971
width=0) (actual time=94.679..1158266.561 rows=3252938 loops=1)
Index Cond: ((num_poste >= 600::numeric) AND (num_poste <=
625::numeric))
Total runtime: 1162102.217 ms
(4 rows)

********************************************************************
* Les points de vue exprimes sont strictement personnels et *
* n'engagent pas la responsabilite de METEO-FRANCE. *
********************************************************************
* Valerie SCHNEIDER Tel : +33 (0)5 61 07 81 91 *
* METEO-FRANCE / DSI/DEV Fax : +33 (0)5 61 07 81 09 *
* 42, avenue G. Coriolis Email : Valerie(dot)Schneider(at)meteo(dot)fr *
* 31057 TOULOUSE Cedex - FRANCE http://www.meteo.fr *
********************************************************************

Browse pgsql-fr-generale by date

  From Date Subject
Next Message Hervé Piedvache 2004-08-04 15:12:05 Re: Pb de perf sur une grosse base
Previous Message Jean-Max Reymond 2004-08-04 13:18:35 Re: Pb de perf sur une grosse base