CODEWITHSUNDEEP
pythonpostgresqlpython-2.7postgispsycopg2
Arijit Basu
Arijit Basu

Reputation: 61

POSTGIS inserts become slow after some time

I have a location table with following structure:

CREATE TABLE location  
        (  
          id BIGINT,  
          location GEOMETRY,  
          CONSTRAINT location_pkey PRIMARY KEY (id, location),  
          CONSTRAINT enforce_dims_geom CHECK (st_ndims(location) = 2),  
          CONSTRAINT enforce_geotype_geom CHECK (geometrytype(location) = 'POINT'::TEXT OR location IS NULL),  
          CONSTRAINT enforce_srid_geom CHECK (st_srid(location) = 4326)  
        )  
   WITH (  
          OIDS=FALSE  
         );  

    CREATE INDEX location_geom_gist  ON location  
     USING 
     GIST (location);

I run the following query to insert data:

def insert_location_data(msisdn, lat, lon):  
    if not (lat and lon):      
            return  
    query = "INSERT INTO location (id, location) VALUES ('%s', ST_GeomFromText('POINT(%s %s)', 4326))"%(str(id), str(lat), str(lon))  
    try:  
        cur = get_cursor()  
        cur.execute(query)  
        conn.commit()  
    except:  
        tb = traceback.format_exc()  
        Logger.get_logger().error("Error while inserting location in sql: %s", str(tb))  
        return False  
    return True

I run this block of code 10,000,000 times in a loop but somewhere after 1 million inserts the inserting speed drops drastically. The speed returns to normal when I restart the script but it again drops around a million documents and the same trend continues. I cannot figure out why? Any help.

Upvotes: 3

Views: 348

Answers (1)

Mike T
Mike T

Reputation: 43642

Here's a few tips.

This is how to insert one point:

cur.execute(
    "INSERT INTO location (id, location) "
    "VALUES (%s, ST_SetSRID(ST_MakePoint(%s, %s), 4326))",
    (msisdn, lon, lat))

And see executemany if you want to insert more records at a time, where you would prepare a list of parameters to insert (i.e. [(msisdn, lon, lat), (msisdn, lon, lat), ..., (msisdn, lon, lat)]).

Upvotes: 2

Related Questions