Re: test / live environment, major performance difference

On 2007-06-11 Christo Du Preez wrote:
> I really hope someone can shed some light on my problem. I'm not sure
> if this is a posgres or potgis issue.
>
> Anyway, we have 2 development laptops and one live server, somehow I
> managed to get the same query to perform very well om my laptop, but
> on both the server and the other laptop it's really performing bad.

You write that you have 3 systems, but provided only two EXPLAIN ANALYZE
results. I will assume that the latter is from your laptop while the
former is from one of the badly performing systems.

> All three environments are running the same versions of everything,
> the two laptops are identical and the server is a monster compared to
> the laptops.

Please provide information what exactly those "same versions of
everything" are. What's the PostgreSQL configuration on each system? Do
all three systems have the same configuration? Information on the
hardware wouldn't hurt either.

[...]
> Sort (cost=181399.77..182144.30 rows=297812 width=370) (actual
> time=1384.976..1385.072 rows=180 loops=1)
[...]
> Sort (cost=37993.10..37994.11 rows=403 width=266) (actual
> time=32.053..32.451 rows=180 loops=1)

The row estimate of the former plan is way off (297812 estimated <-> 180
actual). Did you analyze the table recently? Maybe you need to increase
the statistics target.

Regards
Ansgar Wiechers
--
"The Mac OS X kernel should never panic because, when it does, it
seriously inconveniences the user."
--http://developer.apple.com/technotes/tn2004/tn2118.html