pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  

help / color / mirror / Atom feed
From: Tom Lane <tgl@sss.pgh.pa.us>
To: Mark Mielke <mark@mark.mielke.cc>
Cc: Conor Walsh <ctw@adverb.ly>
Cc: jd@commandprompt.com, Robert Haas <robertmhaas@gmail.com>
Cc: pgsql-performance@postgresql.org <pgsql-performance@postgresql.org>
Subject: Re: Does auto-analyze work on dirty writes? (was: Re: [HACKERS] Slow count(*) again...)
Date: Fri, 04 Feb 2011 10:41:04 -0500
Message-ID: <22836.1296834064@sss.pgh.pa.us> (raw)
In-Reply-To: <4D4B7317.3070803@mark.mielke.cc>
References: <201102012247.p11Ml6u02682@momjian.us>
	<4D48914B.4050403@dunslane.net>
	<4D489AD1.1010105@vmsinfo.com>
	<AANLkTiknZi97dDwm7sHqy0EL7tpNEP9sY5uPV6AN+q=u@mail.gmail.com>
	<4D499E55.4020107@vmsinfo.com>
	<AANLkTimo2J-BagObs_V1bGaHOO9qL+kTF6s6-wZ=M3KW@mail.gmail.com>
	<AANLkTin7A_Q=Yc9Wf_5=POp2mVqmaiwqUDsFMLG68H45@mail.gmail.com>
	<4D4A7B69.2020704@gmail.com>
	<alpine.DEB.2.00.1102030208440.8162@asgard.lang.hm>
	<AANLkTi=Thet0VZ-X6b7tek+iSLVkE27JjiKrB5D8U0Jd@mail.gmail.com>
	<alpine.DEB.2.00.1102031243190.10088@asgard.lang.hm>
	<AANLkTikmChsPODbUD4V-gQ8k2yYvvjJY6b9K_29+k7nu@mail.gmail.com>
	<alpine.DEB.2.00.1102031637480.30983@asgard.lang.hm>
	<AANLkTi=Kx6rRFs3Kz2m32nLKwLGv0Ee87UF8ngKhppgM@mail.gmail.com>
	<alpine.DEB.2.00.1102031732470.30983@asgard.lang.hm>
	<AANLkTikN14hsVVVLJ=BQdqEcpmzkLLqSbEpqx-th4Ohx@mail.gmail.com>
	<AANLkTi=XUprgXqV84iY1sukC_6Pos+DfVPFveaK1CSHf@mail.gmail.com>
	<1296786810.18411.102.camel@jd-desktop>
	<AANLkTikR77m7ttGb5Y1y7HWa0Os95TQXj_+wgqa9Xkfm@mail.gmail.co! m >
	<4D4B7317.3070803@mark.mielke.cc>

Mark Mielke <mark@mark.mielke.cc> writes:
> My understanding is:

> 1) Background daemon wakes up and checks whether a number of changes 
> have happened to the database, irrelevant of transaction boundaries.

> 2) Background daemon analyzes a percentage of rows in the database for 
> statistical data, irrelevant of row visibility.

> 3) Analyze is important for both visible rows and invisible rows, as 
> plan execution is impacted by invisible rows. As long as they are part 
> of the table, they may impact the queries performed against the table.

> 4) It doesn't matter if the invisible rows are invisible because they 
> are not yet committed, or because they are not yet vacuumed.

> Would somebody in the know please confirm the above understanding for my 
> own piece of mind?

No.

1. Autovacuum fires when the stats collector's insert/update/delete
counts have reached appropriate thresholds.  Those counts are
accumulated from messages sent by backends at transaction commit or
rollback, so they take no account of what's been done by transactions
still in progress.

2. Only live rows are included in the stats computed by ANALYZE.
(IIRC it uses SnapshotNow to decide whether rows are live.)

Although the stats collector does track an estimate of the number of
dead rows for the benefit of autovacuum, this isn't used by planning.
Table bloat is accounted for only in terms of growth of the physical
size of the table in blocks.

			regards, tom lane



view thread (2+ messages)

Message-ID: <22836.1296834064@sss.pgh.pa.us>
Permalink:  ../22836.1296834064@sss.pgh.pa.us/
Also on:    postgresql.org/message-id/22836.1296834064@sss.pgh.pa.us

 · 

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: tgl@sss.pgh.pa.us, mark@mark.mielke.cc, ctw@adverb.ly, robertmhaas@gmail.com
  Subject: Re: Does auto-analyze work on dirty writes? (was: Re: [HACKERS] Slow count(*) again...)
  In-Reply-To: <22836.1296834064@sss.pgh.pa.us>

* 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