Hello,
I have a handful of queries that are performing very slowly. I realize that I
will be hitting hardware limits at some point, but want to make sure Ive
squeezed out every bit of performance I can before calling it quits.
Our database is collecting traffic data at the rate of about 3 million rows a
day. The most important columns in the table being queried are timestamp,
volume, occupancy, and speed. I have also denormalized the table by adding
road name, direction, mile marker, and lane type values to eliminate joins with
other tables that contain information about the devices that collect this
information. A typical query will involve segments of roadway (i.e. road
names, directions, and mile marker bounds) over a certain period of time (e.g.
morning rush hour), and will have filters to exclude questionable data such
(e.g. speed > 100 MPH). Unfortunately, there are also a few cases in which a
user will query data for many full days on all roadways, essentially querying
everything for a large period of time. One other thing to note is that we only
ever query readings with lane_type = through_lanes, although we are collecting
ramp and reversible lane data to facilitate future reporting needs.
Table Metadata:
- Volume typically ranges anywhere from 0 to 20, averages around 4 5. A small
percentage of the rows have null volume.
- Occupancy ranges from 1 to 10, averages around 1 or 2
- Speed is about what you would expect, ranging from 30 70 with an average
somewhere near the middle
- There are 17 roads
- There are 2 directions per road
- Mile marker ranges vary by roadway, typical ranges are something like 0 to 40
or 257 to 290
- Most (80 to 90% +) of the readings have lane_type = through_lanes
- Size of a daily table is about 360MB, a half month table is 5 to 6 GB
Full Table and Index Schema:
Ive experimented with partitioning using a table per day and 2 tables per month
(1st through 15th, 16th to end of month). 2 tables/month was the original
approach to keep the number of tables from growing too rapidly, and shows about
3x slower performance. Using daily tables incurs extra planning overhead as
expected, but isnt all that bad. Im OK with taking a 1 second planning hit if
my overall query time decreases significantly. Furthermore, we will only be
storing raw data for about a year and can aggregate old data. This means that
I can do daily tables for raw data and larger/fewer tables for older data. The
table and index structure is below, which is identical between daily and ½
month tables with a couple of exceptions:
- Daily tables have a fill factor of 100, ½ month tables are default
- Only the 4 column indexes were created for the daily tables since the others
never get used
CREATE TABLE vds_detector_data
(
reading_timestamp timestamp without time zone,
vds_id integer,
detector_id integer,
status smallint,
speed numeric(12,9),
volume numeric(12,9),
confidence smallint,
occupancy numeric(12,9),
loadid bigint,
road_name character varying(150),
road_dir character varying(2),
mile_marker numeric(7,2),
lane_number integer,
lane_type character varying(32),
CONSTRAINT vds_detector_vdsid_fkey FOREIGN KEY (vds_id, detector_id)
REFERENCES ref_vds_detector_properties (device_id, detector_id) MATCH
SIMPLE
ON UPDATE CASCADE ON DELETE RESTRICT
)
WITH (
OIDS=FALSE
);
CREATE INDEX vds_detector_data_dir_idx
ON vds_detector_data
USING btree
(road_dir);
CREATE INDEX vds_detector_data_lane_idx
ON vds_detector_data
USING btree
(lane_number);
CREATE INDEX vds_detector_data_mm_idx
ON vds_detector_data
USING btree
(mile_marker);
CREATE INDEX vds_detector_data_occupancy_idx
ON vds_detector_data
USING btree
(occupancy);
CREATE INDEX vds_detector_data_road_idx
ON vds_detector_data
USING btree
(road_name);
CREATE INDEX vds_detector_data_road_ts_mm_dir_idx
ON vds_detector_data
USING btree
(road_name, reading_timestamp, mile_marker, road_dir);
CREATE INDEX vds_detector_data_speed_idx
ON vds_detector_data
USING btree
(speed);
CREATE INDEX vds_detector_data_timestamp_idx
ON vds_detector_data
USING btree
(reading_timestamp);
CREATE INDEX vds_detector_data_ts_road_mm_dir_idx
ON vds_detector_data
USING btree
(reading_timestamp, road_name, mile_marker, road_dir);
CREATE INDEX vds_detector_data_volume_idx
ON vds_detector_data
USING btree
(volume);
EXPLAIN ANALYZE:
Query:
select cast(reading_timestamp as Date) as date, floor(extract(hour from
reading_timestamp) / 1.0) * 1.0 as hour, floor(extract(minute from
reading_timestamp) / 60) * 60 as min,
count(*), sum(vdd.volume) as totalVolume,
sum(vdd.occupancy*vdd.volume)/sum(vdd.volume) as avgOcc,
sum(vdd.speed*vdd.volume)/sum(vdd.volume) as avgSpeed,
avg(vdd.confidence) as avgConfidence, min(vdd.detector_id) as detectorId,
vdd.vds_id as vdsId
from vds_detector_data vdd
where (vdd.reading_timestamp between '2011-4-01 00:00:00.000' and '2011-04-30
23:59:59.999')
and vdd.volume!=0
and ((road_name='44' and mile_marker between 257.65 and 289.5 and
(road_dir='E' or road_dir='W'))
or (road_name='64' and mile_marker between 0.7 and 40.4 and (road_dir='E'
or road_dir='W'))
or (road_name='55' and mile_marker between 184.8 and 208.1 and
(road_dir='N' or road_dir='S'))
or (road_name='270' and mile_marker between 0.8 and 34.5 and
(road_dir='N' or road_dir='S')))
and not(vdd.speed<0.0 or vdd.speed>90.0 or vdd.volume=0.0) and vdd.lane_type
in ('through_lanes')
group by date, hour, min, vdd.vds_id, mile_marker
having sum(vdd.volume)!=0
order by vdd.vds_id, mile_marker;
Daily table explain analyze: http://explain.depesz.com/s/iLY
Half month table explain analyze: http://explain.depesz.com/s/Unt
Postgres version:
PostgreSQL 8.4.8, compiled by Visual C++ build 1400, 32-bit
History:
None, this is a new database and application
Hardware:
- 2 Intel Xeon 2.13GHz processors with 8 cores each
- 8GB RAM
- Disks are in a RAID 5 configuration with a PERC H700 Integrated RAID
Controller 512MB Cache
- 5 disks, Seagate 7200 RPM SAS, 500GB each for a total capacity of about 2TB
- Windows Server 2008 R2 64-bit (but 32-bit postgres)
- Hardware upgrades arent an option at this point due to budget and time
constraints
Maintenance Setup:
Autovacuum is disabled for these tables since the data is never updated. The
tables that we are testing with at the moment will not grow any larger and have
been both clustered and analyzed. They were clustered on the
vds_detector_data_timestamp_idx index.
GUC Settings:
effective_cache_size: 2048MB
work_mem: 512MB
shared_buffers: 64MB, 512MB, and 1024MB, each yielded the same query plan and
took the same amount of time to execute give or take a few seconds
Summary:
The time to get the raw data (before aggregation and sorting) is relatively
similar between the daily and half month tables. It would appear that the
major difference is the ordering of sort and aggregation, the daily tables
aggregate first so the amount of data sorted is significantly less.
Since the daily tables are only 360MB, I would hope that the entire table could
be pulled into memory with one large sequential read. Of course this assumes
that the file pieces are stored contiguously, but auto defrag is enabled and
shows low fragmentation so Im trusting (as much as one can) Windows to do the
right thing here. My drives have a 150MB/s sustained max throughput, and
considering that data is spread across 5 drives I would hope to at least be
able to reach the single disk theoretical limit and read an entire table plus
the index into memory about 4 to 5 seconds. Based on the analyze output, each
daily table averages 6 to 7 seconds, so Im pretty close there and maybe just
limited by disk speed?
In both cases, the row estimates vs actual are way off. Ive increased
statistics on the reading_timestamp and road_name columns to 100 and then 1000
with no change. I ran an ANALYZE after each statistics change. Should I be
upping stats on the non-indexed columns as well? Ive read documentation that
says I should be able to set statistics values for an entire table as opposed
to per column, but havent found how to do that. I guess I was either too lazy
to update statistics on each column or just didnt think it would help much.
So, any pointers for performance improvement?
Thanks,
Craig
Open Roads Consulting, Inc.
757-546-3401
http://www.openroadsconsulting.com
This e-mail communication (including any attachments) may contain confidential
and/or privileged material intended solely for the individual or entity to
which it is addressed. If you are not the intended recipient, you should
immediately stop reading this message and delete it from all computers that it
resides on. Any unauthorized reading, distribution, copying or other use of
this communication (or its attachments) is strictly prohibited. If you have
received this communication in error, please notify us immediately.
This e-mail communication (including any attachments) may contain confidential
and/or privileged material intended solely for the individual or entity to
which it is addressed.
P - Think before you print.
