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@perchine.com
HomePage: http://www.perchine.com/dyp/
FidoNet: 2:5000/120.5
----------------------------------