Advice on tuning slow query

[Samuel Stearns <SStearns@xxxxxxxxxxxxxxxx>]

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