Re: Index not being used

On 8/13/07, Ralph Smith <smithrn(at)u(dot)washington(dot)edu> wrote:
> I'm confused. Shouldn't this index be used?
> (It's running on v7.4.7)
>
> airburst=> \d stats2
> Table "public.stats2"
> Column | Type | Modifiers
> -----------+-----------------------+-----------
> lab | character varying(30) |
> name | character varying(50) |
> status | character varying(40) |
> eventtime | integer |
> username | character varying(30) |
> pkey | character varying(60) |
> Indexes:
> "stats2_etime_index" btree (eventtime)
>
> airburst=> \d stats2_etime_index
> Index "public.stats2_etime_index"
> Column | Type
> -----------+---------
> eventtime | integer
> btree, for table "public.stats2"
>
> airburst=> explain select count(*) from stats2 where eventtime > 1167638400
> ;
> QUERY PLAN
> -----------------------------------------------------------------------
> Aggregate (cost=185247.97..185247.97 rows=1 width=0)
> -> Seq Scan on stats2 (cost=0.00..179622.45 rows=2250205 width=0)
> Filter: (eventtime > 1167638400)
> (3 rows)

That really depends. how many rows are actually returned? If it's
2250205 like the query planner thinks, and that's a fair chunk of the
table, then no, it shouldn't use an index, a seq scan will be faster.
What does explain analyze select ... say?