[postgis-users] radically different performance on different machines

Rémi Cura remi.cura at gmail.com
Fri Nov 8 07:49:07 PST 2013


Hey Richard,
I may be completely wrong, but maybe you could use ST_DWithin instead of
Buffer+intersect.
It is usually a good boost in perf, but maybe you absolutely need this
shrinking.

In the same way I'm not sure the way this query is written is the best (you
really need a subquerry here?).

I'm guessing you don't have a simple index on your "name" from
"special_district", it will accelerate it.
(CREATE INDEX ON special_district (name);)

Last thing is you may consider to protect your column name "owner" and
"name" as these are reserved sql word, using doublequote

Hope it helps =)

Cheers,
Rémi-C


2013/11/8 Sandro Santilli <strk at keybit.net>

> On Fri, Nov 08, 2013 at 07:58:45AM -0700, Richard Greenwood wrote:
> > I have a query that runs in <1 second on one machine and 47 seconds on
> > another. The postgres and postgis versions are bascially the same. The
> > database and query are the same. But "explain" is different on the two.
> The
> > query is below and the graphic output of pgAdmin explain is attached.
> > Obviously, my question is - why the difference?
>
> Different statistics gathered ? Try running ANALYZE on both systems.
> Different cost configuration ? Compare postgresql.conf.
>
> --strk;
> _______________________________________________
> postgis-users mailing list
> postgis-users at lists.osgeo.org
> http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20131108/173def86/attachment.html>


More information about the postgis-users mailing list