Re: planner with index scan cost way off actual cost,

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