The last part, the EXPLAIN, is too big to send. Is there an alternative way I
can get it too you, other than chopping it up and sending in multiple parts?
Thank you,
Sam
From: Samuel Stearns
Sent: Thursday, 3 October 2013 10:34 AM
To: Samuel Stearns; pgsql-performance@postgresql.org
Subject: RE: 57 minute SELECT
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-ow...@postgresql.org
[mailto:pgsql-performance-ow...@postgresql.org] On Behalf Of Samuel Stearns
Sent: Thursday, 3 October 2013 10:26 AM
To: pgsql-performance@postgresql.org
Subject: [PERFORM] 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
