index usage

Hi,

I have a table in my database that is updated every minute with new acquired
data. Anyway there is a query to get latest values to be displayed on
screen. I have postgresql 7.4.2 that work very fine. The problem was that
after hdd crash I have rebuild database from the archive and... Execution
time of this query starts to be unacceptable. And I found funny thing. Using
static value in place expression remove this problem. Query started to be
executed fast again.

I did not change any settings in postgresql configuration. Just had to
restart all the services.

Can someone tell me why the optimizer stopped to choose index? I had seqscan
disabled already.

One note about those two outputs below: there are different number of
touples returned due to the fact that in fact the timestamp is chosen
differently.

Regards,

/Arek

------------------------------------------------------------------

explain analyze SELECT distinct on (index) index, status, value FROM _values
WHERE device=1 and timestamp>(now()-5*interval '1 min') ORDER by index,
timestamp desc;
                                                               QUERY
PLAN                                                    
----------------------------------------------------------------------------
-------------------------------------------------------------
 Unique  (cost=100117679.93..100117756.29 rows=1 width=24) (actual
time=5279.262..5279.308 rows=10 loops=1)
   ->  Sort  (cost=100117679.93..100117718.11 rows=15272 width=24) (actual
time=5279.260..5279.275 rows=21 loops=1)
         Sort Key: "index", "timestamp"
         ->  Seq Scan on _values  (cost=100000000.00..100116618.64
rows=15272 width=24) (actual time=5277.596..5279.184 rows=21 loops=1)
               Filter: ((device = 1) AND (("timestamp")::timestamp with time
zone > (now() - '00:05:00'::interval)))
 Total runtime: 5279.391 ms
(6 rows)

explain analyze SELECT distinct on (index) index, status, value FROM _values
WHERE device=1 and timestamp>'2007-04-22 21:20' ORDER by index, timestamp
desc;
                                                                QUERY
PLAN                                                   
----------------------------------------------------------------------------
---------------------------------------------------------------
 Unique  (cost=703.45..703.47 rows=1 width=24) (actual time=4.807..4.867
rows=10 loops=1)
   ->  Sort  (cost=703.45..703.46 rows=5 width=24) (actual time=4.804..4.827
rows=31 loops=1)
         Sort Key: "index", "timestamp"
         ->  Index Scan using _values_dbidx_idx on _values 
(cost=0.00..703.39 rows=5 width=24) (actual time=0.260..4.728 rows=31
loops=1)
               Index Cond: ("timestamp" > '2007-04-22 21:20:00'::timestamp
without time zone)
               Filter: (device = 1)
 Total runtime: 4.958 ms
(7 rows)

--
List przeskanowano programem ArcaMail, ArcaVir 2007
przeskanowano 2007-04-23 19:20:29, silnik: 2007.01.01 12:00:00, bazy: 2007.04.15 09:21:20
This message has been scanned by ArcaMail, ArcaVir 2007
scanned 2007-04-23 19:20:29, engine: 2007.01.01 12:00:00, base: 2007.04.15 09:21:20
http://www.arcabit.com