Queries returning very big datasets in PostGIS

Question

I have a PostGIS query that will return several million rows:

SELECT 
 t1.id AS id1,
 t2.id AS id2,
 ABS(t1.mean_h - t2.mean_h) AS h_diff, 
 ST_Distance(t1.the_geom, t2.the_geom) AS dist  
FROM tas_ponds as t1, tas_ponds as t2 
WHERE
 (t1.gid > t2.gid) AND
 ST_DWithin(t1.the_geom, t2.the_geom, 17000)

When run in psql, I get an out of memory for query result error.

Googling suggests that this an error within psql rather than postgres/PostGIS. Would amending the query into the form SELECT ... INTO x FROM ... fix the problem? Are there any other recommended approaches for dealing with very large datasets?

Answer 1

Some poking around does confirm this is a Postgres client problem, independent of spatial or server considerations: the client has a limited amount of memory to buffer the results before displaying them on the screen, which you're exceeding.

The recommended approach to handle this is to use a DECLARE / FETCH approach to access the data in smaller blocks than the total result set. You could also create a view with components of the query (e.g. distance) to cut down on the memory needed for the query operation itself.

Answer 2

scw got me by two minutes, so I won't repeat his answer. Here are some other possible solutions:

• Edit the Memory section of postgresql.conf. Try to see if you have extremely low memory settings that might prevent the query from running.

• Try to write the query into a file and run from the command line, using:

  psql -f filename db_name > output_file

• If you intend to use the results in an external application, try to Run the query using a cursor from outside psql. For example, a Python script that would run your query:

script:

try:
    conn = psycopg2.connect("dbname='' user='' host='' password=''") # Fill in
    cur = conn.cursor()
except:
        print 'Unable to connect to the database'
else:
        print 'Connected to database.'

query="""YOUR
         QUERY
         HERE"""
cur.execute(query)

A cursor is iterable, so you can either:

for result in cur:
    print result

Or get them all:

 results=cur.fetchall()

Answer 3

For the record, in my case storing the returned dataset in another table using the SELECT ... INTO ... syntax worked.

It not only solved the out-of-memory issue, but was also substantially faster than original query.

Answer 4

Indexing is very important in Postgres and PostGIS

http://postgis.refractions.net/docs/ch04.html#id2794434

Recommend GiST (Generalized Search Trees) indexes for very large datasets.... (is also "null safe") example CREATE INDEX [indexname] ON [tablename] USING GIST ( [geometryfield] );

After building an index, it is important to force PostgreSQL to collect table statistics, which are used to optimize query plans

VACUUM ANALYZE [table_name] [column_name]; SELECT UPDATE_GEOMETRY_STATS([table_name], [column_name]);

also a good reference is: Taking Advantage of Indexes http://postgis.refractions.net/docs/ch04.html#id2794685

Answer 5

This is a very old question, but an ageless issue. PostgreSQL/PostGIS has real issues with very large datasets, and when this question was asked, we didn't have access to a lot of database engines specifically designed for big datasets, often significantly outperforming PostgreSQL when querying large tables.

Google BigQuery launched in 2011 Amazon Redshift launched in 2012 Snowflake launched in 2014

... and so on. Not to mention, spatial functions weren't available from the start on these platforms. While PostGIS certainly offers more spatial functionality, these platforms do offer a range of ST functions at this point in time. BigQuery, for instance, has ST_DWithin, which the author of the question was struggling with in PostgreSQL.

So, if anyone is getting frustrated with PostgreSQL bottlenecking on large tables, I suggest exploring one of the big data analytics offerings out there.