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

help / color / mirror / Atom feed
From: Mark Mielke <mark@mark.mielke.cc>
To: Conor Walsh <ctw@adverb.ly>
Cc: jd@commandprompt.com, Robert Haas <robertmhaas@gmail.com>
Cc: pgsql-performance@postgresql.org <pgsql-performance@postgresql.org>
Subject: Does auto-analyze work on dirty writes? (was: Re: [HACKERS] Slow count(*) again...)
Date: Thu, 03 Feb 2011 22:31:35 -0500
Message-ID: <4D4B7317.3070803@mark.mielke.cc> (raw)
In-Reply-To: <AANLkTikR77m7ttGb5Y1y7HWa0Os95TQXj_+wgqa9Xkfm@mail.gmail.com>
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.com >

On 02/03/2011 09:45 PM, Conor Walsh wrote:
> My understanding is that auto-analyze will fire only after my
> transaction is completed, because it is a seperate daemon.  If I do
> like so:
>
> BEGIN;
> COPY ...;
> -- Dangerously un-analyzed
> SELECT complicated-stuff ...;
> END;
>
> Auto-analyze does not benefit me, or might not because it won't fire
> often enough.  I agree that analyze is very fast, and it often seems
> to me like the cost/benefit ratio suggests making auto-analyze even
> more aggressive.

The count discussion is boring. Nothing new there. But auto-analyze on 
dirty writes does interest me. :-)

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?

Thanks,
mark

-- 
Mark Mielke<mark@mielke.cc>




view thread (2+ messages)  latest in thread

Message-ID: <4D4B7317.3070803@mark.mielke.cc>
Permalink:  ../4D4B7317.3070803@mark.mielke.cc/
Also on:    postgresql.org/message-id/4D4B7317.3070803@mark.mielke.cc

 · 

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: 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: <4D4B7317.3070803@mark.mielke.cc>

* 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