Re: [GENERAL] Tuning queries on large database

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.
>
>
>