Howdy,
Environment:
Postgres 8.4.15
Ubuntu 10.04
Syslog view def:
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_201304 AS
ON INSERT TO syslog
WHERE new.datetime >= '2013-04-01'::date AND new.datetime <
'2013-05-01'::date DO INSTEAD INSERT INTO syslog_201304 (ip, facility, level,
datetime, program, msg)
VALUES (new.ip, new.facility, new.level, new.datetime, new.program, new.msg)
syslog_insert_201305 AS
ON INSERT TO syslog
WHERE new.datetime >= '2013-05-01'::date AND new.datetime <
'2013-06-01'::date DO INSTEAD INSERT INTO syslog_201305 (ip, facility, level,
datetime, program, msg)
VALUES (new.ip, new.facility, new.level, new.datetime, new.program, new.msg)
syslog_insert_201306 AS
ON INSERT TO syslog
WHERE new.datetime >= '2013-06-01'::date AND new.datetime <
'2013-07-01'::date DO INSTEAD INSERT INTO syslog_201306 (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
Devices table def:
nms=# \d devices
Table "public.devices"
Column | Type | Modifiers
------------------+-----------------------------+------------------------------------------------------
id | integer | not null default
nextval('devices_id_seq'::regclass)
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
Mongroups table def:
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)
The following SELECT runs for 86 seconds on average:
SELECT syslog.ip,
syslog.msg,
syslog.datetime,
devices.hostname,
devices.hostpop
FROM syslog,
devices
WHERE syslog.ip IN
(SELECT ip
FROM devices,
mongroups
WHERE (active = 't'
OR active = 's')
AND devices.hostgroup = mongroups.hostgroup
AND devices.hostname || '.' || devices.hostpop = 'pe1.mel4'
AND devices.id != '1291')
AND datetime <= '2013-04-24 00:00:00'
AND datetime >= '2013-04-21 00:00:00' AND syslog.ip = devices.ip AND (
devices.active = 't'
OR devices.active = 's' );
Is there anything I can do to get the SELECT to run a little quicker.
Thank you,
Samuel Stearns
