Re: [PERFORM] 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