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: Tue, 01 Nov 2005 15:21:21 -0600
Message-ID: <4367DC51.8090803@vailsys.com> (raw)
In-Reply-To: <20051101192123.GA86877@winnie.fuhr.org>
References: <4367B16B.2050003@vailsys.com>
<20051101192123.GA86877@winnie.fuhr.org>
Michael Fuhr wrote:
> No need to guess: run the queries with enable_seqscan disabled and
> see if an index scan is indeed faster.
>
>
>> select max(myTimeStamp) from myTable;
>
>
> In current releases min() and max() can't use indexes; search the
> archives for numerous discussions of the reasons. The workarounds
> are, respectively:
Good to know. Thanks all.
> How many rows do these queries return? If they return a significant
> portion of the table then the planner might think that a sequential
> scan would be faster than an index scan. It would be useful to see
> the EXPLAIN ANALYZE output of these queries so we can see how
> accurate the planner's row count estimates are.
Ok. Looks like you guys caught me in a(n unintentional) lie. Here goes:
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)
sipcdr=# explain analyze select * from october_cdr_call where begin_time
< '10/15/2005';
QUERY PLAN
--------------------------------------------------------------------------------
--------------------------------------------------
Seq Scan on october_cdr_call (cost=0.00..273437.39 rows=1639584
width=568) (ac
tual time=11.623..43681.396 rows=2609215 loops=1)
Filter: (begin_time < '2005-10-15 00:00:00'::timestamp without time
zone)
Total runtime: 54366.944 ms
(3 rows)
> Has the table been vacuumed and analyzed?
Brand new table that I haven't deleted anything from yet.
> If so, and if the planner's
> row count estimates aren't close to the actual row counts, then you
> might benefit from increasing the statistics target for the myTimeStamp
> column.
Ok, this is something that balances what might lead to overuse of the
vacuum command? I can just look that one up.
> 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.
> That's one of the settings that influences the planner's
> decision. Also, what version of PostgreSQL are you running?
8.0.3
> BTW, pgsql-performance would be a more appropriate list to discuss
> performance issues.
Ok, I won't cross post this one, but I'll send the next one there.
Here's the final word on this, I think:
sipcdr=# set enable_seqscan=off;
SET
sipcdr=# explain analyze select * from october_cdr_call where begin_time
< '10/15/2005';
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------------------------
Index Scan using october_begin_time on october_cdr_call
(cost=0.00..6338044.65 rows=1639584 width=568) (actual
time=51.454..355782.687 rows=2609215 loops=1)
Index Cond: (begin_time < '2005-10-15 00:00:00'::timestamp without
time zone)
Total runtime: 366289.918 ms
Thanks again,
-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: <4367DC51.8090803@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