[postgis-users] COPY too slow when geometry column is indexed

David Bitner osgis.lists at gmail.com
Tue Dec 6 20:17:24 PST 2005


If you here anything about partitioning large datasets by time and
then accessing by spatial attributes be sure to report back to this
list.

On 12/6/05, Rick Schumeyer <rschumeyer at ieee.org> wrote:
> As the originator of this thread, thanks to whoever suggested partitioning
> the table to begin with.  At the moment I have about 350 sub tables, each
> with about 230k records, for a total of over 80 million records.  The COPY
> time remained constant, and spatial index searches work as expected.  Keep
> in mind that the tables are partitioned by time, not by location, so a
> spatial query looks at all sub tables.
>
> Having to create a new table for each data file is not that big of a deal.
> I'm a little concerned about the effects of having so many tables.  I'll
> have to ask the postgres-performance list about that.
>
> > Hi, David,
> >
> > David Bitner wrote:
> > > I have not implemented it yet, but I am having the same issues with a
> > > dataset that I add several thousand records to a day.  I have been
> > > looking into partitioning my data by month or quarter akin to:
> > > http://www.postgresql.org/docs/8.1/interactive/ddl-partitioning.html
> > > A bit more difficult to keep adding the partitions, but It shouldn't
> > > be too difficult to just add a script on a cron that adds a new table
> > > each quarter.
> >
> > If you insert the data via INSERT and not via COPY, you can use a view
> > with ON INSERT DO INSTEAD rules that automagically creates needed
> > tables, and redirects the data into the appropriate partitions.
> >
> > Maybe it is possible using a BEFORE INSERT trigger on a table, too.
> > (possibly on the base table if you inherit the partitioned tables, so
> > you get a really transparent partitioning).
> >
> > Markus
>
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
>



More information about the postgis-users mailing list