Slow indexscan

From: "Mikko Partio" <mpartio(at)gmail(dot)com>
To: pgsql-performance(at)postgresql(dot)org
Subject: Slow indexscan
Date: 2007-06-20 14:02:25
Message-ID: 2ca799770706200702y1074f066s61279b67f93315d0@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-performance

Hello group

I have a problem with a simple index scan using the primary key of a table
taking too long.

Relevant facts:
pg version 7.3.4 (yeah very old, we are upgrading asap)

postgresql.conf:
shared_buffers = 25000
random_page_cost = 2
effective_cache_size = 200000
sort_mem = 20000

Table:
db=# \d tbl_20070601
Table "public.tbl_20070601"
Column | Type | Modifiers
------------------+-----------------------+-----------
validtime | bigint | not null
latitude | double precision | not null
longitude | double precision | not null
.....
parname | character varying(20) | not null
....
(table has a lot of columns but these are the most important ones)

Indexes: tbl_20060601_pkey primary key btree (validtime, latitude,
longitude, ..., parname, ...)

Validtime is a timestamp for the row (not my design).

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

QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Index Scan using tbl_20070601_pkey on tbl_20070601 t1
(cost=0.00..365.13rows=13 width=137) (actual time=
120.83..10752.64 rows=539 loops=1)
Index Cond: ((validtime >= 20070602000000::bigint) AND (validtime <=
20070602235500::bigint) AND (latitude = 60.2744::double precision) AND
(longitude = 26.4417::double precision))
Filter: (parname = 'temperature'::character varying)
Total runtime: 10753.85 msec
(4 rows)

db=# select count(*) from tbl_20070601;
count
---------
3715565
(1 row)

the query is only returning 539 rows but it takes more than 10 seconds to
execute. The table has only inserts and never deletes or updates and it has
been analyzed recently.

Is there anything to tweak with the query and/or postgresql, or should the
hardware be inspected? Server is 2-CPU 4GB RAM blade-server with a fibre
connection to a disk subsystem. Any more information I can give about the
system?

Regards

MP

Responses

Browse pgsql-performance by date

  From Date Subject
Next Message Kevin Hunter 2007-06-20 14:26:58 Re: PostgreSQL Configuration Tool for Dummies
Previous Message Andreas Kostyrka 2007-06-20 13:53:49 Re: Maintenance question / DB size anomaly...