[OSM-dev] Postgres 8.4/PostGIS 1.4 performance

Martijn van Oosterhout kleptog at gmail.com
Sun Sep 20 20:56:10 BST 2009

On Sun, Sep 20, 2009 at 7:43 PM, Frederik Ramm <frederik at remote.org> wrote:
> Hi,
> Lars Francke wrote:
>> On a related note: There was a change between PostgreSQL 8.3 and 8.4
>> which increased the default_statistics_target from 10 to 100 and its
>> maximum from 1.000 to 10.000 which makes ANALYZE runs take longer but
>> at least for me it helps a lot.
> Have you, too, noticed the 10% performance degradation (of 8.4 vs. 8.3)
> on full planet imports that prompted me to start this thread?

Just a few ideas:

1. If you list the ctid for the first few rows, perhaps you can spot
if there are maybe more tuples fitting in a page?

2. Is the data for the arrays being TOASTed (pushed out to a seperate
table). It's possible the compression threshold has changed meaning
they get compressed in 8.4, which saves space but costs compression
time.  To test this you need to find an array with lots of entries (a
few hundred at least). You can set the storage options manually if you
want to test.

3. In 8.4 the VACUUM should be a lot faster (maybe not first time, but
subsequent runs) due to the visibility map. Could you test this?

4. As pointed out, the statistics target changed, might be worth
measuring the effect of that explicitly (either manually, of using the
log_statement_duration option).

5. The figures you paste for table size, are they including the TOAST
tables? (as in pg_relation_size vs pg_total_relation_size).

Hope this helps,
Martijn van Oosterhout <kleptog at gmail.com> http://svana.org/kleptog/

More information about the dev mailing list