From: David Durham <ddurham@vailsys.com>
To: Michael Fuhr <mike@fuhr.org>
Cc: pgsql-sql@postgresql.org
Subject: Re: Index lookup on > and < criteria
Date: Wed, 02 Nov 2005 13:42:06 -0600
Message-ID: <4369168E.1080500@vailsys.com> (raw)
In-Reply-To: <20051102010802.GA88950@winnie.fuhr.org>
References: <4367B16B.2050003@vailsys.com>
<20051101192123.GA86877@winnie.fuhr.org>
<4367DC51.8090803@vailsys.com>
<20051102010802.GA88950@winnie.fuhr.org>
Yeah, analyze did make a difference. See below.
>>--------------------------------------------------------------------------
>> 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.
sipcdr=# analyze october_cdr_call;
ANALYZE
sipcdr=# explain analyze select * from october_cdr_call where begin_time
>= '10/1/2005' and begin_time < '10/4/2005';
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------------
Seq Scan on october_cdr_call (cost=0.00..285695.68 rows=500922
width=371) (actual time=54.510..50004.458 rows=538592 loops=1)
Filter: ((begin_time >= '2005-10-01 00:00:00'::timestamp without
time zone) AND (begin_time < '2005-10-04 00:00:00'::timestamp without
time zone))
Total runtime: 52335.126 ms
-Dave
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: ddurham@vailsys.com, mike@fuhr.org
Subject: Re: Index lookup on > and < criteria
In-Reply-To: <4369168E.1080500@vailsys.com>
* 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