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

 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 
 (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?

5 Answers 5


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.


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:


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


A cursor is iterable, so you can either:

for result in cur:
    print result

Or get them all:

  • 2
    I could be wrong, but I don't think the first (and perhaps the second) solutions will work. The first one modifies the server memory, and this is a client issue, not related to the available memory within the DMBS, and the second would likely suffer the same issue unless just buffering it to the screen is the issue. Your third solution is better than mine IMO, since it gives fine-grained control in a flexible environment with the cursor, which is a bit tricker in raw SQL. +1
    – scw
    Aug 12, 2010 at 7:36
  • @scw you're probably right about the first (and perhaps second) solution. Problem is, sometimes these specific tricks seem to work with PostgreSQL, they might improve general performance, and they take little time and effort to try. Learned a lot from your answer.
    – Adam Matan
    Aug 12, 2010 at 8:37

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.


Indexing is very important in Postgres and PostGIS


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

  • Yep, I indexed and vacuumed the tables, and confirmed my indexes are being used via EXPLAIN, etc. However, my issue isn't with speed (at this point), its with an out of memory error.
    – fmark
    Aug 12, 2010 at 2:56

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.