[postgis-users] radically different performance on different machines

[Richard Greenwood]

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 <remi.cura at gmail.com> 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 <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
>>
>
>
> _______________________________________________
> postgis-users mailing list
> postgis-users at lists.osgeo.org
> http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
>



-- 
Richard Greenwood
richard.greenwood at gmail.com
www.greenwoodmap.com
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20131109/f57644d9/attachment.html>