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.
---------------------------(end of broadcast)--------------------------- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
