Re: [GENERAL] Tuning queries on large database

Valerie Schneider DSI/DEV wrote:

 > Hi,
 > I 've decreased the sort_mem to 5000 instead of 50000.
 > I recreated ma table using integer and real types instead of
 > numeric : the result is very improved for the disk space :
 >
 >  schema | relfilenode |      table       |   index    |  reltuples  |   size
 > --------+-------------+------------------+------------+-------------+----------
 >  public |   253442696 | data             |            | 1.25113e+08 | 29760016
 >  public |   378639579 | data             | i_data_dat | 1.25113e+08 |  2744400
 >  public |   378555698 | data             | pk_data    | 1.25113e+08 |  3295584
 >
 > so it takes about 28 Gb instead of 68 Gb !
 >
 > For my different queries, it's better but less performant than oracle :
 >
 >     oracle    PG yesterday(numeric)    PG today(integer/real)
 > Q1    <1s       <1s              <1s
 > Q2     3s        8s               4s
 > Q3     8s     1m20s              27s
 > Q4    28s    17m20s            6m47s


Are you using the same disk for oracle and PG ?

Could you post your actual postgresql.conf ?
Try also to mount your partition with the option: noatime
and try again.


Regards
Gaetano Mendola