On Tue, 11 Jan 2011, Tom Hughes wrote:
Obviously that can be rewritten by replacing each use of a temporary
table with a subquery but as the temporary tables are used multiple
times that leads to multiple evaluations of those queries.
Hence Matt was saying that the real alternative would be to replace the
temporary tables with storing the lists of objects in memory on the
cgimap client. That has both a memory overhead and a cost overhead in
sending long lists of objects back to postgres in subsequent queries.
Another option might be to have a second postgresql instance and select the
lists of objects from the replica into this second database (that exists
just to hold the temp tables). You could use pl/proxy, DBI/link or the
SQL/MED stuff coming in 9.1 to have the second database talk directly to the
first one.
It might also be worth thinking about some of non built-in replication
options (ie Slony or Londiste). They do introduce some overhead to
writes on the master but if we can offload enough reads you can see
significant overal gains. Administration pain will go up but that comes
with the teritory of clustering.
Everything I've read says that the non-builtin replication systems are a
huge amount of hassle.
Yes, expect a huge amount of hassle.
Tom
--
Tom Hughes ([email protected])
http://compton.nu/
_______________________________________________
dev mailing list
[email protected]
http://lists.openstreetmap.org/listinfo/dev
