Re: index speed and failed expectations?

On Mon, Aug 04, 2008 at 08:35:28AM -0500, Adam Rich wrote:
> > This query from the console:
> >
> > select * from stats order by start_time;
> >
> > takes 8 seconds before starting its output. Am I wrong in assuming that
> > the index on start_time should make ORDER BY orders of magnitude
> > faster?
>
> Postgresql won't use the index for queries like this. Due to the
> MVCC implementation, the index does not contain all necessary information
> and would therefore be slower than using the table data alone.

Not necessarily true. Despite the index not having enough information,
the planner might still decide that using the index would be faster
than executing a sort.

create table stats (
id serial primary key,
start_time timestamp with time zone not null
);

insert into stats (start_time)
select now() - random() * '1 year'::interval
from generate_series(1, 100000);

create index stats_start_time_idx on stats (start_time);

analyze stats;

explain analyze select * from stats order by start_time;
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------
Index Scan using stats_start_time_idx on stats (cost=0.00..4767.83 rows=100000 width=12) (actual time=0.146..994.674 rows=100000 loops=1)
Total runtime: 1419.943 ms
(2 rows)

set enable_indexscan to off;
explain analyze select * from stats order by start_time;
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------
Sort (cost=9845.82..10095.82 rows=100000 width=12) (actual time=3240.976..3800.038 rows=100000 loops=1)
Sort Key: start_time
-> Seq Scan on stats (cost=0.00..1541.00 rows=100000 width=12) (actual time=0.091..500.853 rows=100000 loops=1)
Total runtime: 4226.870 ms
(4 rows)

--
Michael Fuhr