From: | Mark Kirkwood <markir(at)coretech(dot)co(dot)nz> |
---|---|
To: | Valerie Schneider DSI/DEV <Valerie(dot)Schneider(at)meteo(dot)fr> |
Cc: | chriskl(at)familyhealth(dot)com(dot)au, pgsql-performance(at)postgresql(dot)org |
Subject: | Re: [GENERAL] Tuning queries on large database |
Date: | 2004-08-05 10:09:58 |
Message-ID: | 41120776.4090401@coretech.co.nz |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | 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.
>
>
>
From | Date | Subject | |
---|---|---|---|
Next Message | Gaetano Mendola | 2004-08-05 13:07:38 | Re: [GENERAL] Tuning queries on large database |
Previous Message | Gaetano Mendola | 2004-08-05 09:53:59 | Re: [GENERAL] Tuning queries on large database |