[postgis-users] Insert performance issues

[Obe, Regina DND\MIS]

That seems like a fair number.  I would try getting rid of them except for
gist and slowly add them back based on query performance.  It will really
slow down your insert performance having so many indexed.  Use Explain for
that.  

I'm not quite sure how Postgresql handles queries - but from experience with
other databases -  a system can use only one or 2 at any given time.

You should do an analysis of the most common queries you will be using -
particularly the WHERE CLAUSE.

If you find you need all of them, I would consider dropping them before
insert and adding them back after the insert is completed.

-----Original Message-----
From: Nicolas RIBOT [mailto:nri at scot.cnes.fr]
Sent: Monday, October 20, 2003 11:44 AM
To: PostGIS Users Discussion
Subject: RE: [postgis-users] Insert performance issues


> You said you have all the fields indexed involved in the 
> query.  Exactly how
> many fields do you have indexed?
> 

7. The table Structure is the following:

create table clcv1 (
    gid         serial primary key      , 
    AREA        float8                  ,
    PERIMETER   float8                  , 
    CLCV1_      int4                    , 
    CLCV1_ID    int4                    , 
    ID_CLC_INI  int4                    , 
    CLC_V0      int4                    , 
    CLC_V1      int4                    ,
    MARQUEUR    int4                    , 
    BLOC        varchar(4)              , 
    EDIT_ST     varchar(5)              , 
    MODIF_ST    varchar (6)             ,
    ID_BLOC     int4                    ,
    ISMODIF     int4          default 0
);
CREATE INDEX clcv1_spi   ON clcv1    USING GIST (the_geom
GIST_GEOMETRY_OPS);
CREATE index clcv1_clcv1__idx on clcv1 (clcv1_);
CREATE index clcv1_clc_v1_idx on clcv1 (clc_v1);
CREATE index clcv1_marqueur_idx on clcv1 (marqueur);
CREATE index clcv1_bloc_idx on clcv1 (bloc);
CREATE index clcv1_edit_st_idx on clcv1 (edit_st);
CREATE index clcv1_modif_st_idx on clcv1 (modif_st);

_______________________________________________
postgis-users mailing list
postgis-users at postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users