Sandro, Rémi, Thanks for the suggestions. I've done quite a bit more work but still have not found a solution. The query runs great on every computer except my production server. My production server is an Amazon instance so I created a new Amazon machine with same OS/Postgres/Postgis version. Backup, restored my database into the new instance and the query runs great. I compared postgresql.conf between the two machines and they are the same. So I have something different on the two computers that is causing the query planner to produce significantly different approaches to the same query on the same database. But I am at a loss as to what.
Thanks for reading, any ideas appreciated. Rich On Fri, Nov 8, 2013 at 8:49 AM, Rémi Cura <[email protected]> wrote: > 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 <[email protected]> > >> 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 >> [email protected] >> http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users >> > > > _______________________________________________ > postgis-users mailing list > [email protected] > http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users > -- Richard Greenwood [email protected] www.greenwoodmap.com
_______________________________________________ postgis-users mailing list [email protected] http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
