Re: Huge Performance Difference on Similar Query in Pg7.2

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
;