Re: Changing optimizations - Mailing list pgsql-general
From | Philip Molter |
---|---|
Subject | Re: Changing optimizations |
Date | |
Msg-id | 20010705102817.Z12723@datafoundry.net Whole thread Raw |
In response to | Re: Changing optimizations (Tom Lane <tgl@sss.pgh.pa.us>) |
Responses |
Re: Changing optimizations
(Tom Lane <tgl@sss.pgh.pa.us>)
|
List | pgsql-general |
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
pgsql-general by date: