Re: Huge Performance Difference on Similar Query in Pg7.2 - Mailing list 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
;