Received: from localhost (maia-3.hub.org [200.46.204.184]) by postgresql.org (Postfix) with ESMTP id 7ECC29FB29A for ; Mon, 23 Apr 2007 14:46:32 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.184]) (amavisd-maia, port 10024) with ESMTP id 46355-01 for ; Mon, 23 Apr 2007 14:46:23 -0300 (ADT) X-Greylist: delayed 00:25:39.480198 by SQLgrey-1.7.4 Received: from svr4.postgresql.org (svr4.postgresql.org [66.98.251.159]) by postgresql.org (Postfix) with ESMTP id D0BDE9FB504 for ; Mon, 23 Apr 2007 14:46:20 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.3 Received: from post17.futuro.info.pl (post17.futuro.info.pl [81.219.17.17]) by svr4.postgresql.org (Postfix) with ESMTP id 7B3A85AF04F for ; Mon, 23 Apr 2007 17:20:35 +0000 (GMT) Received: from localhost (localhost [127.0.0.1]) by post17.futuro.info.pl (Postfix) with ESMTP id BD0E18C5 for ; Mon, 23 Apr 2007 19:20:18 +0200 (CEST) Received: from post17.futuro.info.pl ([127.0.0.1]) by localhost (post17.flexopartner.net [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id 24485-08 for ; Mon, 23 Apr 2007 19:20:18 +0200 (CEST) Received: from areklp (staticline2445.toya.net.pl [217.113.235.91]) by post17.futuro.info.pl (Postfix) with ESMTP id 6409A79B for ; Mon, 23 Apr 2007 19:20:18 +0200 (CEST) X-AVScan: Checked by ArcaMail From: "Arkadiusz Raj" To: Subject: index usage Date: Mon, 23 Apr 2007 19:20:29 +0200 MIME-Version: 1.0 Content-Type: text/plain; charset="iso-8859-2" Content-Transfer-Encoding: quoted-printable X-Mailer: Microsoft Office Outlook, Build 11.0.5510 X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.3028 Thread-Index: AceFHeo29HNshKiSTw2EDKRLTk0RQgAq/Fcg Message-Id: <20070423172018.6409A79B@post17.futuro.info.pl> X-Virus-Scanned: by amavisd-new-2.3.3 (20050822) X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.001 tagged_above=0 required=5 tests=BAYES_50=0.001 X-Spam-Level: X-Archive-Number: 200704/394 X-Sequence-Number: 24271 Hi,=0D=0A=0D=0AI have a table in my database that is updated every minut= e with new acquired=0D=0Adata. Anyway there is a query to get latest= values to be displayed on=0D=0Ascreen. I have postgresql 7.4.2 that= work very fine. The problem was that=0D=0Aafter hdd crash I have re= build database from the archive and... Execution=0D=0Atime of this q= uery starts to be unacceptable. And I found funny thing. Using=0D=0A= static value in place expression remove this problem. Query started = to be=0D=0Aexecuted fast again.=0D=0A=0D=0AI did not change any sett= ings in postgresql configuration. Just had to=0D=0Arestart all the s= ervices.=0D=0A=0D=0ACan someone tell me why the optimizer stopped to= choose index? I had seqscan=0D=0Adisabled already.=0D=0A=0D=0AOne n= ote about those two outputs below: there are different number of=0D=0A= touples returned due to the fact that in fact the timestamp is chose= n=0D=0Adifferently. =0D=0A=0D=0ARegards,=0D=0A=0D=0A/Arek=0D=0A=0D=0A= ------------------------------------------------------------------=0D= =0A=0D=0Aexplain analyze SELECT distinct on (index) index, status, v= alue FROM _values=0D=0AWHERE device=3D1 and timestamp>(now()-5*inter= val '1 min') ORDER by index,=0D=0Atimestamp desc;=0D=0A=A0=A0=A0=A0=A0= =A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0= =A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0= =A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0 QUERY=0D=0APLAN=A0=A0=A0=A0=A0=A0=A0= =A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0= =A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0 =0D= =0A-----------------------------------------------------------------= -----------=0D=0A---------------------------------------------------= ----------=0D=0A=A0Unique=A0 (cost=3D100117679.93..100117756.29 rows= =3D1 width=3D24) (actual=0D=0Atime=3D5279.262..5279.308 rows=3D10 lo= ops=3D1)=0D=0A=A0=A0 ->=A0 Sort=A0 (cost=3D100117679.93..100117718.1= 1 rows=3D15272 width=3D24) (actual=0D=0Atime=3D5279.260..5279.275 ro= ws=3D21 loops=3D1)=0D=0A=A0=A0=A0=A0=A0=A0=A0=A0 Sort Key: "index", = "timestamp"=0D=0A=A0=A0=A0=A0=A0=A0=A0=A0 ->=A0 Seq Scan on _values=A0= (cost=3D100000000.00..100116618.64=0D=0Arows=3D15272 width=3D24) (a= ctual time=3D5277.596..5279.184 rows=3D21 loops=3D1)=0D=0A=A0=A0=A0=A0= =A0=A0=A0=A0=A0=A0=A0=A0=A0=A0 Filter: ((device =3D 1) AND (("timest= amp")::timestamp with time=0D=0Azone > (now() - '00:05:00'::interval= )))=0D=0A=A0Total runtime: 5279.391 ms=0D=0A(6 rows)=0D=0A=0D=0Aexpl= ain analyze SELECT distinct on (index) index, status, value FROM _va= lues=0D=0AWHERE device=3D1 and timestamp>'2007-04-22 21:20' ORDER by= index, timestamp=0D=0Adesc;=0D=0A=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0= =A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0 =A0= =A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0= =A0=A0=A0=A0=A0QUERY=0D=0APLAN=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0= =A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0= =A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0 =0D=0A----------------= ------------------------------------------------------------=0D=0A--= -------------------------------------------------------------=0D=0A=A0= Unique=A0 (cost=3D703.45..703.47 rows=3D1 width=3D24) (actual time=3D= 4.807..4.867=0D=0Arows=3D10 loops=3D1)=0D=0A=A0=A0 ->=A0 Sort=A0 (co= st=3D703.45..703.46 rows=3D5 width=3D24) (actual time=3D4.804..4.827= =0D=0Arows=3D31 loops=3D1)=0D=0A=A0=A0=A0=A0=A0=A0=A0=A0 Sort Key: "= index", "timestamp"=0D=0A=A0=A0=A0=A0=A0=A0=A0=A0 ->=A0 Index Scan u= sing _values_dbidx_idx on _values=A0=0D=0A(cost=3D0.00..703.39 rows=3D= 5 width=3D24) (actual time=3D0.260..4.728 rows=3D31=0D=0Aloops=3D1)=0D= =0A=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0 Index Cond: ("timestam= p" > '2007-04-22 21:20:00'::timestamp=0D=0Awithout time zone)=0D=0A=A0= =A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0 Filter: (device =3D 1)=0D=0A= =A0Total runtime: 4.958 ms=0D=0A(7 rows)=0D=0A=0D=0A=0D=0A-- =0D=0AL= ist przeskanowano programem ArcaMail, ArcaVir 2007=0D=0Aprzeskanowan= o 2007-04-23 19:20:29, silnik: 2007.01.01 12:00:00, bazy: 2007.04.15= 09:21:20=0D=0AThis message has been scanned by ArcaMail, ArcaVir 20= 07=0D=0Ascanned 2007-04-23 19:20:29, engine: 2007.01.01 12:00:00, ba= se: 2007.04.15 09:21:20=0D=0Ahttp://www.arcabit.com=0D=0A