On Fri, Apr 5, 2013 at 8:51 AM, Mark Davidson <mark@4each.co.uk> wrote:
Hi All,
Hoping someone can help me out with some performance issues I'm having with the INDEX on my database. I've got a database that has a data table containing ~55,000,000 rows which have point data and an area table containing ~3,500 rows which have polygon data. A user queries the data by selecting what areas they want to view and using some other filters such as datatime and what datasets they want to query. This all works fine and previously the intersect of the data rows to the areas was being done on the fly with PostGIS ST_Intersects. However as the data table grow we decided it would make sense to offload the data processing and not calculate the intersect for a row on the fly each time, but to pre-calculate it and store the result in the join table. Resultantly this produce a table data_area which contains ~250,000,000 rows.
I think your old method is likely the better option, especially if the intersect can be offloaded to the client or app server (I don't know enough about ST_Intersects to know how likely that is).
What is the difference in performance between the old method and the new method?