From: Dan Harris <fbsd@drivefaster.net>
To: PostgreSQL Performance <pgsql-performance@postgresql.org>
Subject: Help optimizing a slow index scan
Date: Thu, 16 Mar 2006 14:40:32 -0700
Message-ID: <4419DB50.10404@drivefaster.net> (raw)
explain analyze
select distinct eventmain.incidentid, eventmain.entrydate,
eventgeo.long, eventgeo.lat, eventgeo.geox, eventgeo.geoy
from eventmain, eventgeo
where
eventmain.incidentid = eventgeo.incidentid and
( long > -104.998027962962 and long < -104.985957781349 ) and
( lat > 39.7075542720006 and lat < 39.7186195832938 ) and
eventmain.entrydate > '2006-1-1 00:00' and
eventmain.entrydate <= '2006-3-17 00:00'
order by
eventmain.entrydate;
QUERY
PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Unique (cost=121313.81..121330.72 rows=451 width=178) (actual
time=723719.761..723726.875 rows=1408 loops=1)
-> Sort (cost=121313.81..121314.94 rows=451 width=178) (actual
time=723719.755..723721.807 rows=1408 loops=1)
Sort Key: eventmain.entrydate, eventmain.disposition,
eventmain.incidentid, eventgeo.reportingarea, eventgeo.beatid,
eventmain.finaltype, eventmain.casenumber, eventgeo.eventlocation,
eventmain.insertdate, eventmain.priority, eventgeo.long, eventgeo.lat,
eventgeo.geox, eventgeo.geoy
-> Nested Loop (cost=0.00..121293.93 rows=451 width=178)
(actual time=1916.230..723712.900 rows=1408 loops=1)
-> Index Scan using eventgeo_lat_idx on eventgeo
(cost=0.00..85488.05 rows=10149 width=76) (actual time=0.402..393376.129
rows=22937 loops=1)
Index Cond: ((lat > 39.7075542720006::double
precision) AND (lat < 39.7186195832938::double precision))
Filter: ((long > -104.998027962962::double
precision) AND (long < -104.985957781349::double precision))
-> Index Scan using eventmain_incidentid_idx on
eventmain (cost=0.00..3.52 rows=1 width=119) (actual
time=14.384..14.392 rows=0 loops=22937)
Index Cond: ((eventmain.incidentid)::text =
("outer".incidentid)::text)
Filter: ((entrydate > '2006-01-01
00:00:00'::timestamp without time zone) AND (entrydate <= '2006-03-17
00:00:00'::timestamp without time zone))
Total runtime: >>> 723729.238 ms(!) <<<
I'm trying to figure out why it's consuming so much time on the index
scan for eventgeo_lat_idx. Also, I have an index on "long" that the
planner does not appear to find helpful.
There are 3.3 million records in eventmain and eventgeo. The server has
a reasonably fast RAID10 setup with 16x 15k RPM drives and 12GB of RAM (
11GB listed as "cache" by vmstat ). Running version 8.0.2 on linux
kernel 2.6.12.
I have just vacuum analyze'd both tables, rebuilt the eventgeo_lat_idx
index and reran the query multiple times to see if caching helped ( it
didn't help much ). The server seems to be fine utilizing other fields
from this table but using "long" and "lat" seem to drag it down
significantly.
Is it because there's such slight differences between the records,
since they are all within a few hundredths of a degree from each other?
Thanks for your time and ideas.
-Dan
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: fbsd@drivefaster.net
Subject: Re: Help optimizing a slow index scan
In-Reply-To: <4419DB50.10404@drivefaster.net>
* 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