pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Tuning queries on large database
15+ messages / 8 participants
[nested] [flat]

* Tuning queries on large database
@ 2004-08-04 12:44 Valerie Schneider DSI/DEV <Valerie.Schneider@meteo.fr>
  2004-08-04 13:21 ` Re: [PERFORM] Tuning queries on large database Christopher Kings-Lynne <chriskl@familyhealth.com.au>
  2004-08-04 13:26 ` Re: Tuning queries on large database Rod Taylor <rbt@rbt.ca>
  2004-08-04 15:34 ` Re: Tuning queries on large database Gaetano Mendola <mendola@bigfoot.com>
  0 siblings, 3 replies; 15+ messages in thread

From: Valerie Schneider DSI/DEV @ 2004-08-04 12:44 UTC (permalink / raw)
  To: pgsql-performance; pgsql-general@postgresql.org; +Cc: Valerie.Schneider@meteo.fr

Hi,

I have some problem of performance on a PG database, and I don't
know how to improve. I Have two questions : one about the storage
of data, one about tuning queries. If possible !

My job is to compare Oracle and Postgres. All our operational databases
have been running under Oracle for about fifteen years. Now I try to replace
Oracle by Postgres.

I have a test platform under linux (Dell server, 4 Gb RAM, bi-processor,
Linux Red Hat 9 (2.4.20-31.9)) with 2 databases, 1 with Oracle
(V8i or V9i it's quite the same), 1 with PG (7.4.2). Both databases
have the same structure, same content, about 100 Gb each. I developped
some benches, representative of our use of databases. My problem
is that I have tables (relations) with more than 100 millions rows,
and each row has about 160 fields and an average size 256 bytes.

For Oracle I have a SGA size of 500 Mb.
For PG I have a postgresql.conf as :
	max_connections = 1500
	shared_buffers = 30000
	sort_mem = 50000
	effective_cache_size = 200000
and default value for other parameters.

I have a table named "data" which looks like this :
bench=> \d data
                 Table "public.data"
   Column   |            Type             | Modifiers 
------------+-----------------------------+-----------
 num_poste  | numeric(9,0)                | not null
 dat        | timestamp without time zone | not null
 datrecu    | timestamp without time zone | not null
 rr1        | numeric(5,1)                | 
 qrr1       | numeric(2,0)                |       ...
 ... all numeric fields
 ...
 Indexes:
    "pk_data" primary key, btree (num_poste, dat)
    "i_data_dat" btree (dat)

It contains 1000 different values of "num_poste" and for each one 
125000 different values of "dat" (1 row per hour, 15 years). 

I run a vacuum analyze of the table.

bench=> select * from tailledb ;
 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.
125 000 000 rows x 256 b = about 32 Gb. This calculation gives an idea
not so bad for oracle. What about for PG ? How data is stored ?


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 !
I can't understand these results. The way to execute queries is the
same I think. I've read recommended articles on the PG site.
I tried with a table containing 30 millions rows, results are similar.

What can I do ?

Thanks for your help !                     

********************************************************************
*    Les points de vue exprimes sont strictement personnels et     *
*      n'engagent pas la responsabilite de METEO-FRANCE.           *
********************************************************************
* Valerie SCHNEIDER             Tel : +33 (0)5 61 07 81 91         *
* METEO-FRANCE / DSI/DEV        Fax : +33 (0)5 61 07 81 09         *
* 42, avenue G. Coriolis        Email : Valerie.Schneider@meteo.fr *
* 31057 TOULOUSE Cedex - FRANCE         http://www.meteo.fr        *
********************************************************************




^ permalink  raw  reply  [nested|flat] 15+ messages in thread

* Re: [PERFORM] Tuning queries on large database
  2004-08-04 12:44 Tuning queries on large database Valerie Schneider DSI/DEV <Valerie.Schneider@meteo.fr>
@ 2004-08-04 13:21 ` Christopher Kings-Lynne <chriskl@familyhealth.com.au>
  2 siblings, 0 replies; 15+ messages in thread

From: Christopher Kings-Lynne @ 2004-08-04 13:21 UTC (permalink / raw)
  To: Valerie Schneider DSI/DEV <Valerie.Schneider@meteo.fr>; +Cc: pgsql-performance@postgresql.org, <pgsql-general@postgresql.org>

> 	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





^ permalink  raw  reply  [nested|flat] 15+ messages in thread

* Re: Tuning queries on large database
  2004-08-04 12:44 Tuning queries on large database Valerie Schneider DSI/DEV <Valerie.Schneider@meteo.fr>
@ 2004-08-04 13:26 ` Rod Taylor <rbt@rbt.ca>
  2004-08-04 15:50   ` Re: Tuning queries on large database Pierre-Frédéric Caillaud <lists@boutiquenumerique.com>
  2 siblings, 1 reply; 15+ messages in thread

From: Rod Taylor @ 2004-08-04 13:26 UTC (permalink / raw)
  To: Valerie Schneider DSI/DEV <Valerie.Schneider@meteo.fr>; +Cc: pgsql-performance

On Wed, 2004-08-04 at 08:44, Valerie Schneider DSI/DEV wrote:
> Hi,
> 
> I have some problem of performance on a PG database, and I don't
> know how to improve. I Have two questions : one about the storage
> of data, one about tuning queries. If possible !
> 
> My job is to compare Oracle and Postgres. All our operational databases
> have been running under Oracle for about fifteen years. Now I try to replace
> Oracle by Postgres.

You may assume some additional hardware may be required -- this would be
purchased out of the Oracle License budget :)

> My first remark is that the table takes a lot of place on disk, about
> 70 Gb, instead of 35 Gb with oracle.
> 125 000 000 rows x 256 b = about 32 Gb. This calculation gives an idea
> not so bad for oracle. What about for PG ? How data is stored ?

This is due to the datatype you've selected. PostgreSQL does not convert
NUMERIC into a more appropriate integer format behind the scenes, nor
will it use the faster routines for the math when it is an integer.
Currently it makes the assumption that if you've asked for numeric
rather than integer or float that you are dealing with either large
numbers or require high precision math.

Changing most of your columns to integer + Check constraint (where
necessary) will give you a large speed boost and reduce disk
requirements a little.

> The different queries of the bench are "simple" queries (no join,
> sub-query, ...) and are using indexes (I "explained" each one to
> be sure) :

Care to send us the EXPLAIN ANALYZE output for each of the 4 queries
after you've improved the datatype selection?

-- 
Rod Taylor <rbt [at] rbt [dot] ca>

Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL
PGP Key: http://www.rbt.ca/signature.asc

Attachments:

  signature.asc (application/pgp-signature, 186B) download

^ permalink  raw  reply  [nested|flat] 15+ messages in thread

* Re: Tuning queries on large database
  2004-08-04 12:44 Tuning queries on large database Valerie Schneider DSI/DEV <Valerie.Schneider@meteo.fr>
  2004-08-04 13:26 ` Re: Tuning queries on large database Rod Taylor <rbt@rbt.ca>
@ 2004-08-04 15:50   ` Pierre-Frédéric Caillaud <lists@boutiquenumerique.com>
  0 siblings, 0 replies; 15+ messages in thread

From: Pierre-Frédéric Caillaud @ 2004-08-04 15:50 UTC (permalink / raw)
  To: Rod Taylor <rbt@rbt.ca>; Valerie Schneider DSI/DEV <Valerie.Schneider@meteo.fr>; +Cc: pgsql-performance


>> not so bad for oracle. What about for PG ? How data is stored

	I agree with the datatype issue. Smallint, bigint, integer... add a  
constraint...

	Also the way order of the records in the database is very important. As  
you seem to have a very large static population in your table, you should  
insert it, ordered by your favourite selection index (looks like it's  
poste).

	Also, you have a lot of static data which pollutes your table. Why not  
create two tables, one for the current year, and one for all the past  
years. Use a view to present a merged view.



^ permalink  raw  reply  [nested|flat] 15+ messages in thread

* Re: Tuning queries on large database
  2004-08-04 12:44 Tuning queries on large database Valerie Schneider DSI/DEV <Valerie.Schneider@meteo.fr>
@ 2004-08-04 15:34 ` Gaetano Mendola <mendola@bigfoot.com>
  2 siblings, 0 replies; 15+ messages in thread

From: Gaetano Mendola @ 2004-08-04 15:34 UTC (permalink / raw)
  To: Valerie Schneider DSI/DEV <Valerie.Schneider@meteo.fr>

Valerie Schneider DSI/DEV wrote:

> Hi,
> 
> I have some problem of performance on a PG database, and I don't
> know how to improve. I Have two questions : one about the storage
> of data, one about tuning queries. If possible !
> 
> My job is to compare Oracle and Postgres. All our operational databases
> have been running under Oracle for about fifteen years. Now I try to replace
> Oracle by Postgres.

Show us the explain analyze on your queries.

Regards
Gaetano Mendola







^ permalink  raw  reply  [nested|flat] 15+ messages in thread

* Re: Tuning queries on large database
@ 2004-08-04 14:12 Valerie Schneider DSI/DEV <Valerie.Schneider@meteo.fr>
  2004-08-04 15:53 ` Re: Tuning queries on large database Pierre-Frédéric Caillaud <lists@boutiquenumerique.com>
  0 siblings, 1 reply; 15+ messages in thread

From: Valerie Schneider DSI/DEV @ 2004-08-04 14:12 UTC (permalink / raw)
  To: pgsql-performance


>X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org
>X-Authentication-Warning: houston.familyhealth.com.au: chriskl owned process 
doing -bs
>Date: Wed, 4 Aug 2004 21:21:51 +0800 (WST)
>From: Christopher Kings-Lynne <chriskl@familyhealth.com.au>
>To: Valerie Schneider DSI/DEV <Valerie.Schneider@meteo.fr>
>Cc: pgsql-performance@postgresql.org, <pgsql-general@postgresql.org>
>Subject: Re: [PERFORM] Tuning queries on large database
>MIME-Version: 1.0
>X-Virus-Scanned: by amavisd-new at hub.org
>X-Spam-Status: No, hits=0.0 tagged_above=0.0 required=5.0 tests=
>X-Spam-Level: 
>X-Mailing-List: pgsql-performance
>
>> 	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

Q1 :
bench=> explain analyze select 'Q1',min(td),max(u) from data where 
num_poste=1000 and dat between 
(date_trunc('month',to_timestamp('31012004','ddmmyyyy')-interval '2000 
days'))::timestamp and 
(date_trunc('month',to_timestamp('31012004','ddmmyyyy')-interval '2000 days') + 
interval '1 month' - interval '1 hour')::timestamp;
                                                                                 
                                                                                 
                            QUERY PLAN                                                                                                                                                                                              
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
----------------------------------------------------------------------
 Aggregate  (cost=2501.90..2501.90 rows=1 width=21) (actual 
time=581.460..581.461 rows=1 loops=1)
   ->  Index Scan using pk_data on data  (cost=0.00..2498.80 rows=619 width=21) 
(actual time=92.986..579.089 rows=744 loops=1)
         Index Cond: ((num_poste = 1000::numeric) AND (dat >= 
(date_trunc('month'::text, (to_timestamp('31012004'::text, 'ddmmyyyy'::text) - 
'2000 days'::interval)))::timestamp without time zone) AND (dat <= 
(((date_trunc('month'::text, (to_timestamp('31012004'::text, 'ddmmyyyy'::text) - 
'2000 days'::interval)) + '1 mon'::interval) - '01:00:00'::interval))::timestamp 
without time zone))
 Total runtime: 609.149 ms
(4 rows)


Q2 :
bench=> explain analyze select 'Q2',count(*) from data where num_poste between 
100 and 100+10 and dat between 
(date_trunc('month',to_timestamp('31012004','ddmmyyyy')-interval '3000 
days'))::timestamp and 
(date_trunc('month',to_timestamp('31012004','ddmmyyyy')-interval '3000 days') + 
interval '1 month' - interval '1 hour')::timestamp;
                                                                                 
                                                                                 
                                            QUERY PLAN                                                                                                                                                                                                              
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
----------------------
 Aggregate  (cost=23232.05..23232.05 rows=1 width=0) (actual 
time=5678.849..5678.850 rows=1 loops=1)
   ->  Index Scan using pk_data on data  (cost=0.00..23217.68 rows=5747 width=0) 
(actual time=44.408..5669.387 rows=7920 loops=1)
         Index Cond: ((num_poste >= 100::numeric) AND (num_poste <= 
110::numeric) AND (dat >= (date_trunc('month'::text, 
(to_timestamp('31012004'::text, 'ddmmyyyy'::text) - '3000 
days'::interval)))::timestamp without time zone) AND (dat <= 
(((date_trunc('month'::text, (to_timestamp('31012004'::text, 'ddmmyyyy'::text) - 
'3000 days'::interval)) + '1 mon'::interval) - '01:00:00'::interval))::timestamp 
without time zone))
 Total runtime: 5679.059 ms
(4 rows)


Q3 :
bench=> explain analyze select 'Q3',sum(rr1),count(ff) from data where num_poste 
in (50,50+2);
                                                                    QUERY PLAN                                                                    
--------------------------------------------------------------------------------
------------------------------------------------------------------
 Aggregate  (cost=986770.56..986770.56 rows=1 width=17) (actual 
time=75401.030..75401.031 rows=1 loops=1)
   ->  Index Scan using pk_data, pk_data on data  (cost=0.00..985534.43 
rows=247225 width=17) (actual time=35.823..74885.689 rows=250226 loops=1)
         Index Cond: ((num_poste = 50::numeric) OR (num_poste = 52::numeric))
 Total runtime: 75405.666 ms
(4 rows)


Q4 :
bench=> explain analyze select 'Q4',count(*) from data where num_poste between 
600 and 625;
                                                                  QUERY PLAN                                                                  
--------------------------------------------------------------------------------
--------------------------------------------------------------
 Aggregate  (cost=12166763.62..12166763.62 rows=1 width=0) (actual 
time=1162090.302..1162090.303 rows=1 loops=1)
   ->  Index Scan using pk_data on data  (cost=0.00..12159021.19 rows=3096971 
width=0) (actual time=94.679..1158266.561 rows=3252938 loops=1)
         Index Cond: ((num_poste >= 600::numeric) AND (num_poste <= 
625::numeric))
 Total runtime: 1162102.217 ms
(4 rows)


Now I'm going to recreate my table with integer and real datatype,
and to decrease sort_mem to 5000.
Then I'll try these queries again.
Thanks.



********************************************************************
*    Les points de vue exprimes sont strictement personnels et     *
*      n'engagent pas la responsabilite de METEO-FRANCE.           *
********************************************************************
* Valerie SCHNEIDER             Tel : +33 (0)5 61 07 81 91         *
* METEO-FRANCE / DSI/DEV        Fax : +33 (0)5 61 07 81 09         *
* 42, avenue G. Coriolis        Email : Valerie.Schneider@meteo.fr *
* 31057 TOULOUSE Cedex - FRANCE         http://www.meteo.fr        *
********************************************************************




^ permalink  raw  reply  [nested|flat] 15+ messages in thread

* Re: Tuning queries on large database
  2004-08-04 14:12 Re: Tuning queries on large database Valerie Schneider DSI/DEV <Valerie.Schneider@meteo.fr>
@ 2004-08-04 15:53 ` Pierre-Frédéric Caillaud <lists@boutiquenumerique.com>
  0 siblings, 0 replies; 15+ messages in thread

From: Pierre-Frédéric Caillaud @ 2004-08-04 15:53 UTC (permalink / raw)
  To: pgsql-performance


	You often make sums. Why not use separate tables to cache these sums by  
month, by poste, by whatever ?

	Rule on insert on the big table updates the cache tables.



^ permalink  raw  reply  [nested|flat] 15+ messages in thread

* FW: Tuning queries on large database
@ 2004-08-04 15:56 Merlin Moncure <merlin.moncure@rcsonline.com>
  0 siblings, 0 replies; 15+ messages in thread

From: Merlin Moncure @ 2004-08-04 15:56 UTC (permalink / raw)
  To: pgsql-performance

[forwarded to performance] 
> 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 !
> I can't understand these results. The way to execute queries is the
> same I think. I've read recommended articles on the PG site.
> I tried with a table containing 30 millions rows, results are similar.


I don't trust the Oracle #s.  Lets look at Q4: returns 3 million rows.
Using your #s of 160 fields and 256 bytes, your are asking for a result
set of 160 * 256 * 3M  = 12 GB!  This data has to be gathered by the
disk, assembled, and sent over the network.

I don't know Oracle, but it probably has some 'smart' result set that
uses a cursor behind the scenes to do the fetching.

With a 3M row result set, you need to strongly consider using cursors.
Try experimenting with the same query (Q4), declared as a cursor, and
fetch the data in 10k blocks in a loop (fetch 10000), and watch the #s
fly.

Merlin




^ permalink  raw  reply  [nested|flat] 15+ messages in thread

* Re: [GENERAL] Tuning queries on large database
@ 2004-08-05 08:16 Valerie Schneider DSI/DEV <Valerie.Schneider@meteo.fr>
  2004-08-05 09:53 ` Re: [GENERAL] Tuning queries on large database Gaetano Mendola <mendola@bigfoot.com>
  2004-08-05 10:09 ` Re: [GENERAL] Tuning queries on large database Mark Kirkwood <markir@coretech.co.nz>
  2004-08-05 13:18 ` Re: [GENERAL] Tuning queries on large database Rod Taylor <pg@rbt.ca>
  0 siblings, 3 replies; 15+ messages in thread

From: Valerie Schneider DSI/DEV @ 2004-08-05 08:16 UTC (permalink / raw)
  To: chriskl@familyhealth.com.au; +Cc: pgsql-performance

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

Result of EXPLAIN ANALYZE :

Q1 :bench=> explain analyze select 'Q1',min(td),max(u) from data where 
num_poste=1000 and dat between 
(date_trunc('month',to_timestamp('31012004','ddmmyyyy')-interval '2000 
days'))::timestamp and 
(date_trunc('month',to_timestamp('31012004','ddmmyyyy')-interval '2000 days') + 
interval '1 month' - interval '1 hour')::timestamp;

                       QUERY PLAN                                                                                                                                                                                          
--------------------------------------------------------------------------------
 Aggregate  (cost=2466.47..2466.47 rows=1 width=8) (actual time=261.777..261.778 
rows=1 loops=1)
   ->  Index Scan using pk_data on data  (cost=0.00..2463.41 rows=611 width=8) 
(actual time=20.106..259.924 rows=744 loops=1)
         Index Cond: ((num_poste = 1000) AND (dat >= (date_trunc('month'::text, 
(to_timestamp('31012004'::text, 'ddmmyyyy'::text) - '2000 
days'::interval)))::timestamp without time zone) AND (dat <= 
(((date_trunc('month'::text, (to_timestamp('31012004'::text, 'ddmmyyyy'::text) - 
'2000 days'::interval)) + '1 mon'::interval) - '01:00:00'::interval))::timestamp 
without time zone))
 Total runtime: 262.145 ms
(4 rows)


Q2 : bench=> explain analyze select 'Q2',count(*) from data where num_poste 
between 100 and 100+10 and dat between 
(date_trunc('month',to_timestamp('31012004','ddmmyyyy')-interval '3000 
days'))::timestamp and 
(date_trunc('month',to_timestamp('31012004','ddmmyyyy')-interval '3000 days') + 
interval '1 month' - interval '1 hour')::timestamp;
                                                                                 
                                    QUERY PLAN                                                                                                                                                                                                     
--------------------------------------------------------------------------------
 Aggregate  (cost=24777.68..24777.68 rows=1 width=0) (actual 
time=4253.977..4253.978 rows=1 loops=1)
   ->  Index Scan using pk_data on data  (cost=0.00..24762.34 rows=6138 width=0) 
(actual time=46.602..4244.984 rows=7920 loops=1)
         Index Cond: ((num_poste >= 100) AND (num_poste <= 110) AND (dat >= 
(date_trunc('month'::text, (to_timestamp('31012004'::text, 'ddmmyyyy'::text) - 
'3000 days'::interval)))::timestamp without time zone) AND (dat <= 
(((date_trunc('month'::text, (to_timestamp('31012004'::text, 'ddmmyyyy'::text) - 
'3000 days'::interval)) + '1 mon'::interval) - '01:00:00'::interval))::timestamp 
without time zone))
 Total runtime: 4254.233 ms
(4 rows)


Q3 : bench=> explain analyze select 'Q3',sum(rr1),count(ff) from data where 
num_poste in (50,50+2);
                             QUERY PLAN                                                                    
--------------------------------------------------------------------------------
 Aggregate  (cost=963455.87..963455.87 rows=1 width=8) (actual 
time=27668.666..27668.667 rows=1 loops=1)
   ->  Index Scan using pk_data, pk_data on data  (cost=0.00..962236.31 
rows=243910 width=8) (actual time=16.251..27275.468 rows=250226 loops=1)
         Index Cond: ((num_poste = 50) OR (num_poste = 52))
 Total runtime: 27673.837 ms
(4 rows)


Q4 : bench=> explain analyze select 'Q4',count(*) from data where num_poste 
between 600 and 625;
                                     QUERY PLAN                                                                  
--------------------------------------------------------------------------------
 Aggregate  (cost=14086174.57..14086174.57 rows=1 width=0) (actual 
time=428235.024..428235.025 rows=1 loops=1)
   ->  Index Scan using pk_data on data  (cost=0.00..14076910.99 rows=3705431 
width=0) (actual time=45.283..424634.826 rows=3252938 loops=1)
         Index Cond: ((num_poste >= 600) AND (num_poste <= 625))
 Total runtime: 428235.224 ms
(4 rows)

Thanks for all, Valerie.

>X-Original-To: pgsql-general-postgresql.org@localhost.postgresql.org
>X-Authentication-Warning: houston.familyhealth.com.au: chriskl owned process 
doing -bs
>Date: Wed, 4 Aug 2004 21:21:51 +0800 (WST)
>From: Christopher Kings-Lynne <chriskl@familyhealth.com.au>
>To: Valerie Schneider DSI/DEV <Valerie.Schneider@meteo.fr>
>Cc: pgsql-performance@postgresql.org, <pgsql-general@postgresql.org>
>Subject: Re: [GENERAL] [PERFORM] Tuning queries on large database
>MIME-Version: 1.0
>X-Virus-Scanned: by amavisd-new at hub.org
>X-Spam-Status: No, hits=0.0 tagged_above=0.0 required=5.0 tests=
>X-Spam-Level: 
>X-Mailing-List: pgsql-general
>
>> 	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 5: Have you checked our extensive FAQ?
>
>               http://www.postgresql.org/docs/faqs/FAQ.html



********************************************************************
*    Les points de vue exprimes sont strictement personnels et     *
*      n'engagent pas la responsabilite de METEO-FRANCE.           *
********************************************************************
* Valerie SCHNEIDER             Tel : +33 (0)5 61 07 81 91         *
* METEO-FRANCE / DSI/DEV        Fax : +33 (0)5 61 07 81 09         *
* 42, avenue G. Coriolis        Email : Valerie.Schneider@meteo.fr *
* 31057 TOULOUSE Cedex - FRANCE         http://www.meteo.fr        *
********************************************************************




^ permalink  raw  reply  [nested|flat] 15+ messages in thread

* Re: [GENERAL] Tuning queries on large database
  2004-08-05 08:16 Re: [GENERAL] Tuning queries on large database Valerie Schneider DSI/DEV <Valerie.Schneider@meteo.fr>
@ 2004-08-05 09:53 ` Gaetano Mendola <mendola@bigfoot.com>
  2 siblings, 0 replies; 15+ messages in thread

From: Gaetano Mendola @ 2004-08-05 09:53 UTC (permalink / raw)
  To: Valerie Schneider DSI/DEV <Valerie.Schneider@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






^ permalink  raw  reply  [nested|flat] 15+ messages in thread

* Re: [GENERAL] Tuning queries on large database
  2004-08-05 08:16 Re: [GENERAL] Tuning queries on large database Valerie Schneider DSI/DEV <Valerie.Schneider@meteo.fr>
@ 2004-08-05 10:09 ` Mark Kirkwood <markir@coretech.co.nz>
  2 siblings, 0 replies; 15+ messages in thread

From: Mark Kirkwood @ 2004-08-05 10:09 UTC (permalink / raw)
  To: Valerie Schneider DSI/DEV <Valerie.Schneider@meteo.fr>; +Cc: chriskl@familyhealth.com.au; pgsql-performance

I am guessing that Oracle can satisfy Q4 entirely via index access, 
whereas Pg has to visit the table as well.

Having said that, a few partial indexes may be worth trying out on 
data.num_poste (say 10 or so), this won't help the table access but 
could lower the index cost. If you combine this with loading the data in 
num_poste order (or run CLUSTER), you may get closer to Oracle's time 
for this query.

regards

Mark

Valerie Schneider DSI/DEV wrote:

>For my different queries, it's better but less performant than oracle :
>
>	oracle	PG yesterday(numeric)	PG today(integer/real)
>
>Q4	28s	17m20s			6m47s
>
>
>
>Q4 : bench=> explain analyze select 'Q4',count(*) from data where num_poste 
>between 600 and 625;
>                                     QUERY PLAN                                                                  
>--------------------------------------------------------------------------------
> Aggregate  (cost=14086174.57..14086174.57 rows=1 width=0) (actual 
>time=428235.024..428235.025 rows=1 loops=1)
>   ->  Index Scan using pk_data on data  (cost=0.00..14076910.99 rows=3705431 
>width=0) (actual time=45.283..424634.826 rows=3252938 loops=1)
>         Index Cond: ((num_poste >= 600) AND (num_poste <= 625))
> Total runtime: 428235.224 ms
>(4 rows)
>
>Thanks for all, Valerie.
>
>  
>



^ permalink  raw  reply  [nested|flat] 15+ messages in thread

* Re: [GENERAL] Tuning queries on large database
  2004-08-05 08:16 Re: [GENERAL] Tuning queries on large database Valerie Schneider DSI/DEV <Valerie.Schneider@meteo.fr>
@ 2004-08-05 13:18 ` Rod Taylor <pg@rbt.ca>
  2 siblings, 0 replies; 15+ messages in thread

From: Rod Taylor @ 2004-08-05 13:18 UTC (permalink / raw)
  To: Valerie Schneider DSI/DEV <Valerie.Schneider@meteo.fr>; +Cc: Christopher Kings-Lynne <chriskl@familyhealth.com.au>; pgsql-performance

> so it takes about 28 Gb instead of 68 Gb !

Huzzah!

> For my different queries, it's better but less performant than oracle :

Not surprising. Oracle has a number of optimizations that we don't have
implemented at this point, particularly where aggregates are involved. 

One that PG could use, particularly for Q4, is the ability to execute a
selective sequential scan based on a read of the index -- right now it
pulls in actual data from the table structure while following the index
-- creates unnecessary disk-head movement.

The only solution to that, at the moment, is to cluster the table by
pk_data.


I am curious though, could you run the below query on both systems and
report back times?

        select 'Q4', * from data where num_poste between 600 and 625;

I'm wondering if Oracle is using a shortcut since the count(*) doesn't
actually require the data -- just knowledge of whether a matching row
exists or not.





^ permalink  raw  reply  [nested|flat] 15+ messages in thread

* Re: [GENERAL] Tuning queries on large database
@ 2004-08-05 13:07 Gaetano Mendola <mendola@bigfoot.com>
  0 siblings, 0 replies; 15+ messages in thread

From: Gaetano Mendola @ 2004-08-05 13:07 UTC (permalink / raw)
  To: Valerie Schneider DSI/DEV <Valerie.Schneider@meteo.fr>; +Cc: pgsql-performance

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Valerie Schneider DSI/DEV wrote:

| #---------------------------------------------------------------------------
| # RESOURCE USAGE (except WAL)
| #---------------------------------------------------------------------------
|
| # - Memory -
|
| shared_buffers = 30000          # min 16, at least max_connections*2, 8KB each
| #sort_mem = 1024                # min 64, size in KB
| sort_mem = 5000         # min 64, size in KB
| #vacuum_mem = 8192              # min 1024, size in KB
|
| # - Free Space Map -
|
| #max_fsm_pages = 20000          # min max_fsm_relations*16, 6 bytes each
| #max_fsm_relations = 1000       # min 100, ~50 bytes each
|
| # - Kernel Resource Usage -
|
| #max_files_per_process = 1000   # min 25
| #preload_libraries = ''
|
|
| #---------------------------------------------------------------------------
| # WRITE AHEAD LOG
| #---------------------------------------------------------------------------
|
| # - Settings -
|
| #fsync = true                   # turns forced synchronization on or off
| #wal_sync_method = fsync        # the default varies across platforms:
|                                 # fsync, fdatasync, open_sync, or open_datasync
| #wal_buffers = 8                # min 4, 8KB each
|
| # - Checkpoints -
|
| #checkpoint_segments = 3        # in logfile segments, min 1, 16MB each
| checkpoint_segments = 30        # in logfile segments, min 1, 16MB each
| #checkpoint_timeout = 300       # range 30-3600, in seconds
| #checkpoint_warning = 30        # 0 is off, in seconds
| #commit_delay = 0               # range 0-100000, in microseconds
| #commit_siblings = 5            # range 1-1000
|
|
| #---------------------------------------------------------------------------
| # QUERY TUNING
| #---------------------------------------------------------------------------
|
| # - Planner Method Enabling -
|
| #enable_hashagg = true
| #enable_hashjoin = true
| #enable_indexscan = true
| #enable_mergejoin = true
| #enable_nestloop = true
| enable_seqscan = false
| #enable_sort = true
| #enable_tidscan = true
|
| # - Planner Cost Constants -
|
| #effective_cache_size = 1000    # typically 8KB each
| effective_cache_size = 200000   # typically 8KB each
| #random_page_cost = 4           # units are one sequential page fetch cost
| random_page_cost = 2            # units are one sequential page fetch cost
| #cpu_tuple_cost = 0.01          # (same)
| #cpu_index_tuple_cost = 0.001   # (same)
| #cpu_operator_cost = 0.0025     # (same)
|
| # - Genetic Query Optimizer -
|
| #geqo = true
| #geqo_threshold = 11
| #geqo_effort = 1
| #geqo_generations = 0
| #geqo_pool_size = 0             # default based on tables in statement,
|                                 # range 128-1024
| #geqo_selection_bias = 2.0      # range 1.5-2.0



Your wal_buffers is too small try do bump up your wal_buffers to ~3000,
and see the effects.

why did you disable the sequential_scan (see later) ?

Try also to lower  the cpu_costs:

cpu_tuple_cost = 0.005
cpu_index_tuple_cost = 0.0005   # (same)
cpu_operator_cost = 0.0005     # (same)

this will push the optimizer to choose the index scans.
If not show us the explain with enable_seqscan = false
and with enable_seqscan = true

*Mount also your partition with the noatime parameter*




Regards
Gaeatano Mendola








-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.4 (MingW32)
Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org

iD8DBQFBEjEY7UpzwH2SGd4RAtxnAKDuTtYZvWMXL7zjHWU20VFtm2V1OACg/Y1l
GZuQ5RviMB2nB4M8G6PW17U=
=HxGz
-----END PGP SIGNATURE-----




^ permalink  raw  reply  [nested|flat] 15+ messages in thread

* Re: [GENERAL] Tuning queries on large database
@ 2004-08-05 13:20 Merlin Moncure <merlin.moncure@rcsonline.com>
  0 siblings, 0 replies; 15+ messages in thread

From: Merlin Moncure @ 2004-08-05 13:20 UTC (permalink / raw)
  To: Valerie Schneider DSI/DEV <Valerie.Schneider@meteo.fr>; +Cc: pgsql-performance

Another question:
How are you benchmarking your queries?  Are you running them from within
psql?  Do you plan to run these queries from within an application?

psql introduces some overhead because it has to scan the result set to
determine the widths of the columns for formatting purposes.  Try
returning a result set inside the libpq library if you know C and
compare the times.  Of course, if you are already using libpq, this
moot.  If you do know libpq, try setting up a loop that fetches the data
in 10k block in a loop...I will wager that you can get this to run in
under two minutes (Q4).

Merlin







^ permalink  raw  reply  [nested|flat] 15+ messages in thread

* Re: [GENERAL] Tuning queries on large database
@ 2004-08-05 13:26 Merlin Moncure <merlin.moncure@rcsonline.com>
  0 siblings, 0 replies; 15+ messages in thread

From: Merlin Moncure @ 2004-08-05 13:26 UTC (permalink / raw)
  To: Valerie Schneider DSI/DEV <Valerie.Schneider@meteo.fr>; +Cc: pgsql-performance

I wrote: 
> Another question:
> How are you benchmarking your queries?  Are you running them from
within
> psql?  Do you plan to run these queries from within an application?
> 
> psql introduces some overhead because it has to scan the result set to
> determine the widths of the columns for formatting purposes.  Try
> returning a result set inside the libpq library if you know C and
compare
> the times.  Of course, if you are already using libpq, this moot.  If
you
> do know libpq, try setting up a loop that fetches the data in 10k
block in
> a loop...I will wager that you can get this to run in under two
minutes
> (Q4).

Whoop, didn't see the aggregate...sorry :)

Merlin



^ permalink  raw  reply  [nested|flat] 15+ messages in thread


end of thread, other threads:[~2004-08-05 13:26 UTC | newest]

Thread overview: 15+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2004-08-04 12:44 Tuning queries on large database Valerie Schneider DSI/DEV <Valerie.Schneider@meteo.fr>
2004-08-04 13:21 ` Christopher Kings-Lynne <chriskl@familyhealth.com.au>
2004-08-04 13:26 ` Rod Taylor <rbt@rbt.ca>
2004-08-04 15:50   ` Pierre-Frédéric Caillaud <lists@boutiquenumerique.com>
2004-08-04 15:34 ` Gaetano Mendola <mendola@bigfoot.com>
2004-08-04 14:12 Re: Tuning queries on large database Valerie Schneider DSI/DEV <Valerie.Schneider@meteo.fr>
2004-08-04 15:53 ` Pierre-Frédéric Caillaud <lists@boutiquenumerique.com>
2004-08-04 15:56 FW: Tuning queries on large database Merlin Moncure <merlin.moncure@rcsonline.com>
2004-08-05 08:16 Re: [GENERAL] Tuning queries on large database Valerie Schneider DSI/DEV <Valerie.Schneider@meteo.fr>
2004-08-05 09:53 ` Gaetano Mendola <mendola@bigfoot.com>
2004-08-05 10:09 ` Mark Kirkwood <markir@coretech.co.nz>
2004-08-05 13:18 ` Rod Taylor <pg@rbt.ca>
2004-08-05 13:07 Re: [GENERAL] Tuning queries on large database Gaetano Mendola <mendola@bigfoot.com>
2004-08-05 13:20 Re: [GENERAL] Tuning queries on large database Merlin Moncure <merlin.moncure@rcsonline.com>
2004-08-05 13:26 Re: [GENERAL] Tuning queries on large database Merlin Moncure <merlin.moncure@rcsonline.com>

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