On 12/30/15 1:31 PM, Joe Conway wrote:
On 12/30/2015 11:09 AM, Cory Tucker wrote: With this scenario you can expect an autoanalyze every 5 million rows and autovacuum every 10 million. In my experience (and based on your description, yours as well) this is not often enough. Not only that, when it does run it runs longer than you would like, causing an I/O hit while it does. You probably should tune this table specifically, e.g.
Another option is to explicitly analyze then SELECT from the table after you're done inserting into it. The advantage is it doesn't tie up an autovac worker and you can ensure that the newly added tuples get properly hinted.
You can run the ANALYZE immediately after your insert finishes. The reason to do that is to get up-to-date statistics for other queries to use. That can be particularly important if the new rows have values significantly outside what was in the table before. That's common with things like sequence IDs and timestamp data.
The SELECT is a bit trickier; you want to ensure that there is no transaction still running in the database that's older than the transaction that added all the new data. You can check that by comparing the xmin field of one of your new rows with txid_snapshot_xmin(txid_current_snapshot()). Note that because of wraparound you can't do a simple comparison; txid 3 is actually greater than txid 2^32.
The whole point of this SELECT is to get the new tuples hinted while the pages are still hot in cache. If you don't do that, the next query that reads the tuple will have to set the hints, which also dirties the page. VACUUM does that too, but there's really no point in having vacuum run through the entire table just to set hints on less than 1% of it.
-- Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX Experts in Analytics, Data Architecture and PostgreSQL Data in Trouble? Get it in Treble! http://BlueTreble.com -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
