Re: integrate pg_upgrade analyze_new_cluster.sh into vacuumdb

From: Oskari Saarenmaa <os(at)ohmu(dot)fi>
To: Peter Eisentraut <peter_e(at)gmx(dot)net>, pgsql-hackers <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: integrate pg_upgrade analyze_new_cluster.sh into vacuumdb
Date: 2014-01-21 17:06:48
Message-ID: 52DEA928.9000203@ohmu.fi
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

09.01.2014 05:15, Peter Eisentraut kirjoitti:
> pg_upgrade creates a script analyze_new_cluster.{sh|bat} that runs
> vacuumdb --analyze-only in three stages with different statistics target
> settings to get a fresh cluster analyzed faster. I think this behavior
> is also useful for clusters or databases freshly created by pg_restore
> or any other loading mechanism, so it's suboptimal to have this
> constrained to pg_upgrade.

I think the three stage analyze is a wrong solution to the "slow
analyze" problem. In my experience most of the analyze time goes to
reading random blocks from the disk but we usually use only a small
portion of that data (1 row per block.)

If we were able to better utilize the data we read we could get good
statistics with a lot less IO than we currently need. This was
discussed in length at
http://www.postgresql.org/message-id/CAM-w4HOjRbNPMW=SHjHw_Qfapcuu5Ege1tMdR0ZQU+kqX8Qeug@mail.gmail.com
but it hasn't turned into patches so far.

/ Oskari

In response to

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Fujii Masao 2014-01-21 17:31:45 Re: [9.3 bug] disk space in pg_xlog increases during archive recovery
Previous Message Robert Haas 2014-01-21 16:41:13 Re: Closing commitfest 2013-11