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.



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.

---------------------------(end of broadcast)--------------------------- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

Reply via email to