pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Gaetano Mendola <mendola@bigfoot.com>
To: Valerie Schneider DSI/DEV <Valerie.Schneider@meteo.fr>
Subject: Re: [GENERAL] Tuning queries on large database
Date: Thu, 05 Aug 2004 11:53:59 +0200
Message-ID: <411203B7.8050706@bigfoot.com> (raw)
In-Reply-To: <200408050816.i758GxO21560@mu.meteo.fr>
References: <200408050816.i758GxO21560@mu.meteo.fr>

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






view thread (15+ messages)  latest in thread

Message-ID: <411203B7.8050706@bigfoot.com>
Permalink:  ../411203B7.8050706@bigfoot.com/
Also on:    postgresql.org/message-id/411203B7.8050706@bigfoot.com

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-performance@postgresql.org
  Cc: mendola@bigfoot.com, Valerie.Schneider@meteo.fr
  Subject: Re: [GENERAL] Tuning queries on large database
  In-Reply-To: <411203B7.8050706@bigfoot.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox