Re: INDEX Performance Issue

On Fri, Apr 5, 2013 at 8:51 AM, Mark Davidson <mark(at)4each(dot)co(dot)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?

Cheers,

Jeff