From: Mark Kirkwood <markir@paradise.net.nz>
To: Guillaume Cottenceau <gc@mnc.ch>
Cc: pgsql-performance@postgresql.org
Subject: Re: planner with index scan cost way off actual cost,
Date: Sun, 19 Mar 2006 16:14:34 +1200
Message-ID: <441CDAAA.20609@paradise.net.nz> (raw)
In-Reply-To: <87ek11gz69.fsf@meuh.mnc.lan>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: markir@paradise.net.nz, gc@mnc.ch
Subject: Re: planner with index scan cost way off actual cost,
In-Reply-To: <441CDAAA.20609@paradise.net.nz>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox