8

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?

7

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.

5

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()
  • 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 '10 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 '10 at 8:37
2

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.

1

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

  • 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 '10 at 2:56

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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