From: | Ralph Smith <smithrn(at)u(dot)washington(dot)edu> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: Index not being used |
Date: | 2007-08-14 00:24:30 |
Message-ID: | 98B259D4-A190-4A8D-A7C7-5E3ABA1DBF5C@u.washington.edu |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
On Aug 13, 2007, at 4:39 PM, Scott Marlowe wrote:
> 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?
======================================================================
Somewhere between 40,000 and 48,000 rows returned the index kicks in.
Out of a table of 7 million rows, that's a fairly common count I have
to work with.
It's the amount of activity since August 2nd, this year; NOT that
long ago.
Any suggestions on speeding up these queries, other than using more
and more tables, thus ruling out the reasonable use of command-line
queries?
Ultimately we'll move to some datawarehousing solution, but that's
not a 'tomorrow' kind of thing...
Thanks again all,
Ralph
======================================================================
From | Date | Subject | |
---|---|---|---|
Next Message | Dave Cramer | 2007-08-14 00:59:26 | Re: Dell Hardware Recommendations |
Previous Message | Scott Marlowe | 2007-08-13 23:39:46 | Re: Index not being used |