Re: Load a database into memory

> Here it comes :
>
> VACUUM VERBOSE ANALYZE accord_editeur;
> NOTICE: --Relation accord_editeur--
> NOTICE: Pages 7096: Changed 0, reaped 6790, Empty 0, New 0; Tup 447032:
> Vac 0, Keep/VTL 0/0, Crash 0, UnUsed 23768, MinLen 124, MaxLen 124;
> Re-using: Free/Avail. Space 721776/0; EndEmpty/Avail. Pages 0/0. CPU
> 0.39s/1.67u sec.
> NOTICE: Index ae_tracking_idx: Pages 2300; Tuples 447032: Deleted 0.
> CPU 0.07s/0.90u sec.
> VACUUM
>
> EXPLAIN :
> NOTICE: QUERY PLAN:
>
> Nested Loop (cost=0.00..228.27 rows=1 width=56)
> -> Index Scan using parametre_tracking_idx on parametre par
> (cost=0.00..2.02 rows=1 width=8)
> -> Index Scan using ae_tracking_idx on accord_editeur ae
> (cost=0.00..225.50 rows=60 width=48)
>
> EXPLAIN
>
> So, no change for the cost...
> Only for the number of pages...
>
> So, if I can't more optimize my index, what else should I try ?
> It uses too much CPU for me...

These numbers can be not completly correct. I would rather suggest you to
look on actual query speed.

Say:
set PG_Options TO "executorstats=1";

And run your query with different indices. You will see something like this
in your postgres log:

! Executor Stats:
! system usage stats:
! 1.409678 elapsed 0.480000 user 0.630000 system sec
! [0.500000 user 0.640000 sys total]
! 0/0 [0/0] filesystem blocks in/out
! 4/1790 [256/2238] page faults/reclaims, 0 [0] swaps
! 0 [0] signals rcvd, 0/0 [0/0] messages rcvd/sent
! 0/0 [0/0] voluntary/involuntary context switches
! postgres usage stats:
! Shared blocks: 7035 read, 7 written, buffer hit rate =
1.24%
! Local blocks: 0 read, 0 written, buffer hit rate =
0.00%
! Direct blocks: 0 read, 0 written

And the first number is amount of seconds.

This will be real measure.

--
Sincerely Yours,
Denis Perchine

----------------------------------
E-Mail: dyp(at)perchine(dot)com
HomePage: http://www.perchine.com/dyp/
FidoNet: 2:5000/120.5
----------------------------------