From: Richard Neill <rn214@richardneill.org>
Cc: pgsql-performance@postgresql.org <pgsql-performance@postgresql.org>
Subject: Re: explain analyze reports that my queries are fast but they run very slowly
Date: Thu, 27 Dec 2012 17:35:39 +0000
Message-ID: <50DC86EB.2070709@richardneill.org> (raw)
In-Reply-To: <1C24B9EF-045C-473F-8C28-485F4CD697AF@teksol.info>
References: <CAPmjWd2XgV9d3zy3zc8L9PJRdoVM720g2zSQWsDKZsJkOqKMjw@mail.gmail.com>
<1C24B9EF-045C-473F-8C28-485F4CD697AF@teksol.info>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-performance>
On 27/12/12 17:21, François Beausoleil wrote:
>
> Le 2012-12-27 à 12:10, Nikolas Everett a écrit :
>
>> We just upgraded from 8.3 to 9.1 and we're seeing some performance problems. 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. Does anyone know why this might happen?
>>
Is it possible you missed an optimisation setting in the migration
process? I made that mistake, and much later found performance was
somewhat degraded (but surprisingly not as much as I'd expected) by my
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
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
memory cache (or try to) - which may give significantly faster results
(try running the same query twice in succession: it's often 5x faster
the 2nd time).
HTH,
Richard
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: rn214@richardneill.org
Subject: Re: explain analyze reports that my queries are fast but they run very slowly
In-Reply-To: <50DC86EB.2070709@richardneill.org>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox