Date: Sun, 19 Mar 2006 16:14:34 +1200
From: Mark Kirkwood <markir@paradise.net.nz>
Subject: Re: planner with index scan cost way off actual cost,
In-reply-to: <87ek11gz69.fsf@meuh.mnc.lan>
To: Guillaume Cottenceau <gc@mnc.ch>
Cc: pgsql-performance@postgresql.org
Message-id: <441CDAAA.20609@paradise.net.nz>
MIME-version: 1.0
Content-type: text/plain; format=flowed; charset=ISO-8859-1
Content-transfer-encoding: 7bit
User-Agent: Mozilla Thunderbird 1.0.6 (X11/20051106)
References: <87ek11gz69.fsf@meuh.mnc.lan>

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