Re: strange performance regression between 7.4 and 8.1 - Mailing list pgsql-performance

From Florian Weimer
Subject Re: strange performance regression between 7.4 and 8.1
Date
Msg-id 82slco6n9t.fsf@mid.bfk.de
Whole thread Raw
In response to strange performance regression between 7.4 and 8.1  ("Alex Deucher" <alexdeucher@gmail.com>)
Responses Re: strange performance regression between 7.4 and 8.1  (Carlos Moreno <moreno_pg@mochima.com>)
Re: strange performance regression between 7.4 and 8.1  ("Alex Deucher" <alexdeucher@gmail.com>)
Re: strange performance regression between 7.4 and 8.1  (Alvaro Herrera <alvherre@commandprompt.com>)
List pgsql-performance
* Alex Deucher:

> I have noticed a strange performance regression and I'm at a loss as
> to what's happening.  We have a fairly large database (~16 GB).

Sorry for asking, but is this a typo?  Do you mean 16 *TB* instead of
16 *GB*?

If it's really 16 GB, you should check if it's cheaper to buy more RAM
than to fiddle with the existing infrastructure.

> however the table structure is almost identical (UTF8 on the new one
> vs. C on the old).

Locale settings make a huge difference for sorting and LIKE queries.
We usually use the C locale and SQL_ASCII encoding, mostly for
performance reasons.  (Proper UTF-8 can be enforced through
constraints if necessary.)

--
Florian Weimer                <fweimer@bfk.de>
BFK edv-consulting GmbH       http://www.bfk.de/
Kriegsstraße 100              tel: +49-721-96201-1
D-76133 Karlsruhe             fax: +49-721-96201-99

pgsql-performance by date:

Previous
From: Adam L Beberg
Date:
Subject: Re: Array indexes, GIN?
Next
From: David Leangen
Date:
Subject: Re: Improving query performance