[postgis-users] performance problem with loading data

[Sergey Karin]

Hi all.

I use PG 8.1.8 and PostGIS 1.1.1
vka1=# select version();
                                                     version
-----------------------------------------------------------------------------------------------------------------
 PostgreSQL 8.1.8 on x86_64-pc-linux-gnu, compiled by GCC cc (GCC)
4.1.220061115 (prerelease) (Debian
4.1.1-21)

vka1=# select postgis_full_version();
                               postgis_full_version
----------------------------------------------------------------------------------
 POSTGIS="1.1.1" GEOS="2.2.3-CAPI-1.1.1" PROJ="Rel. 4.4.9, 29 Oct 2004"
USE_STATS

I develop application for loading geodata to database. In separate
transaction the application  inserts the data in separate table that created
dynamically when transaction started. All tables has equal structure.

Geodata has simple and similar structure (field geometry): POLYGON((x1 y1,
x2 y2, x3 y3, x4 y4, x1 y1))
For loading geodata I use  INSERT into <TABLE_NAME> .... via LIBPQ.
In big loop I call PQexec(conn, query).

I found *VERY* strange problem: speed of the loading process is slow down
(first 10000 objects are loaded in 69792 ms and last 10000 objects in 123737
ms). And futhermore, if I do NOT close current session but start new
transaction, the first 10000 objects will be loaded in 192279 ms and last
10000 objects in 251742 ms. And so on!! :-(

But if I drop the trigger speed of loading process is NOT slow down.

Who can explain me what I do incorrect?

Thanks in advance

Sergey Karin

=======================================
================code===================
=======================================
create table <TABLE_NAME> (
    GID                               SERIAL               not null,
    GEOM_ORDER             INT4                    not null default 0,
    ZOOMABLE                  BOOL                  not null default false,
    GEOM                          GEOMETRY
    constraint <TABLE_NAME_GID> primary key (GID),
    );

create index <TABLE_NAME_GEOM_INDEX> on <TABLE_NAME> using gist ( geom
gist_geometry_ops );
create trigger trgOInsert
                    before insert or update
                    on <TABLE_NAME>
                    for each row
                    execute procedure oInsertCheck('GEOMETRYCOLLECTION', 0);

create or replace function oInsertCheck() returns trigger as'
declare
    g_isvalid boolean;
    iSrid int4;
    geomType varchar;
begin

    if(new.geom isnull) then
        new.geom := geomFromText(\'GEOMETRYCOLLECTION(EMPTY)\');
    end if;
    if(new.geom_order isnull) then
        new.geom_order := 0;
    end if;

    select isvalid(new.geom) into g_isvalid;
    if(g_isvalid isnull) then
        return NULL;
    end if;

    geomType := TG_ARGV[TG_NARGS-2];
    iSrid    := TG_ARGV[TG_NARGS-1];

    if(upper(geomType) = \'GEOMETRYCOLLECTION\') then
        new.geom := force_collection(new.geom);
    end if;

    new.geom := setSrid(new.geom, iSrid);

    return new;
end
'language 'plpgsql' security definer;

=================end of code==========================
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20070609/2489e167/attachment.html>