From: Michael Fuhr <mike@fuhr.org>
To: David Durham <ddurham@vailsys.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: Index lookup on > and < criteria
Date: Tue, 1 Nov 2005 18:08:02 -0700
Message-ID: <20051102010802.GA88950@winnie.fuhr.org> (raw)
In-Reply-To: <4367DC51.8090803@vailsys.com>
References: <4367B16B.2050003@vailsys.com>
<20051101192123.GA86877@winnie.fuhr.org>
<4367DC51.8090803@vailsys.com>
On Tue, Nov 01, 2005 at 03:21:21PM -0600, David Durham wrote:
> sipcdr=# explain analyze select * from october_cdr_call where begin_time
> >= '10/1/2005' and begin_time < '10/4/2005';
>
> QUERY PLAN
>
> --------------------------------------------------------------------------------
> --------------------------------------------------------------------------
> Index Scan using october_begin_time on october_cdr_call
> (cost=0.00..98383.82 r
> ows=24594 width=568) (actual time=0.280..79274.579 rows=538592 loops=1)
> Index Cond: ((begin_time >= '2005-10-01 00:00:00'::timestamp without
> time zon
> e) AND (begin_time < '2005-10-04 00:00:00'::timestamp without time zone))
> Total runtime: 81457.938 ms
> (3 rows)
The estimated row count (24594) is much different than the actual
row count (538592), which makes me wonder if the statistics are up
to date. Try running ANALYZE on the table and then see if the
estimate is more accurate. With a more accurate estimate the planner
might choose a sequential scan, but the other queries you posted
suggest that a sequential scan is indeed faster when you're fetching
this much data.
> >Has the table been vacuumed and analyzed?
>
> Brand new table that I haven't deleted anything from yet.
The table should still be analyzed to update the planner's statistics.
The planner uses statistics to estimate how many rows a query will
return, and that influences the choice of plan.
> >How much memory do you have and what's your effective_cache_size
> >setting?
>
> 1.5 gig RAM, effective_cache_size is the default, so 1000.
You'd probably benefit from raising effective_cache_size to reflect
the amount of memory being used for disk cache, both by PostgreSQL
and by the operating system; you might also benefit from adjusting
other settings like shared_buffers. See a tuning guide like the
following for advice:
http://www.powerpostgresql.com/PerfList
--
Michael Fuhr
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-sql@postgresql.org
Cc: mike@fuhr.org, ddurham@vailsys.com
Subject: Re: Index lookup on > and < criteria
In-Reply-To: <20051102010802.GA88950@winnie.fuhr.org>
* 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