Re: Huge Performance Difference on Similar Query in Pg7.2
От | Heiko Klein |
---|---|
Тема | Re: Huge Performance Difference on Similar Query in Pg7.2 |
Дата | |
Msg-id | 15515.7254.337677.120558@polar.oslo.dnmi.no обсуждение исходный текст |
Ответ на | Re: Huge Performance Difference on Similar Query in Pg7.2 ("Luis Alberto Amigo Navarro" <lamigo@atc.unican.es>) |
Список | pgsql-general |
Luis Alberto Amigo Navarro writes: > In fact it seems it's no planner's mistake, you would need for some indexing > how is the table layout, and what indexes u have? > Those are the main tables: CREATE TABLE DataSets (setid INTEGER NOT NULL, areaid INTEGER NULL, reportid INTEGER NOT NULL, datatype VARCHAR(12) NULL, component VARCHAR(12) NULL, year INTEGER NULL, updated DATE NULL, scaling INTEGER NULL, xcomment VARCHAR(2000) NULL, quality CHARACTER(1) DEFAULT '9' NULL, PRIMARY KEY (setid), FOREIGN KEY (areaid) REFERENCES Areas(areaid), FOREIGN KEY (reportid) REFERENCES Reports(reportid), FOREIGN KEY (datatype) REFERENCES DataTypes(datatype), FOREIGN KEY (component) REFERENCES Components(component) ); Additional indexes on areaid, reportid (as far as I understood, Primary key is allways a index?) CREATE TABLE LowHiSectorGridEmissions (setid integer not null, sector integer not null, x integer not null, y integer not null, lowemission integer null, highemission integer null, footnoteid INTEGER NULL, PRIMARY KEY (setid, sector, x, y), FOREIGN KEY (footnoteid) REFERENCES FootNotes(footnoteid), FOREIGN KEY (setid) REFERENCES DataSets ); Additional indexes on setid, sector, x, y. The main Views: CREATE VIEW DataSetsView AS SELECT setid, DataSets.areaid AS areaid, Areas.name AS name, Areas.acronym AS acronym, DataSets.reportid AS reportid, reportcode, reportyear, datereceived, datatype, origin, component, year, (SUBSTRING(origin FROM 1 FOR 1) || SUBSTRING(datatype FROM 1 FOR 2) || '-' || TRIM(BOTH ' ' FROM component) || '-' || TRIM(BOTH ' ' FROM Areas.acronym) || SUBSTRING(year FROM 3 FOR 2) || SUBSTRING (reportyear FROM 3 FOR 2) ) AS datasetcode, updated, scaling, DataSets.xcomment AS xcomment, datesent, datemscw, quality FROM DataSets, Areas, ReportsView WHERE DataSets.areaid = Areas.areaid AND DataSets.reportid = ReportsView.reportid ; CREATE VIEW LowHiSectorGridEmissionsView AS SELECT LowHiSectorGridEmissions.setid AS setid, LowHiSectorGridEmissions.sector AS sector, sectorcode, x, y, scaling, ('EMIS_' || scaling || '_0x' || int2hex(LowHiSectorGridEmissions.lowemission) ) AS exlowemission, ('EMIS_' || scaling || '_0x' || int2hex(LowHiSectorGridEmissions.highemission) ) AS exhighemission, ('0x' || int2hex(LowHiSectorGridEmissions.lowemission) ) AS rawlowemission, ('0x' || int2hex(LowHiSectorGridEmissions.highemission) ) AS rawhighemission, FootNotes.footnote AS footnote FROM DataSetsView, LowHiSectorGridEmissions, DataTypes, SectorDefinitions, FootNotes WHERE DataSetsView.setid = LowHiSectorGridEmissions.setid AND DataSetsView.datatype = DataTypes.datatype AND DataTypes.sectordefinition = SectorDefinitions.sectordefinition AND LowHiSectorGridEmissions.sector = SectorDefinitions.sector AND ( LowHiSectorGridEmissions.footnoteid = FootNotes.footnoteid OR FootNotes.footnoteid = '0') ; Area, Footnotes, Components, DataTypes, SectorDefinitions are all small tables. I tried also to add indexes to all things, which are part of the where clauses of the views. Without success. Heiko Below I add the complete tabledescribtion: -- $Author$ -- $Date$ -- $Header$ -- $Id$ -- $Name$ -- $Locker$ -- $Log$ -- $Revision$ -- $Source$ -- $State$ CREATE TABLE AreaCollections (collection varchar(12) not null, acronyms varchar(2000) null, description varchar(100) null, PRIMARY KEY (collection) ); CREATE TABLE Areas (areaid INTEGER NOT NULL, includedin INTEGER NULL, name VARCHAR(100) NULL, acronym VARCHAR(12) NULL, areastart DATE NULL, areaend DATE NULL, PRIMARY KEY (areaid), FOREIGN KEY (includedin) REFERENCES Areas(areaid) ); CREATE TABLE ComponentCategories (componentcategory VARCHAR(12) NOT NULL, description VARCHAR(100) NULL, PRIMARY KEY (componentcategory) ); CREATE TABLE Components (component VARCHAR(12) NOT NULL, componentcategory VARCHAR(12) NOT NULL, name VARCHAR(100) NULL, PRIMARY KEY (component), FOREIGN KEY (componentcategory) REFERENCES ComponentCategories(componentcategory) ); CREATE TABLE SectorDefinitions (sdid integer not null, sectordefinition varchar(15) not null, sector integer not null, sectorcode varchar(20) not null, anthropogenic integer not null, description varchar(2000) null, PRIMARY KEY (sectordefinition, sector), UNIQUE (sdid) ); CREATE TABLE GridDefinitions (griddefinition varchar(12) not null, xlow integer null, xhigh integer null, ylow integer null, yhigh integer null, description varchar(2000) null, PRIMARY KEY (griddefinition) ); CREATE TABLE GridAllocations (griddefinition VARCHAR(12) NOT NULL, areaid INTEGER NOT NULL, x INTEGER NOT NULL, y INTEGER NOT NULL, fraction VARCHAR(12) NULL, PRIMARY KEY (griddefinition, areaid, x, y), FOREIGN KEY (areaid) REFERENCES Areas(areaid), FOREIGN KEY (griddefinition) REFERENCES GridDefinitions(griddefinition) ); CREATE TABLE DataTypes (datatype VARCHAR(12) NOT NULL, sectordefinition VARCHAR(15) NULL, griddefinition VARCHAR(12) NULL, description VARCHAR(2000) NULL, datatable VARCHAR(100) NULL, PRIMARY KEY (datatype), FOREIGN KEY (sectordefinition) REFERENCES SectorDefinitions(sectordefinition), FOREIGN KEY (griddefinition) REFERENCES GridDefinitions (griddefinition) ); create table Origins (origin varchar(12) not null, description varchar(2000) null, PRIMARY KEY (origin) ); CREATE TABLE Reports (reportid INTEGER NOT NULL, reportcode VARCHAR(100) NULL, areaid INTEGER NULL, datereceived DATE NULL, datesent DATE NULL, datemscw DATE NULL, origin VARCHAR(12) NULL, xcomment VARCHAR(2000) NULL, PRIMARY KEY (reportid), FOREIGN KEY (areaid) REFERENCES Areas(areaid) ); CREATE TABLE DataSets (setid INTEGER NOT NULL, areaid INTEGER NULL, reportid INTEGER NOT NULL, datatype VARCHAR(12) NULL, component VARCHAR(12) NULL, year INTEGER NULL, updated DATE NULL, scaling INTEGER NULL, xcomment VARCHAR(2000) NULL, quality CHARACTER(1) DEFAULT '9' NULL, PRIMARY KEY (setid), FOREIGN KEY (areaid) REFERENCES Areas(areaid), FOREIGN KEY (reportid) REFERENCES Reports(reportid), FOREIGN KEY (datatype) REFERENCES DataTypes(datatype), FOREIGN KEY (component) REFERENCES Components(component) ); create table DataCollections (collection varchar(12) not null, description varchar(2000) null, PRIMARY KEY (collection) ); CREATE TABLE CollectionSpecs (collection VARCHAR(12) NOT NULL, setid INTEGER NOT NULL, PRIMARY KEY (collection, setid), FOREIGN KEY (collection) REFERENCES DataCollections(collection), FOREIGN KEY (setid) REFERENCES DataSets(setid) ); CREATE TABLE FootNotes (footnoteid INTEGER NOT NULL, footnote VARCHAR(2000) NULL, reportid INTEGER NULL, PRIMARY KEY (footnoteid), FOREIGN KEY (reportid) REFERENCES Reports(reportid) ); CREATE TABLE Emissions (setid INTEGER NOT NULL, emission INTEGER NULL, xcomment VARCHAR(2000) NULL, footnoteid INTEGER NULL, PRIMARY KEY (setid), FOREIGN KEY (footnoteid) REFERENCES FootNotes(footnoteid), FOREIGN KEY (setid) REFERENCES DataSets ); create table GridEmissions (setid integer not null, x integer not null, y integer not null, emission integer null, footnoteid INTEGER NULL, PRIMARY KEY (setid, x, y), FOREIGN KEY (footnoteid) REFERENCES FootNotes(footnoteid), FOREIGN KEY (setid) REFERENCES DataSets ); create table PointEmissions (setid integer not null, lps varchar(12) not null, emission integer null, footnoteid INTEGER NULL, PRIMARY KEY (setid, lps), FOREIGN KEY (footnoteid) REFERENCES FootNotes(footnoteid), FOREIGN KEY (setid) REFERENCES DataSets ); create table LowHiEmissions (setid integer not null, lowemission integer null, highemission integer null, xcomment varchar(2000) null, footnoteid INTEGER NULL, PRIMARY KEY (setid), FOREIGN KEY (footnoteid) REFERENCES FootNotes(footnoteid), FOREIGN KEY (setid) REFERENCES DataSets ); create table LowHiGridEmissions (setid integer not null, x integer not null, y integer not null, lowemission integer null, highemission integer null, footnoteid INTEGER NULL, PRIMARY KEY (setid, x, y), FOREIGN KEY (footnoteid) REFERENCES FootNotes(footnoteid), FOREIGN KEY (setid) REFERENCES DataSets ); create table SectorEmissions (setid integer not null, sector integer not null, emission integer null, xcomment varchar(2000) null, footnoteid INTEGER NULL, PRIMARY KEY (setid, sector), FOREIGN KEY (footnoteid) REFERENCES FootNotes(footnoteid), FOREIGN KEY (setid) REFERENCES DataSets ); create table SectorGridEmissions (setid integer not null, sector integer not null, x integer not null, y integer not null, emission integer null, footnoteid INTEGER NULL, PRIMARY KEY (setid, sector, x, y), FOREIGN KEY (footnoteid) REFERENCES FootNotes(footnoteid), FOREIGN KEY (setid) REFERENCES DataSets ); create table LowHiSectorGridEmissions (setid integer not null, sector integer not null, x integer not null, y integer not null, lowemission integer null, highemission integer null, footnoteid INTEGER NULL, PRIMARY KEY (setid, sector, x, y), FOREIGN KEY (footnoteid) REFERENCES FootNotes(footnoteid), FOREIGN KEY (setid) REFERENCES DataSets ); create table LargePointSources (lpsid integer not null, areaid integer not null, lps varchar(5) not null, name varchar(100) null, latitude integer null, longitude integer null, height integer null, exitsurface integer null, speed integer null, temperature integer null, PRIMARY KEY (areaid, lps), FOREIGN KEY (areaid) REFERENCES Areas(areaid), UNIQUE (lpsid) ); create table UpdateSessions (sesid integer not null, updatetime date not null, logname varchar(30) not null, tablename varchar(30) not null, PRIMARY KEY (sesid) ); create table UpdateRecords (sesid integer not null, seqnum integer not null, updatetype varchar(12) not null, recordid varchar(12) null, -- recordid can but must not be the Records/recordid -- this is an awful design-flaw!!! setid integer null, sector integer null, x integer null, y integer null, PRIMARY KEY (sesid,seqnum), FOREIGN KEY (sesid) REFERENCES UpdateSessions(sesid) ); create table UpdateValues (sesid integer not null, seqnum integer not null, varname varchar(30) not null, oldval varchar(2000) null, newval varchar(2000) null, PRIMARY KEY (sesid, seqnum, varname), FOREIGN KEY (sesid) REFERENCES UpdateSessions(sesid) ); create table Globals (globid varchar(12) not null, globvalue integer null, PRIMARY KEY (globid) ); create table Batches (batchid varchar(12) not null, first integer not null, last integer not null, PRIMARY KEY (batchid, first) ) -- $Author$ -- $Date$ -- $Header$ -- $Id$ -- $Name$ -- $Locker$ -- $Log$ -- $Revision$ -- $Source$ -- $State$ -- This works only as superuser. On polar, plperl is compiled into template1 -- so will exist in each db created -- DROP LANGUAGE 'plperl'; -- CREATE LANGUAGE 'plperl'; DROP FUNCTION int2hex(integer); CREATE FUNCTION int2hex(integer) RETURNS char(10) AS '$tmp = sprintf "%x", $_[0]; return ("0"x(8-length($tmp)).uc($tmp));' LANGUAGE 'plperl'; DROP VIEW SectorDefView; CREATE VIEW SectorDefView AS SELECT DISTINCT sectordefinition FROM SectorDefinitions ; DROP VIEW GridAllocView; CREATE VIEW GridAllocView AS SELECT griddefinition, Areas.areaid AS areaid, name, acronym, x, y, fraction FROM GridAllocations, Areas WHERE GridAllocations.areaid = Areas.areaid ; DROP VIEW ReportsView; CREATE VIEW ReportsView AS SELECT reportid, to_char(datereceived,'YYYY') AS reportyear, reportcode, Areas.areaid AS areaid, acronym, name, to_char(datereceived,'YYYYMMDD') AS datereceived, to_char(datesent,'YYYYMMDD') AS datesent, to_char(datemscw,'YYYYMMDD') AS datemscw, origin, xcomment FROM Reports, Areas WHERE Reports.areaid = Areas.areaid ; DROP VIEW YearOfRepView; CREATE VIEW YearOfRepView AS SELECT DISTINCT reportyear FROM ReportsView ; DROP VIEW DataSetsView; CREATE VIEW DataSetsView AS SELECT setid, DataSets.areaid AS areaid, Areas.name AS name, Areas.acronym AS acronym, DataSets.reportid AS reportid, reportcode, reportyear, datereceived, datatype, origin, component, year, (SUBSTRING(origin FROM 1 FOR 1) || SUBSTRING(datatype FROM 1 FOR 2) || '-' || TRIM(BOTH ' ' FROM component) || '-' || TRIM(BOTH ' ' FROM Areas.acronym) || SUBSTRING(year FROM 3 FOR 2) || SUBSTRING (reportyear FROM 3 FOR 2) ) AS datasetcode, updated, scaling, DataSets.xcomment AS xcomment, datesent, datemscw, quality FROM DataSets, Areas, ReportsView WHERE DataSets.areaid = Areas.areaid AND DataSets.reportid = ReportsView.reportid ; DROP VIEW EmissionsView; CREATE VIEW EmissionsView AS SELECT Emissions.setid AS setid, areaid, name, acronym, DataSetsView.reportid, reportcode, reportyear, datereceived, datatype, origin, component, year, datasetcode, updated, scaling, Emissions.emission AS emission , ('EMIS_' || scaling || '_0x' || int2hex(Emissions.emission) ) AS exemission, Emissions.xcomment AS xcomment, FootNotes.footnote AS footnote, ('0x' || int2hex(Emissions.emission) ) AS rawemission FROM DataSetsView, Emissions, FootNotes WHERE DataSetsView.setid = Emissions.setid AND ( Emissions.footnoteid = FootNotes.footnoteid OR FootNotes.footnoteid = '0' ) ; DROP VIEW GridEmissionsView; CREATE VIEW GridEmissionsView AS SELECT GridEmissions.setid AS setid, x, y, scaling, GridEmissions.emission AS emission, ('EMIS_' || scaling || '_0x' || int2hex(GridEmissions.emission) ) AS exemission, ('0x' || int2hex(GridEmissions.emission) ) AS rawemission, FootNotes.footnote as footnote FROM DataSetsView, GridEmissions, FootNotes WHERE DataSetsView.setid = GridEmissions.setid AND ( GridEmissions.footnoteid = FootNotes.footnoteid OR FootNotes.footnoteid = '0') ; DROP VIEW LowHiEmissionsView; CREATE VIEW LowHiEmissionsView AS SELECT LowHiEmissions.setid AS setid, areaid, name, acronym, DataSetsView.reportid, reportcode, reportyear, datereceived, datatype, origin, component, year, datasetcode, updated, scaling, ('EMIS_' || scaling || '_0x' || int2hex(LowHiEmissions.lowemission) ) AS exlowemission, ('EMIS_' || scaling || '_0x' || int2hex(LowHiEmissions.highemission) ) AS exhighemission, LowHiEmissions.xcomment AS xcomment, ('0x' || int2hex(LowHiEmissions.lowemission) ) AS rawlowemission, ('0x' || int2hex(LowHiEmissions.highemission) ) AS rawhighemission, datesent, datemscw, FootNotes.footnote AS footnote FROM DataSetsView, LowHiEmissions, FootNotes WHERE DataSetsView.setid = LowHiEmissions.setid AND ( LowHiEmissions.footnoteid = FootNotes.footnoteid OR FootNotes.footnoteid = '0' ) ; DROP VIEW LowHiGridEmissionsView; CREATE VIEW LowHiGridEmissionsView AS SELECT LowHiGridEmissions.setid AS setid, x, y, scaling, ('EMIS_' || scaling || '_0x' || int2hex(LowHiGridEmissions.lowemission) ) AS exlowemission, ('EMIS_' || scaling || '_0x' || int2hex(LowHiGridEmissions.highemission) ) AS exhighemission, ('0x' || int2hex(LowHiGridEmissions.lowemission) ) AS rawlowemission, ('0x' || int2hex(LowHiGridEmissions.highemission) ) AS rawhighemission, FootNotes.footnote AS footnote FROM DataSetsView, LowHiGridEmissions, FootNotes WHERE DataSetsView.setid = LowHiGridEmissions.setid AND ( LowHiGridEmissions.footnoteid = FootNotes.footnoteid OR FootNotes.footnoteid = '0') ; DROP VIEW SectorEmissionsView; CREATE VIEW SectorEmissionsView AS SELECT SectorEmissions.setid AS setid, areaid, name, acronym, DataSetsView.reportid, reportcode, reportyear, datereceived, DataSetsView.datatype AS datatype, origin, component, year, datasetcode, updated, scaling, SectorEmissions.sector AS sector, sectorcode, ('EMIS_' || scaling || '_0x' || int2hex(SectorEmissions.emission) ) AS exemission, SectorEmissions.xcomment AS xcomment, ('0x' || int2hex(SectorEmissions.emission) ) AS rawemission, datesent, datemscw, FootNotes.footnote AS footnote FROM DataSetsView, SectorEmissions, DataTypes, SectorDefinitions, FootNotes WHERE DataSetsView.setid = SectorEmissions.setid AND DataSetsView.datatype = DataTypes.datatype AND DataTypes.sectordefinition = SectorDefinitions.sectordefinition AND SectorEmissions.sector = SectorDefinitions.sector AND ( SectorEmissions.footnoteid = FootNotes.footnoteid OR FootNotes.footnoteid = '0') ; DROP VIEW SectorGridEmissionsView; CREATE VIEW SectorGridEmissionsView AS SELECT SectorGridEmissions.setid AS setid, SectorGridEmissions.sector AS sector, sectorcode, x, y, scaling, ('EMIS_' || scaling || '_0x' || int2hex(SectorGridEmissions.emission) ) AS exemission, ('0x' || int2hex(SectorGridEmissions.emission) ) AS rawemission, FootNotes.footnote AS footnote FROM DataSetsView, SectorGridEmissions, DataTypes, SectorDefinitions, FootNotes WHERE DataSetsView.setid = SectorGridEmissions.setid AND DataSetsView.datatype = DataTypes.datatype AND DataTypes.sectordefinition = SectorDefinitions.sectordefinition AND SectorGridEmissions.sector = SectorDefinitions.sector AND ( SectorGridEmissions.footnoteid = FootNotes.footnoteid OR FootNotes.footnoteid = '0' ) ; DROP VIEW LowHiSectorGridEmissionsView; CREATE VIEW LowHiSectorGridEmissionsView AS SELECT LowHiSectorGridEmissions.setid AS setid, LowHiSectorGridEmissions.sector AS sector, sectorcode, x, y, scaling, ('EMIS_' || scaling || '_0x' || int2hex(LowHiSectorGridEmissions.lowemission) ) AS exlowemission, ('EMIS_' || scaling || '_0x' || int2hex(LowHiSectorGridEmissions.highemission) ) AS exhighemission, ('0x' || int2hex(LowHiSectorGridEmissions.lowemission) ) AS rawlowemission, ('0x' || int2hex(LowHiSectorGridEmissions.highemission) ) AS rawhighemission, FootNotes.footnote AS footnote FROM DataSetsView, LowHiSectorGridEmissions, DataTypes, SectorDefinitions, FootNotes WHERE DataSetsView.setid = LowHiSectorGridEmissions.setid AND DataSetsView.datatype = DataTypes.datatype AND DataTypes.sectordefinition = SectorDefinitions.sectordefinition AND LowHiSectorGridEmissions.sector = SectorDefinitions.sector AND ( LowHiSectorGridEmissions.footnoteid = FootNotes.footnoteid OR FootNotes.footnoteid = '0') ; DROP VIEW EmissionYearView; CREATE VIEW EmissionYearView AS SELECT DISTINCT year FROM DataSets ; DROP VIEW CollSpecsView; CREATE VIEW CollSpecsView AS SELECT collection, CollectionSpecs.setid AS setid, datasetcode FROM CollectionSpecs, DataSetsView WHERE CollectionSpecs.setid = DataSetsView.setid ; DROP VIEW LaPoSoView; CREATE VIEW LaPoSoView AS SELECT LargePointSources.lpsid AS lpsid, LargePointSources.areaid AS areaid, Areas.name AS name, acronym, lps, (acronym || lps) AS lpscode, LargePointSources.name AS lpsname, latitude, longitude, height, exitsurface, speed, temperature FROM LargePointSources, Areas WHERE LargePointSources.areaid = Areas.areaid ; DROP VIEW EmissionsUpdView; CREATE VIEW EmissionsUpdView AS SELECT setid, xcomment, ('0x' || int2hex(Emissions.emission) ) AS rawemission FROM Emissions ;
В списке pgsql-general по дате отправления: