[postgis-users] PostGIS INDEX Performance Intermittent

[Paul Ramsey]

On Tue, Nov 20, 2012 at 3:26 AM, Mark Davidson <mark at 4each.co.uk> wrote:
> Hi Paul
>
> Thanks very much for your responses
>
> A few questions.
>
> With the difference in the first two plans if your saying it might be the
> autovacuumer messing it up would it be worth changing some of the default
> settings to make it more or less regular etc or is it just delaying the
> inevitable do you think? Also is it worth adding additional statistics on
> the columns in question to try and get the planner to behave more as
> expected.

It's not a bug, it's a feature. The vacuumer isn't messing things up,
it's just improving the information available. The fact that the
system is using that information poorly is the bug. And the bug is
basically that our join selectivity calculation things the join is far
more selective than it actually is, so it prioritizes it ahead of the
other clauses, which turns out to be a terrible choice.

> Ok that’s interesting is kinda what I thought the case was. Unfortunately
> don't have the budget to fund anything at the moment, but this is a very big
> and on going project so might be potential in the future.
> In the meanwhile is there anything you could suggest that I could do to find
> out more about why the planner is making those decisions. Or is that way
> above a user level and more a case of a developer looking at the underlying
> code?

It is way above the user level and probably one of the most complex
parts of the PostGIS code base. Developer++.

> These other tickets you mention that may have a similar problem could you
> point me in the direction of them I'd be very interested to have a read.
>
> Will reply to your response on gis.stackexchange on there shortly.

Read again, I've updated my suggestion a bit.
http://gis.stackexchange.com/questions/41199/postgis-intermittent-index-performance

HACK: If all else fails, since you've found that you can get great
performance without the spatial index: drop the spatial index. If you
need it for other queries, add a second geometry column, and have one
indexed column and one unindexed, and call them as needed.

P.

> Many thanks again for your input it is very much appreciated,
>
> Mark
>
> _______________________________________________
> postgis-users mailing list
> postgis-users at lists.osgeo.org
> http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
>