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
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 w
-
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)
[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 P
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.
---(end of broadcast)---
TIP 9: the planner will ignore your desire to choose
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
]>
>Cc: [EMAIL PROTECTED], <[EMAIL PROTECTED]>
>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-perf
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 Postg
> sort_mem = 5
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.
> sche
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
10 matches
Mail list logo
