Thread

1. Routine analyze of single column prevents standard autoanalyze from running at all

   Tomasz Ostrowski <tometzky+pg@ato.waw.pl> — 2016-06-06T14:25:29Z

   Hi.
   
   I'm routinely bulk inserting data to a PostgreSQL table and then 
   analyzing a single column of the table, because it contains data which 
   significantly changes histogram of this column values - for example 
   something like adding rows with "todo=true" column, when all rows before 
   bulk insert have "todo=false".
   
   This column has rather small "statistics" value, so analyze of it is 
   fairly fast, which is important as I'm doing it often and also in 
   parallel (and analyze blocks - only one can run at the time). The full 
   analyze of this large table would take a lot of time (20 times more 
   actually), and I can't perform it after each bulk insert.
   
   But I've noticed that a standard automatic analyze, which should work in 
   background, never runs. I've noticed that this fast analyze of one 
   column resets pg_stat_user_tables(n_mod_since_analyze) counter.
   
   I suppose that the decision to analyze the whole table is based on these 
   values from pg_stat_user_tables and autovacuum_analyze_threshold and 
   autovacuum_analyze_scale_factor settings. And in this case this highly 
   updated table never reaches these values.
   
   I suppose this is a bug - an analyze, which does not analyze all 
   columns, should not reset pg_stat_user_tables(n_mod_since_analyze). What 
   do you think?
   
   -- 
   Tomasz "Tometzky" Ostrowski
   
   
   

2. Re: [BUGS] Routine analyze of single column prevents standard autoanalyze from running at all

   Tom Lane <tgl@sss.pgh.pa.us> — 2016-06-06T18:38:00Z

   [ redirecting to -hackers ]
   
   Tomasz Ostrowski <tometzky+pg@ato.waw.pl> writes:
   > I'm routinely bulk inserting data to a PostgreSQL table and then 
   > analyzing a single column of the table, because it contains data which 
   > significantly changes histogram of this column values - for example 
   > something like adding rows with "todo=true" column, when all rows before 
   > bulk insert have "todo=false".
   
   > But I've noticed that a standard automatic analyze, which should work in 
   > background, never runs. I've noticed that this fast analyze of one 
   > column resets pg_stat_user_tables(n_mod_since_analyze) counter.
   
   > I suppose this is a bug - an analyze, which does not analyze all 
   > columns, should not reset pg_stat_user_tables(n_mod_since_analyze). What 
   > do you think?
   
   I'm inclined to think that this is a reasonable complaint.  A usage
   pattern like that probably hasn't come up before; but now that it has,
   it's clear it shouldn't block auto-analyze from happening.
   
   A cheap-and-dirty solution would be to not send a PgStat_MsgAnalyze
   message at all, but I think that's probably undesirable: typically it
   would be a good thing to accept the new n_live_tuples and n_dead_tuples
   estimates.  What we could do instead is add a bool flag to
   PgStat_MsgAnalyze saying whether or not to reset changes_since_analyze.
   
   It would be safe enough to back-patch such a change, because the stats
   collector messages are private to the backend (in fact, really private
   to pgstat.c).
   
   One question here is whether there is any connection between
   changes_since_analyze and the tuple-count estimates that would make it
   improper to update the latter and not the former.  I can't really see one;
   in fact, changes_since_analyze is pretty squishy anyway because changes
   made by an ANALYZE's own transaction are already accounted for in the new
   pg_statistic entries but will be added to changes_since_analyze at commit
   despite that.  So I'd go ahead and update all the fields other than
   changes_since_analyze in this case.
   
   Another interesting consideration is that if the table's columns have
   different stats targets, a selective-column ANALYZE might possibly sample
   fewer rows than an all-columns ANALYZE would.  This might mean that our
   new tuple-count estimates are less accurate than autoanalyze would get.
   So you could possibly argue that we shouldn't update the tuple-count
   estimates after all.  But I don't particularly believe that, because it
   disregards the fact that the new estimates are, well, new.  Even if they
   have more statistical risk than autoanalyze would have, they could well
   be better just by virtue of having seen whatever bulk updates might have
   happened since the last autoanalyze.  So my inclination is to disregard
   this fine point.  (Though it's interesting to ask whether Tomasz's use
   case includes a lower-than-default stats target for his very volatile
   column ...)
   
   Also, I'd be a bit inclined to disable the counter reset whenever a column
   list is specified, disregarding the corner case where a list is given but
   it includes all the table's analyzable columns.  It doesn't really seem
   worth the effort to account for that case specially (especially after
   you consider that index expressions should count as analyzable columns).
   
   Thoughts?
   
   			regards, tom lane
   
   
   

3. Re: [BUGS] Routine analyze of single column prevents standard autoanalyze from running at all

   Tom Lane <tgl@sss.pgh.pa.us> — 2016-06-06T19:59:37Z

   I wrote:
   > Tomasz Ostrowski <tometzky+pg@ato.waw.pl> writes:
   >> I suppose this is a bug - an analyze, which does not analyze all 
   >> columns, should not reset pg_stat_user_tables(n_mod_since_analyze). What 
   >> do you think?
   
   > I'm inclined to think that this is a reasonable complaint.  A usage
   > pattern like that probably hasn't come up before; but now that it has,
   > it's clear it shouldn't block auto-analyze from happening.
   
   Attached is a draft patch for this, which I propose to apply and
   back-patch.  Any objections?
   
   			regards, tom lane
   
   

4. Re: [BUGS] Routine analyze of single column prevents standard autoanalyze from running at all

   Josh Berkus <josh@agliodbs.com> — 2016-06-06T20:23:30Z

   On 06/06/2016 01:38 PM, Tom Lane wrote:
   
   > Also, I'd be a bit inclined to disable the counter reset whenever a column
   > list is specified, disregarding the corner case where a list is given but
   > it includes all the table's analyzable columns.  It doesn't really seem
   > worth the effort to account for that case specially (especially after
   > you consider that index expressions should count as analyzable columns).
   > 
   > Thoughts?
   
   +1.  Better to err on the side of duplicate analyzes than none at all.
   
   Also, I'm not surprised this took so long to discover; I doubt most
   users are aware that you *can* analyze individual columns.
   
   -- 
   --
   Josh Berkus
   Red Hat OSAS
   (any opinions are my own)
   
   
   

5. Re: [BUGS] Routine analyze of single column prevents standard autoanalyze from running at all

   Jim Nasby <jim.nasby@bluetreble.com> — 2016-06-08T00:25:13Z

   On 6/6/16 3:23 PM, Josh berkus wrote:
   > On 06/06/2016 01:38 PM, Tom Lane wrote:
   >
   >> Also, I'd be a bit inclined to disable the counter reset whenever a column
   >> list is specified, disregarding the corner case where a list is given but
   >> it includes all the table's analyzable columns.  It doesn't really seem
   >> worth the effort to account for that case specially (especially after
   >> you consider that index expressions should count as analyzable columns).
   >>
   >> Thoughts?
   >
   > +1.  Better to err on the side of duplicate analyzes than none at all.
   >
   > Also, I'm not surprised this took so long to discover; I doubt most
   > users are aware that you *can* analyze individual columns.
   
   Is there any significant advantage to not analyzing all columns? Only 
   case I can think of is if you have a fair number of columns that have 
   been toasted; otherwise I'd think IO would completely swamp any other 
   considerations.
   -- 
   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
   855-TREBLE2 (855-873-2532)   mobile: 512-569-9461
   
   
   

6. Re: [BUGS] Routine analyze of single column prevents standard autoanalyze from running at all

   Tom Lane <tgl@sss.pgh.pa.us> — 2016-06-08T03:04:05Z

   Jim Nasby <Jim.Nasby@BlueTreble.com> writes:
   > Is there any significant advantage to not analyzing all columns? Only 
   > case I can think of is if you have a fair number of columns that have 
   > been toasted; otherwise I'd think IO would completely swamp any other 
   > considerations.
   
   Yeah, my guess is that the OP's example where analyzing just one column
   was significantly cheaper boiled down to some of the other columns being
   mostly toasted data.  Otherwise it's hard to see how there's much more
   expense in analyzing them all.
   
   			regards, tom lane
   
   
   

7. Re: [BUGS] Routine analyze of single column prevents standard autoanalyze from running at all

   Tomasz Ostrowski <tometzky+pg@ato.waw.pl> — 2016-06-08T06:19:58Z

   W dniu 2016-06-08 o 05:04, Tom Lane pisze:
   > Jim Nasby <Jim.Nasby@BlueTreble.com> writes:
   >> Is there any significant advantage to not analyzing all columns? Only
   >> case I can think of is if you have a fair number of columns that have
   >> been toasted; otherwise I'd think IO would completely swamp any other
   >> considerations.
   >
   > Yeah, my guess is that the OP's example where analyzing just one column
   > was significantly cheaper boiled down to some of the other columns being
   > mostly toasted data.  Otherwise it's hard to see how there's much more
   > expense in analyzing them all.
   
   Actually no - this volatile column has smaller "statistics" than most of 
   the table, so analyzing it is much faster when it's data is not in RAM. 
   Here is a small exaggerated example showing a difference:
   
   $ psql
   tometzky=> create table test (id serial, data text);
   tometzky=> insert into test(data)
     select 'Lorem ipsum dolor sit amet, consectetur adipiscing elit. '
     ||generate_series(0,10000000)::text;
   tometzky=> alter table test alter column id set statistics 10;
   tometzky=> alter table test alter column data set statistics 1000;
   tometzky=> \q
   
   # Drop OS page cache and restart postgres
   # so the table data won't be in RAM anymore:
   $ sudo sh -c 'echo 1 >/proc/sys/vm/drop_caches'
   $ sudo systemctl restart postgresql;
   
   # Test single column analyze:
   $ psql
   tometzky=> \timing
   Timing is on.
   tometzky=> analyze verbose test(id);
   INFO:  analyzing "public.test"
   INFO:  "test": scanned 3000 of 123457 pages, containing 243000 live rows 
   and 0 dead rows; 3000 rows in sample, 2712238 estimated total rows
   ANALYZE
   Time: 422,521 ms
   tometzky=> \q
   
   # Drop OS page cache and restart postgres again
   $ sudo sh -c 'echo 1 >/proc/sys/vm/drop_caches'
   $ sudo systemctl restart postgresql;
   
   $ psql
   tometzky=> \timing
   Timing is on.
   tometzky=> analyze verbose test;
   INFO:  analyzing "public.test"
   INFO:  "test": scanned 123457 of 123457 pages, containing 10000001 live 
   rows and 0 dead rows; 300000 rows in sample, 10000001 estimated total rows
   ANALYZE
   Time: 9447,519 ms
   
   -- 
   Tomasz "Tometzky" Ostrowski
   
   
   

8. Re: [BUGS] Routine analyze of single column prevents standard autoanalyze from running at all

   Tom Lane <tgl@sss.pgh.pa.us> — 2016-06-08T14:04:51Z

   Tomasz Ostrowski <tometzky+pg@ato.waw.pl> writes:
   > W dniu 2016-06-08 o 05:04, Tom Lane pisze:
   >> Yeah, my guess is that the OP's example where analyzing just one column
   >> was significantly cheaper boiled down to some of the other columns being
   >> mostly toasted data.  Otherwise it's hard to see how there's much more
   >> expense in analyzing them all.
   
   > Actually no - this volatile column has smaller "statistics" than most of 
   > the table, so analyzing it is much faster when it's data is not in RAM. 
   
   Oh, so you *will* be vulnerable to the side-effect we wondered about
   earlier, wherein the manual ANALYZE scans fewer blocks and may therefore
   derive a less-accurate tuple count estimate than auto-analyze gets.
   It'll be interesting to see how the fix plays out for you --- please pay
   attention to whether the pg_stat_all_tables.n_live_tup/n_dead_tup numbers
   jump around in unexpected ways.
   
   			regards, tom lane