Search Postgresql Archives

Re: Index not being used

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 




On Aug 13, 2007, at 4:39 PM, Scott Marlowe wrote:

On 8/13/07, Ralph Smith <smithrn@xxxxxxxxxxxxxxxx> 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
======================================================================

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux