|
Ok, let’s try 3 parts: Table counts: syslog – 150200285 devices – 3291 mongroups – 71 The query: 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 ~* E'pe1.mel4' AND devices.id != '1291') AND datetime <= '2013-08-01 00:00:00' AND datetime >= '2013-04-12 00:00:00' AND syslog.ip = devices.ip AND (devices.active = 't' OR devices.active = 's'); <end part II> Thank you, Sam From: pgsql-performance-owner@xxxxxxxxxxxxxx
[mailto:pgsql-performance-owner@xxxxxxxxxxxxxx] On Behalf Of Samuel Stearns 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 |
