Re: 57 minute SELECT

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(at)postgresql(dot)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-owner(at)postgresql(dot)org [mailto:pgsql-performance-owner(at)postgresql(dot)org] On Behalf Of Samuel Stearns
Sent: Thursday, 3 October 2013 10:26 AM
To: pgsql-performance(at)postgresql(dot)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