Message-ID: <50DC86EB.2070709@richardneill.org>
Date: Thu, 27 Dec 2012 17:35:39 +0000
From: Richard Neill <rn214@richardneill.org>
User-Agent: Mozilla/5.0 (X11; Linux x86_64;
	rv:16.0) Gecko/20121010 Thunderbird/16.0.1
MIME-Version: 1.0
CC: "pgsql-performance@postgresql.org" <pgsql-performance@postgresql.org>
Subject: Re: explain analyze reports that my queries are fast but
	they run very slowly
References: 
 <CAPmjWd2XgV9d3zy3zc8L9PJRdoVM720g2zSQWsDKZsJkOqKMjw@mail.gmail.com>
	<1C24B9EF-045C-473F-8C28-485F4CD697AF@teksol.info>
In-Reply-To: <1C24B9EF-045C-473F-8C28-485F4CD697AF@teksol.info>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: quoted-printable
Precedence: bulk
Sender: pgsql-performance-owner@postgresql.org

On 27/12/12 17:21, Fran=E7ois Beausoleil wrote:
>
> Le 2012-12-27 =E0 12:10, Nikolas Everett a =E9crit :
>
>> We just upgraded from 8.3 to 9.1 and we're seeing some performance probl=
ems.  When we EXPLAIN ANALYZE our queries the explain result claim that the=
 queries are reasonably fast but the wall clock time is way way longer.  Do=
es anyone know why this might happen?
>>

Is it possible you missed an optimisation setting in the migration=20
process? I made that mistake, and much later found performance was=20
somewhat degraded (but surprisingly not as much as I'd expected) by my=20
having failed to set effective_cache_size.

Also, if you just did a dump/restore, it might help to run Analyse once
(it seems that Analyse is normally run automatically via vacuum, but the=20
first time you insert the data, it may not happen).

A side-effect of Analyse it that it will pull all the tables into the OS=20
memory cache (or try to) - which may give significantly faster results=20
(try running the same query twice in succession: it's often 5x faster=20
the 2nd time).

HTH,

Richard


--=20
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance