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'::timestampwith 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
