Re: Changing optimizations

On Thu, Jul 05, 2001 at 11:19:01AM -0400, Tom Lane wrote:
: Philip Molter <philip@datafoundry.net> writes:
: > If someone could, please explain the following.
:
: Difficult to do, when you haven't shown us the query nor the table
: schemas.

Well, I hesitated to do it since the table definitions are so long.

Here's the query (functions have been defined for the time being for
UNIX_TIMESTAMP() and IFNULL()):

       SELECT h.hid, h.sysname, h.snmpaddr, h.snmpcomm, h.hostgroupid,
             h.active, h.lowalert, h.os, h.osrev, h.platform,
             UNIX_TIMESTAMP( p.nextrun ) AS nextrun, p.pid, p.pkdwid as dwid,
             p.deleted, pt.units, pt.tablename, pt.classname, pt.description,
             pt.logtype, IFNULL( sl.state, 0 ) AS state,
             UNIX_TIMESTAMP( sl.start_time ) AS sctstamp,
             sl.state AS log_state,
             IFNULL( p.runinterval, pt.runinterval ) AS runinterval,
             IFNULL( SUM( sd.state >> 1 ), 0 ) AS dephold,
             IFNULL( pth.d1_time, ptt.d1_time ) AS d1_time,
             IFNULL( pth.d1_min, ptt.d1_min ) AS d1_min,
             IFNULL( pth.d1_max, ptt.d1_max ) AS d1_max,
             IFNULL( pth.d2_time, ptt.d2_time ) AS d2_time,
             IFNULL( pth.d2_min, ptt.d2_min ) AS d2_min,
             IFNULL( pth.d2_max, ptt.d2_max ) AS d2_max,
             p.running, plf.logfield, plf.min, plf.max,
             wft.maptype AS logfield_type
        FROM percept p
             INNER JOIN perceptType pt ON pt.ptid=p.ptid
               AND pt.runinterval IS NOT NULL
             INNER JOIN hosts h ON h.hid=p.hid
             LEFT JOIN perceptThreshold pth ON pth.pid=p.pid
             LEFT JOIN stateSummary sl ON sl.pid=p.pid
             LEFT JOIN perceptDepCache pdc ON pdc.pid=p.pid
             LEFT JOIN stateSummary sd ON pdc.dep_pid=sd.pid
             LEFT JOIN perceptLogField plf ON p.pid=plf.pid
             LEFT JOIN perceptTypeThreshold ptt ON p.ptid=ptt.ptid
             LEFT JOIN warehouseFieldType wft ON plf.type=wft.fieldtype AND
               pt.logtype=wft.logtype
       WHERE p.deleted=0 AND UNIX_TIMESTAMP( p.nextrun )<=NOW() AND
             pt.runinterval IS NOT NULL AND p.running=0 AND h.active=1
    GROUP BY h.hid, h.sysname, h.snmpaddr, h.snmpcomm, h.hostgroupid,
             h.active, h.lowalert, h.os, h.osrev, h.platform,
             p.nextrun, p.pid, p.deleted, pt.units, pt.tablename,
             pt.classname, pt.description, sl.state, sl.start_time,
             p.running, plf.logfield, plf.min, plf.max,
             pt.logtype, p.pkdwid, wft.maptype,
             IFNULL( p.runinterval, pt.runinterval ),
             IFNULL( pth.d1_time, ptt.d1_time ),
             IFNULL( pth.d1_min, ptt.d1_min ),
             IFNULL( pth.d1_max, ptt.d1_max ),
             IFNULL( pth.d2_time, ptt.d2_time ),
             IFNULL( pth.d2_min, ptt.d2_min ),
             IFNULL( pth.d2_max, ptt.d2_max )
      HAVING SUM( sd.state >> 1 ) = 0 OR SUM( sd.state >> 1 ) IS NULL

Every field being used in the JOINs and the WHERE clause is indexed (or
a primary key).

I can post the schemas for all the tables if it's really necessary, but
since the only two tables that seem to be affected are percept and
stateSummary, I'll post those for now.

  CREATE SEQUENCE percept_pid_seq;

  CREATE TABLE percept (
    pid INTEGER DEFAULT nextval('percept_pid_seq'),
    hid INTEGER NOT NULL DEFAULT 0,
    pkdwid INTEGER NOT NULL DEFAULT 1,
    ptid INTEGER NOT NULL DEFAULT 0,
    nextrun TIMESTAMP NOT NULL DEFAULT 'epoch',
    runinterval INTEGER DEFAULT NULL,
    pkwid INTEGER DEFAULT NULL,
    deleted SMALLINT NOT NULL DEFAULT 0,
    running SMALLINT NOT NULL DEFAULT 0,
    PRIMARY KEY (pid)
  );

  CREATE INDEX deleted_p_index ON percept (deleted);
  CREATE INDEX hid_p_index ON percept (hid);
  CREATE INDEX ptid_p_index ON percept (ptid);
  CREATE INDEX nextrun_p_index ON percept (nextrun);
  CREATE INDEX running_p_index ON percept (running);

  CREATE TABLE stateSummary (
    pid INTEGER NOT NULL DEFAULT 0,
    state SMALLINT DEFAULT NULL,
    start_time TIMESTAMP NOT NULL,
    PRIMARY KEY (pid)
  );

* Philip Molter
* DataFoundry.net
* http://www.datafoundry.net/
* philip@datafoundry.net