From: Mikko Partio <mpartio@gmail.com>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: pgsql-performance@postgresql.org
Subject: Re: Slow indexscan
Date: Mon, 25 Jun 2007 10:50:48 +0300
Message-ID: <2ca799770706250050x225525f8lad01b4e69e2d1743@mail.gmail.com> (raw)
In-Reply-To: <29484.1182367794@sss.pgh.pa.us>
References: <2ca799770706200702y1074f066s61279b67f93315d0@mail.gmail.com>
<17907.1182352197@sss.pgh.pa.us>
<2ca799770706200855w1a6b6a7cy8526b9be0ef9c97c@mail.gmail.com>
<18952.1182355276@sss.pgh.pa.us>
<2ca799770706200943l6ae9f6bfif54683e21fa33f35@mail.gmail.com>
<29484.1182367794@sss.pgh.pa.us>
On 6/20/07, Tom Lane <tgl@sss.pgh.pa.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
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: mpartio@gmail.com, tgl@sss.pgh.pa.us
Subject: Re: Slow indexscan
In-Reply-To: <2ca799770706250050x225525f8lad01b4e69e2d1743@mail.gmail.com>
* 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