Re: Tuning queries on large database

>       sort_mem = 50000

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 4: Don't 'kill -9' the postmaster