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