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

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