Re: Changing optimizations

On Thu, Jul 05, 2001 at 11:19:01AM -0400, Tom Lane wrote:
: Philip Molter <philip(at)datafoundry(dot)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(at)datafoundry(dot)net