> I forgot to mention one point: the point and the polygon tables reside in > different databases and dblink is used for cross db query. Today, we > imported the small table into the db where the polygon table is and the > problem is gone. So it sounds like dblink might be the reason for the > crashes. Any bug associated with dblink?
dblink is what I think of as a marginal package -- not in the core of Postgres, with many quirks, and (I believe) behaves strangely if you link across different versions and libraries. For anything complex, I would avoid it. > On the other hand, the database is not well tuned so that "select count(*) > from PolyTbl" takes about 100 seconds, which is too slow for a table with > only 3 million records. Any input on the performance issue of postgres? THere are LOTS of discussions on why "select count(*) from Table" is slow in Postgres (it forces a seq scan because Postgres doesn't have the table metadata to just output the count). The way to fix is to keep a table with the count which is updated with a trigger (I think this is the best -- google for the discussions). Just because select count(*) is slow doesn't mean a more specific query will be slow, however. You will probably get better answers on the Postgresql general mailing list to these questions, and lots of those folks are familiar with Postgis too. _______________________________________________ postgis-users mailing list [email protected] http://postgis.refractions.net/mailman/listinfo/postgis-users
