pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedDoes auto-analyze work on dirty writes? (was: Re: [HACKERS] Slow count(*) again...)
2+ messages / 2 participants
[nested] [flat]
* Does auto-analyze work on dirty writes? (was: Re: [HACKERS] Slow count(*) again...)
@ 2011-02-04 03:31 Mark Mielke <mark@mark.mielke.cc>
2011-02-04 15:41 ` Re: Does auto-analyze work on dirty writes? (was: Re: [HACKERS] Slow count(*) again...) Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 2+ messages in thread
From: Mark Mielke @ 2011-02-04 03:31 UTC (permalink / raw)
To: Conor Walsh <ctw@adverb.ly>; +Cc: jd@commandprompt.com, Robert Haas <robertmhaas@gmail.com>; pgsql-performance
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>
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: Does auto-analyze work on dirty writes? (was: Re: [HACKERS] Slow count(*) again...)
2011-02-04 03:31 Does auto-analyze work on dirty writes? (was: Re: [HACKERS] Slow count(*) again...) Mark Mielke <mark@mark.mielke.cc>
@ 2011-02-04 15:41 ` Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 2+ messages in thread
From: Tom Lane @ 2011-02-04 15:41 UTC (permalink / raw)
To: Mark Mielke <mark@mark.mielke.cc>; +Cc: Conor Walsh <ctw@adverb.ly>; jd@commandprompt.com, Robert Haas <robertmhaas@gmail.com>; pgsql-performance
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
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2011-02-04 15:41 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2011-02-04 03:31 Does auto-analyze work on dirty writes? (was: Re: [HACKERS] Slow count(*) again...) Mark Mielke <mark@mark.mielke.cc>
2011-02-04 15:41 ` Tom Lane <tgl@sss.pgh.pa.us>
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