Thread: index usage

index usage

From
"Arkadiusz Raj"
Date:
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


Re: index usage

From
"Steinar H. Gunderson"
Date:
On Mon, Apr 23, 2007 at 07:20:29PM +0200, Arkadiusz Raj wrote:
> 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.

You want _at least_ the latest 7.4 version -- ideally, the latest 8.2
version.

> The problem was that

> after hdd crash I have rebuild database from the archive and... Execution

> time of this query starts to be unacceptable.

Have you re-ANALYZEd after the data load?

Anyhow, the issue with the planner not knowing how to estimate expressions
like "now() - interval '5 minutes'" correctly is a known 7.4 issue, and it's
fixed in later versions. It might have worked more or less by accident
earlier, although it seems odd that it wouldn't even have considered the
index scan...

/* Steinar */
--
Homepage: http://www.sesse.net/