Re: Index lookup on > and < criteria

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