From: Arkadiusz Raj <arek@raj.priv.pl>
To: pgsql-performance@postgresql.org
Subject: index usage
Date: Mon, 23 Apr 2007 19:20:29 +0200
Message-ID: <20070423172018.6409A79B@post17.futuro.info.pl> (raw)
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: arek@raj.priv.pl
Subject: Re: index usage
In-Reply-To: <20070423172018.6409A79B@post17.futuro.info.pl>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox