Skip site navigation (1) Skip section navigation (2)

Re: Slow indexscan

From: "Mikko Partio" <mpartio(at)gmail(dot)com>
To: "Tom Lane" <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: pgsql-performance(at)postgresql(dot)org
Subject: Re: Slow indexscan
Date: 2007-06-25 07:50:48
Message-ID: 2ca799770706250050x225525f8lad01b4e69e2d1743@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-performance
On 6/20/07, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>
>
> There's no obvious reason for the previous query to be so slow, unless
> you've got horrendously slow or overloaded disk hardware.  What sort of
> machine is this anyway, and was it doing any other work at the time?



Granted it is doing other work besides database-stuff, mainly CPU-intensive
calculations.

The creation of the (latitude,longitude,validtime,parname) index and moving
the database files from a RAID-5 to RAID-10 has decreased the query time to
~4 seconds:

db=# explain analyze select * from tbl_20070601 where validtime between
20070602000000 and 20070602235500 and latitude=60.2744 and
longitude=26.4417and parname in ('temperature');

QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Index Scan using tbl_20070601_latlonvalidparname_index on tbl_20070601
(cost=0.00..28.46 rows=13 width=137) (actual time=94.52..3743.53 rows=539
loops=1)
   Index Cond: ((latitude = 60.2744::double precision) AND (longitude =
26.4417::double precision) AND (validtime >= 20070602000000::bigint) AND
(validtime <= 20070602235500::bigint) AND (parname =
'temperature'::character varying))
 Total runtime: 3744.56 msec
(3 rows)

This is already a great improvement compared to the previous 8 seconds. Our
app developers claim though that previously the same queries have run in
less than 1 second. The database had a mysterious crash a few months ago
(some tables lost their content) and the performance has been bad ever
since. I don't know the details of this crash since I just inherited the
system recently and unfortunately no logfiles are left. Could the crash
somehow corrupt catalog files so that the querying gets slower? I know this
is a long shot but I don't know what else to think of.

Anyways thanks a lot for your help.

Regards

MP

In response to

Responses

pgsql-performance by date

Next:From: Dawid KuroczkoDate: 2007-06-25 10:01:44
Subject: Is AIX Concurrent IO safe with PostgreSQL?
Previous:From: PFCDate: 2007-06-23 22:27:32
Subject: Re: PREPARE and stuff

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group