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