On Wed, 2008-06-04 at 17:02 -0500, Jason Long wrote:
> I have a query that takes 2 sec if I run it from a freshly restored
> dump. If I run a full vacuum on the database it then takes 30 seconds.

If you run it a second time after the vacuum full?

Joshua D. Drake



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

Yes. 
This is what I do.
1. Vacuum full
2. Reindex force
This is done ever night

My users complained about a report being slow.

I grabbed a dump and restored it to my development machine.

The query worked just fine, but not on the production server.

I did a vacuum full and then reran the query.  Now it performs just as slow as on the production machine.  That is the only change I made.

I am in the process of trying to figure out the EXPLAIN ANALYZE for both queries, but I have never had to read this output and I am unfamiliar with how to find the problem.  I will post both here and maybe someone can help me out.



Joshua D. Drake wrote:

On Wed, 2008-06-04 at 17:02 -0500, Jason Long wrote:



I have a query that takes 2 sec if I run it from a freshly restored dump. If I run a full vacuum on the database it then takes 30 seconds.



If you run it a second time after the vacuum full? Joshua D. Drake

Just post the explain analyze output here on the list. There's lots
of folks here who can read it.

On Wed, Jun 4, 2008 at 5:49 PM, Jason Long
<mailing.list@supernovasoftware.com> wrote:
> Yes.
> This is what I do.
> 1. Vacuum full
> 2. Reindex force
> This is done ever night
>
> My users complained about a report being slow.
>
> I grabbed a dump and restored it to my development machine.
>
> The query worked just fine, but not on the production server.
>
> I did a vacuum full and then reran the query. Now it performs just as slow
> as on the production machine. That is the only change I made.
>
> I am in the process of trying to figure out the EXPLAIN ANALYZE for both
> queries, but I have never had to read this output and I am unfamiliar with
> how to find the problem. I will post both here and maybe someone can help
> me out.
>
>
>
> Joshua D. Drake wrote:
>
> On Wed, 2008-06-04 at 17:02 -0500, Jason Long wrote:
>
>
> I have a query that takes 2 sec if I run it from a freshly restored
> dump. If I run a full vacuum on the database it then takes 30 seconds.
>
>
> If you run it a second time after the vacuum full?
>
> Joshua D. Drake
>
>
>
>

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