Re: Index lookup on > and < criteria - Mailing list pgsql-sql

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';
QUERYPLAN 
 

--------------------------------------------------------------------------------
-------------------------------------------------------------------------- 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'::timestampwithout 
 
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 


---------------------------------------------------------------------------------------------------------------------------------------------------------------
IndexScan 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