>When I run the query, it takes a really long time (10 seconds) the first
>time I execute it, and then returns instantly the second time.
This is behavior which I often encounter with my databases as well (and I
have 8 gigs of RAM now), and is easy to explain.
The first time, the system has to page in all the necessary data from the
disk. This is slow.
The second and subsequent times, the pages are in memory (either in the
shared_mem segment or the OS disk cache) and hence the query runs extremely
quickly.
Of course, if you run a different query which requires other page loads in
the interim, and those page loads cause the other pages to be flushed, the
next time this query runs it will be slow again.
Cheers,
Doug