Guillaume Cottenceau wrote:
SET random_page_cost = 2;
SET effective_cache_size = 10000;
EXPLAIN SELECT * FROM sent_messages WHERE date > '2005-09-01' AND date < '2005-09-19';
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------
Index Scan using idx_sent_msgs_date_theme_status on sent_messages (cost=0.00..595894.94 rows=392066 width=78)
Index Cond: ((date > '2005-09-01 00:00:00+00'::timestamp with time zone) AND (date < '2005-09-19 00:00:00+00'::timestamp with time zone))
We can see that estimated index scan cost goes down but by a
factor of approx. 2.3 which is far from enough to "fix" it. I
am reluctant in changing way more the random_page_cost and
effective_cache_size values as I'm suspecting it might have
other (bad) consequences if it is too far away from reality
(even if Linux is known to aggressively cache), the application
being multithreaded (there is a warning about concurrent
queries using different indexes in documentation). But I
certainly could benefit from others' experience on this matter.
I apologize for this long email but I wanted to be sure I gave
enough information on the data and things I have tried to fix the
problem myself. If anyone can see what I am doing wrong, I would
be very interested in pointers.
Thanks in advance!
Btw, I use postgres 7.4.5 with -B 1000 -N 500 and all
postgresql.conf default values except timezone = 'UTC', on an
ext3 partition with data=ordered, and run Linux 2.6.12.
I didn't see any mention of how much memory is on your server, but
provided you have say 1G, and are using the box solely for a database
server, I would increase both shared_buffers and effective_cache size.
shared_buffer = 12000
effective_cache_size = 25000
This would mean you are reserving 100M for Postgres to cache relation
pages, and informing the planner that it can expect ~200M available from
the disk buffer cache. To give a better recommendation, we need to know
more about your server and workload (e.g server memory configuration and
usage plus how close you get to 500 connections).
Cheers
Mark
