57 minute SELECT

Howdy,

I'm going to post this in 2 parts as I think it's too big for 1 post.

Environment:

PG 8.4.17
Linux Ubuntu 10.04
Total RAM - 1G

Things that have been performed:

1. Explain on SELECT.

2. ANALYZE database.

3. VACUUM database.

4. shared_buffers = 256M

5. effective_cache_size = 768M

6. work_mem = 512M

Table DDL:

nms=# \d syslog
View "public.syslog"
Column | Type | Modifiers
----------+-----------------------------+-----------
ip | inet |
facility | character varying(10) |
level | character varying(10) |
datetime | timestamp without time zone |
program | character varying(25) |
msg | text |
seq | bigint |
View definition:
SELECT syslog_master.ip, syslog_master.facility, syslog_master.level, syslog_master.datetime, syslog_master.program, syslog_master.msg, syslog_master.seq
FROM syslog_master;
Rules:
syslog_insert_201308 AS
ON INSERT TO syslog
WHERE new.datetime >= '2013-08-01'::date AND new.datetime < '2013-09-01'::date DO INSTEAD INSERT INTO syslog_201308 (ip, facility, level, datetime, program, msg)
VALUES (new.ip, new.facility, new.level, new.datetime, new.program, new.msg)
syslog_insert_201309 AS
ON INSERT TO syslog
WHERE new.datetime >= '2013-09-01'::date AND new.datetime < '2013-10-01'::date DO INSTEAD INSERT INTO syslog_201309 (ip, facility, level, datetime, program, msg)
VALUES (new.ip, new.facility, new.level, new.datetime, new.program, new.msg)
syslog_insert_201310 AS
ON INSERT TO syslog
WHERE new.datetime >= '2013-10-01'::date AND new.datetime < '2013-11-01'::date DO INSTEAD INSERT INTO syslog_201310 (ip, facility, level, datetime, program, msg)
VALUES (new.ip, new.facility, new.level, new.datetime, new.program, new.msg)
syslog_insert_null AS
ON INSERT TO syslog DO INSTEAD NOTHING

nms=#

nms=# \d devices
hostname | character varying(20) |
hostpop | character varying(20) |
hostgroup | character varying(20) |
rack | character varying(10) |
asset | character varying(10) |
ip | inet |
snmprw | character varying(20) |
snmpro | character varying(20) |
snmpver | character varying(3) |
console | character varying(20) |
psu1 | character varying(20) |
psu2 | character varying(20) |
psu3 | character varying(20) |
psu4 | character varying(20) |
alias1 | character varying(20) |
alias2 | character varying(20) |
failure | character varying(255) |
modified | timestamp without time zone | not null default now()
modified_by | character varying(20) |
active | character(1) | default 't'::bpchar
rad_secret | character varying(20) |
rad_atr | character varying(40) |
snmpdev | integer |
netflow | text |
cpu | integer |
temp | integer |
firmware_type_id | bigint | default 1
Indexes:
"id_pkey" PRIMARY KEY, btree (id)
"devices_active_index" btree (active)
"devices_failure" btree (failure)
"devices_hostgroup" btree (hostgroup)
"devices_hostname" btree (hostname)
"devices_hostpop" btree (hostpop)
"devices_ip_index" btree (ip)
"devices_snmprw" btree (snmprw)
Foreign-key constraints:
"devices_firmware_type_id_fkey" FOREIGN KEY (firmware_type_id) REFERENCES firmware_type(id)
Referenced by:
TABLE "ac_attributes" CONSTRAINT "ac_attributes_id_fkey" FOREIGN KEY (id) REFERENCES devices(id) ON DELETE CASCADE
TABLE "acls_matrix" CONSTRAINT "acls_matrix_device_id_fkey" FOREIGN KEY (device_id) REFERENCES devices(id) ON UPDATE CASCADE ON DELETE CASCADE
TABLE "ip_local_pool_aggregates" CONSTRAINT "ip_local_pool_aggregates_host_fkey" FOREIGN KEY (host) REFERENCES devices(id)
TABLE "ipsla_instances" CONSTRAINT "ipsla_instances_host_fkey" FOREIGN KEY (host) REFERENCES devices(id) ON DELETE CASCADE
TABLE "lns_attributes" CONSTRAINT "lns_attributes_id_fkey" FOREIGN KEY (id) REFERENCES devices(id) ON DELETE CASCADE

(END)

nms=# \d mongroups
Table "public.mongroups"
Column | Type | Modifiers
------------+-----------------------+-----------
hostgroup | character varying(20) |
locale | text |
department | character varying(20) |
Indexes:
"ukey_hostgroup_department" UNIQUE, btree (hostgroup, department)

nms=#

<end part I>

Thank you,

Sam