From: Gaetano Mendola <mendola@bigfoot.com>
To: Matthew T. O'Connor <matthew@zeut.net>
Cc: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Dennis Bjorklund <db@zigo.dhs.org>
Cc: pgsql-performance@postgresql.org <pgsql-performance@postgresql.org>
Subject: Re: [HACKERS] Wrong index choosen?
Date: Sat, 24 Jul 2004 02:07:27 +0200
Message-ID: <4101A83F.2070801@bigfoot.com> (raw)
In-Reply-To: <410154F9.3080500@zeut.net>
References: <Pine.LNX.4.44.0407231354520.9559-100000@zigo.dhs.org>
<20162.1090593927@sss.pgh.pa.us>
<41014DE1.8000007@bigfoot.com>
<410154F9.3080500@zeut.net>
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
Matthew T. O'Connor wrote:
| Gaetano Mendola wrote:
|
|> Tom Lane wrote:
|> | Given the nature of the data (login times), I'd imagine that the
|> problem
|> | is simply that he hasn't analyzed recently enough. A bump in stats
|> | target may not be needed, but he's going to have to re-analyze that
|> | column often if he wants this sort of query to be estimated accurately,
|> | because the fraction of entries later than a given time T is *always*
|> | going to be changing.
|>
|> Well know that I think about it, I felt my shoulders covered by
|> pg_autovacuum but looking at the log I see that table never analyzed!
|> Aaargh.
|>
|> I already applied the patch for the autovacuum but evidently I have to
|> make it more aggressive, I'm sorry that I can not made him more
|> aggressive
|> only for this table.
|
|
| Yeah, the version of autovacuum in 7.4 contrib doesn't allow table
| specific settings. The patch I have sumbitted for 7.5 does, so
| hopefully this will be better in the future.
|
| You can however set the VACUUM and ANALYZE thresholds independently. So
| perhpaps it will help you if you set your ANALYZE setting to be very
| aggressive and your VACUUM settings to something more standard.
Well I think pg_autovacuum as is in 7.4 can not help me for this particular
table.
The table have 4.8 milions rows and I have for that table almost 10252 new
entries for day.
I'm using pg_autovacuum with -a 200 -A 0.8 this means a threashold for
that table equal to: 3849008 and if I understod well the way pg_autovacuum
works this means have an analyze each 375 days, and I need an analyze for
each day, at least.
So I think is better for me put an analyze for that table in the cron.
Am I wrong ?
Regards
Gaetano Mendola
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.4 (MingW32)
Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org
iD8DBQFBAag87UpzwH2SGd4RAqb1AJ416ioVEY5T/dqnAQsaaqqoWcU3ZACghzsO
4xMowWp/MM8+i7DhoRO4018=
=/gNn
-----END PGP SIGNATURE-----
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: mendola@bigfoot.com, matthew@zeut.net, tgl@sss.pgh.pa.us, db@zigo.dhs.org
Subject: Re: [HACKERS] Wrong index choosen?
In-Reply-To: <4101A83F.2070801@bigfoot.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