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
---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend
