Re: [GENERAL] [PERFORM] Tuning queries on large database

2004-08-05 Thread Mark Kirkwood 
I am guessing that Oracle can satisfy Q4 entirely via index access, 
whereas Pg has to visit the table as well.

Having said that, a few partial indexes may be worth trying out on 
data.num_poste (say 10 or so), this won't help the table access but 
could lower the index cost. If you combine this with loading the data in 
num_poste order (or run CLUSTER), you may get closer to Oracle's time 
for this query.

regards
Mark
Valerie Schneider DSI/DEV wrote:
For my different queries, it's better but less performant than oracle :
oracle  PG yesterday(numeric)   PG today(integer/real)
Q4  28s 17m20s  6m47s

Q4 : bench=> explain analyze select 'Q4',count(*) from data where num_poste 
between 600 and 625;
QUERY PLAN  

Aggregate  (cost=14086174.57..14086174.57 rows=1 width=0) (actual 
time=428235.024..428235.025 rows=1 loops=1)
  ->  Index Scan using pk_data on data  (cost=0.00..14076910.99 rows=3705431 
width=0) (actual time=45.283..424634.826 rows=3252938 loops=1)
Index Cond: ((num_poste >= 600) AND (num_poste <= 625))
Total runtime: 428235.224 ms
(4 rows)

Thanks for all, Valerie.
 

---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

Re: [GENERAL] [PERFORM] Tuning queries on large database

2004-08-05 Thread Valerie Schneider DSI/DEV 
  

 Aggregate  (cost=14086174.57..14086174.57 rows=1 width=0) (actual 
time=428235.024..428235.025 rows=1 loops=1)
   ->  Index Scan using pk_data on data  (cost=0.00..14076910.99 rows=3705431 
width=0) (actual time=45.283..424634.826 rows=3252938 loops=1)
 Index Cond: ((num_poste >= 600) AND (num_poste <= 625))
 Total runtime: 428235.224 ms
(4 rows)

Thanks for all, Valerie.

>X-Original-To: [EMAIL PROTECTED]
>X-Authentication-Warning: houston.familyhealth.com.au: chriskl owned process 
doing -bs
>Date: Wed, 4 Aug 2004 21:21:51 +0800 (WST)
>From: Christopher Kings-Lynne <[EMAIL PROTECTED]>
>To: Valerie Schneider DSI/DEV <[EMAIL PROTECTED]>
>Cc: [EMAIL PROTECTED], <[EMAIL PROTECTED]>
>Subject: Re: [GENERAL] [PERFORM] Tuning queries on large database
>MIME-Version: 1.0
>X-Virus-Scanned: by amavisd-new at hub.org
>X-Spam-Status: No, hits=0.0 tagged_above=0.0 required=5.0 tests=
>X-Spam-Level: 
>X-Mailing-List: pgsql-general
>
>>  sort_mem = 5
>
>That is way, way too large.  Try more like 5000 or lower.
>
>>  num_poste  | numeric(9,0)| not null
>
>For starters numerics are really, really slow compared to integers.  Why
>aren't you using an integer for this field since youhave '0' decimal
>places.
>
>>  schema | relfilenode |  table   |   index|  reltuples  |   size
>> 
+-+--++-+--
>>  public |   125615917 | data || 1.25113e+08 | 
72312040
>>  public |   251139049 | data | i_data_dat | 1.25113e+08 |  
2744400
>>  public |   250870177 | data | pk_data| 1.25113e+08 |  
4395480
>>
>> My first remark is that the table takes a lot of place on disk, about
>> 70 Gb, instead of 35 Gb with oracle.
>
>Integers will take a lot less space than numerics.
>
>> The different queries of the bench are "simple" queries (no join,
>> sub-query, ...) and are using indexes (I "explained" each one to
>> be sure) :
>> Q1 select_court : access to about 700 rows  : 1 "num_poste" and 1 month
>>  (using PK : num_poste=p1  and dat between p2 and p3)
>> Q2 select_moy   : access to about 7000 rows : 10 "num_poste" and 1 month
>>  (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)
>>
>> The result is that for "short queries" (Q1 and Q2) it runs in a few
>> seconds on both Oracle and PG. The difference becomes important with
>> Q3 : 8 seconds with oracle
>>  80 sec with PG
>> and too much with Q4 : 28s with oracle
>>17m20s with PG !
>>
>> Of course when I run 100 or 1000 parallel queries such as Q3 or Q4,
>> it becomes a disaster !
>
>Please reply with the EXPLAIN ANALYZE output of these queries so we can
>have some idea of how to help you.
>
>Chris
>
>
>
>---(end of broadcast)---
>TIP 5: Have you checked our extensive FAQ?
>
>   http://www.postgresql.org/docs/faqs/FAQ.html




*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/DEVFax : +33 (0)5 61 07 81 09 *
* 42, avenue G. CoriolisEmail : [EMAIL PROTECTED] *
* 31057 TOULOUSE Cedex - FRANCE http://www.meteo.fr*



---(end of broadcast)---
TIP 8: explain analyze is your friend