pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: John Arbash Meinel <john@arbash-meinel.com>
To: werner fraga <wfraga@yahoo.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: VACUUM ANALYZE slows down query
Date: Thu, 17 Feb 2005 16:38:37 -0600
Message-ID: <42151CED.7050309@arbash-meinel.com> (raw)
In-Reply-To: <20050217222428.84111.qmail@web41408.mail.yahoo.com>
References: <20050217222428.84111.qmail@web41408.mail.yahoo.com>

werner fraga wrote:

>Certain queries on my database get slower after
>running a VACUUM ANALYZE. Why would this happen, and
>how can I fix it?
>
>I am running PostgreSQL 7.4.2 (I also seen this
>problem on v. 7.3 and 8.0)
>
>Here is a sample query that exhibits this behaviour
>(here the query goes from 1 second before VACUUM
>ANALYZE to 2 seconds after; there are other queries
>that go from 20 seconds before to 800 seconds after):
>
>
>
First, try to attach your explain analyze as a textfile attachment,
rather than inline to prevent wrapping and make it easier to read.

Second, the problem is that it *is* getting a more accurate estimate of
the number of rows that are going to be returned, compare:

Plan 1:

>-----------------------------------------------------------------------------------------------------------------------------------------------------------------
> Nested Loop Left Join  (cost=3974.74..48055.42
>rows=79 width=8) (actual time=359.751..1136.165
>rows=1518 loops=1)
>
>
The planner was expecting 79 rows, but was actually getting 1518.

Plan 2:

>-----------------------------------------------------------------------------------------------------------------------------------------------------------------------
> Merge Left Join  (cost=18310.59..29162.44 rows=1533
>width=8) (actual time=1886.942..2183.774 rows=1518
>loops=1)
>
>
It is predicting 1533 rows, and found 1518, a pretty good guess.

So the big issue is why does the planner think that a nested loop is
going to be more expensive than a merge join. That I don't really know.
I'm guessing some parameters like random_page_cost could be tweaked, but
I don't really know the criteria postgres uses for merge joins vs nested
loop joins.

>Thanks for any assistance.
>
>Walt
>
>
Hopefully someone can help a little better. In the mean time, you might
want to resend with an attachment. I know I had trouble reading your
explain analyze.

John
=:->

Attachments:

  signature.asc (application/pgp-signature, 252B) download

view thread (4+ messages)  latest in thread

Message-ID: <42151CED.7050309@arbash-meinel.com>
Permalink:  ../42151CED.7050309@arbash-meinel.com/
Also on:    postgresql.org/message-id/42151CED.7050309@arbash-meinel.com

 · 

reply

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: john@arbash-meinel.com, wfraga@yahoo.com
  Subject: Re: VACUUM ANALYZE slows down query
  In-Reply-To: <42151CED.7050309@arbash-meinel.com>

* 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