X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (av.hub.org [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 874CFD8ABD for ; Wed, 2 Nov 2005 15:41:28 -0400 (AST) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 01280-07 for ; Wed, 2 Nov 2005 19:41:25 +0000 (GMT) Received: from chrobd01.vailsys.com (chrobd01.vailsys.com [63.210.102.138]) by svr1.postgresql.org (Postfix) with ESMTP id 13DD0D6868 for ; Wed, 2 Nov 2005 15:41:24 -0400 (AST) Received: from dfsfbd06.vail (dfsfbd06.vail [192.168.129.190]) by chrobd01.vailsys.com (Postfix) with ESMTP id B66D2497D; Wed, 2 Nov 2005 13:41:23 -0600 (CST) Received: from [192.168.129.86] (dfwddurham.vail [192.168.129.86]) by dfsfbd06.vail (Postfix) with ESMTP id 9E6A55E63; Wed, 2 Nov 2005 13:41:23 -0600 (CST) Message-ID: <4369168E.1080500@vailsys.com> Date: Wed, 02 Nov 2005 13:42:06 -0600 From: David Durham User-Agent: Mozilla Thunderbird 1.0.2 (X11/20050404) X-Accept-Language: en-us, en MIME-Version: 1.0 To: Michael Fuhr Cc: pgsql-sql@postgresql.org Subject: Re: Index lookup on > and < criteria References: <4367B16B.2050003@vailsys.com> <20051101192123.GA86877@winnie.fuhr.org> <4367DC51.8090803@vailsys.com> <20051102010802.GA88950@winnie.fuhr.org> In-Reply-To: <20051102010802.GA88950@winnie.fuhr.org> Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, score=0 required=5 tests=[none] X-Spam-Score: 0 X-Spam-Level: X-Archive-Number: 200511/32 X-Sequence-Number: 23284 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