Re: autovacuum big table taking hours and sometimes seconds

[Laurenz Albe]

Mariel Cherkassky wrote:
> Lets focus for example on one of the outputs :
> postgresql-Fri.log:2019-02-08 05:05:53 EST  24776  LOG:  automatic vacuum of 
> table "myDB.pg_toast.pg_toast_1958391": index scans: 8
> postgresql-Fri.log-   pages: 2253 removed, 13737828 remain
> postgresql-Fri.log-   tuples: 21759258 removed, 27324090 remain
> postgresql-Fri.log-   buffer usage: 15031267 hits, 21081633 misses, 19274530 
> dirtied
> postgresql-Fri.log-   avg read rate: 2.700 MiB/s, avg write rate: 2.469 MiB/s
> 
> The cost_limit is set to 200 (default) and the cost_delay is set to 20ms. 
> The calculation I did : (1*15031267+10*21081633+20*19274530)/200*20/1000 = 
> 61133.8197 seconds ~ 17H
> So autovacuum was laying down for 17h ? I think that I should increase the 
> cost_limit to max specifically on the toasted table. What do you think ? Am I 
> wrong here ?

Increasing cost_limit or reducing cost_delay improves the situation.

cost_delay = 0 makes autovacuum as fast as possible.

Yours,
Laurenz Albe

Re: ERROR: unrecognized parameter "autovacuum_analyze_scale_factor"

[Alvaro Herrera]

On 2019-Feb-14, Mariel Cherkassky wrote:

> I meant the anaylze, if anaylze will run very often on the original table,
> arent there disadvantages for it ?

It'll waste time and resources pointlessly.  Don't do it -- it won't do
any good.


-- 
Álvaro Herrerahttps://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

Re: constraint exclusion with ineq condition (Re: server hardware tuning.)

[suganthi Sekar]

Hi,


yes i accept , but when i will do for existing tables, i am facing issue.



I have created 100 Function , all the function having  five table join(now all 
partition by date) , now its not possible to change where condition in all 100 
Function.


so that i am trying any other possibilities are there.



Regards,

Suganthi Sekar


From: Michael Lewis 
Sent: 15 February 2019 00:20:00
To: suganthi Sekar
Cc: Justin Pryzby; pgsql-performance@lists.postgresql.org
Subject: Re: constraint exclusion with ineq condition (Re: server hardware 
tuning.)

Yeah, the planner doesn't know that call_created_date can be limited on both 
tables unless you tell it specify it in the where condition as Laurenz said on 
another thread.


Michael Lewis

On Thu, Feb 14, 2019 at 7:35 AM suganthi Sekar 
mailto:sugan...@uniphore.com>> wrote:
Both table Portion by  same column call_created_date

From: Michael Lewis mailto:mle...@entrata.com>>
Sent: 14 February 2019 19:35:48
To: suganthi Sekar
Cc: Justin Pryzby; 
pgsql-performance@lists.postgresql.org
Subject: Re: constraint exclusion with ineq condition (Re: server hardware 
tuning.)

What are these two tables partitioned by?

On Thu, Feb 14, 2019, 5:03 AM suganthi Sekar 
mailto:sugan...@uniphore.com>>
 wrote:
Hi,

Thanks, i know if explicitly we give in where condition it is working.

i thought with below parameter in Postgresq11 this issue is fixed ?

 enable_partitionwise_join  to 'on';

 what is the use of enable_partitionwise_join  to 'on';

Thanks for your response.

Regards
Suganthi Sekar

From: Justin Pryzby 
mailto:pry...@telsasoft.com>>>
Sent: 14 February 2019 16:10:01
To: suganthi Sekar
Cc: 
pgsql-performance@lists.postgresql.org>
Subject: Re: constraint exclusion with ineq condition (Re: server hardware 
tuning.)

On Thu, Feb 14, 2019 at 10:38:36AM +, suganthi Sekar wrote:
> u mean the below parameter need to set on . its already on only.
>   alter system set  constraint_exclusion  to 'on';

No, I said:
> You can work around it by specifying the same condition on 
> b.call_created_date:
> >  AND b.call_created_date >='2017-11-01' AND b.call_created_date<'2017-11-30'

Re: Performance regressions found using sqlfuzz

[Jung, Jinho]

Hi Jeff,


Thanks for the feedback! The git bisect idea was particularly helpful.


We use query complexity constraints in sqlfuzz to ensure that the constructed 
queries are realistic (e.g., limit the query size, automatically minimize the 
query, avoid esoteric expressions and functions, restrict number of joins, 
etc.).


Our goal is to augment the test suite with queries that will assist developers  
with more comprehensively  evaluating the impact of new optimization 
heuristics, query processing  strategies etc. We are working on improving the 
utility of the tool and your feedback on these reports will be super helpful. 
Thanks.


For each regression, we share:


1) the associated query,

2) the commit that activated it,

3) our high-level analysis, and

4) query execution plans in old and new versions of PostgreSQL.


All these regressions are observed on the latest version (dev HEAD).


### QUERY 2:

  select distinct
ref_0.i_im_id as c0,
ref_1.ol_dist_info as c1
  from
  public.item as ref_0 right join
  public.order_line as ref_1
  on (ref_0.i_id = 5)

- Commit: 84f9a35 (Improve estimate of distinct values in estimate_num_groups())

- Our analysis: We believe that this regression is related to the new logic for 
estimating the number of distinct values in the optimizer. This is affecting 
even queries with point lookups (ref_0.i_id = 5) in the TPC-C benchmark.

- Query Execution Plans

  [OLD version]
  HashAggregate  (cost=11972.38..12266.20 rows=29382 width=29) (actual 
time=233.543..324.973 rows=300144 loops=1)
Group Key: ref_0.i_im_id, ref_1.ol_dist_info
->  Nested Loop Left Join  (cost=0.29..10471.64 rows=300148 width=29) 
(actual time=0.012..114.955 rows=300148 loops=1)
  ->  Seq Scan on order_line ref_1  (cost=0.00..6711.48 rows=300148 
width=25) (actual time=0.004..25.061 rows=300148 loops=1)
  ->  Materialize  (cost=0.29..8.32 rows=1 width=4) (actual 
time=0.000..0.000 rows=1 loops=300148)
->  Index Scan using item_pkey on item ref_0  (cost=0.29..8.31 
rows=1 width=4) (actual time=0.005..0.005 rows=1 loops=1)
  Index Cond: (i_id = 10)
  Planning time: 0.267 ms
  Execution time: 338.027 ms


  [NEW version]
  Unique  (cost=44960.08..47211.19 rows=300148 width=29) (actual 
time=646.545..885.502 rows=300144 loops=1)
->  Sort  (cost=44960.08..45710.45 rows=300148 width=29) (actual 
time=646.544..838.933 rows=300148 loops=1)
  Sort Key: ref_0.i_im_id, ref_1.ol_dist_info
  Sort Method: external merge  Disk: 11480kB
  ->  Nested Loop Left Join  (cost=0.29..10471.64 rows=300148 width=29) 
(actual time=0.016..111.889 rows=300148 loops=1)
->  Seq Scan on order_line ref_1  (cost=0.00..6711.48 
rows=300148 width=25) (actual time=0.004..24.612 rows=300148 loops=1)
->  Materialize  (cost=0.29..8.32 rows=1 width=4) (actual 
time=0.000..0.000 rows=1 loops=300148)
  ->  Index Scan using item_pkey on item ref_0  
(cost=0.29..8.31 rows=1 width=4) (actual time=0.008..0.008 rows=1 loops=1)
Index Cond: (i_id = 10)
  Planning Time: 0.341 ms
  Execution Time: 896.662 ms


### QUERY 3:

  select
cast(ref_1.ol_i_id as int4) as c0
  from
public.stock as ref_0
  left join public.order_line as ref_1
  on (ref_1.ol_number is not null)
  where ref_1.ol_number is null

- Commit: 77cd477 (Enable parallel query by default.)

- Our analysis: We believe that this regression is due to parallel queries 
being enabled by default. Surprisingly, we found that even on a larger TPC-C 
database (scale factor of 50, roughly 4GB), parallel scan is still slower than 
the non-parallel one in the old version, when the query is not returning any 
tuples.

- Query Execution Plans

  [OLD version]
  Nested Loop Anti Join  (cost=0.00..18006.81 rows=1 width=4) (actual 
time=28.689..28.689 rows=0 loops=1)
->  Seq Scan on stock ref_0  (cost=0.00..5340.00 rows=10 width=0) 
(actual time=0.028..15.722 rows=10 loops=1)
->  Materialize  (cost=0.00..9385.22 rows=300148 width=4) (actual 
time=0.000..0.000 rows=1 loops=10)
  ->  Seq Scan on order_line ref_1  (cost=0.00..6711.48 rows=300148 
width=4) (actual time=0.004..0.004 rows=1 loops=1)
Filter: (ol_number IS NOT NULL)
  Planning time: 0.198 ms
  Execution time: 28.745 ms

  [NEW version]
  Gather  (cost=1000.00..15164.93 rows=1 width=4) (actual time=91.022..92.634 
rows=0 loops=1)
Workers Planned: 2
Workers Launched: 2
->  Nested Loop Anti Join  (cost=0.00..14164.83 rows=1 width=4) (actual 
time=88.889..88.889 rows=0 loops=3)
  ->  Parallel Seq Scan on stock ref_0  (cost=0.00..4756.67 rows=41667 
width=0) (actual time=0.025..7.331 rows=3 loops=3)
  ->  Seq Scan on order_line ref_1  (cost=0.00..6711.48 rows=300148 
width=4) (actual time=0.002..0.002 rows=1 loops=10)
Filter: (ol_number IS NOT NU

Re: Performance regressions found using sqlfuzz

[Andrew Gierth]

> "Jung" == Jung, Jinho  writes:

 Jung>   select distinct
 Jung> ref_0.i_im_id as c0,
 Jung> ref_1.ol_dist_info as c1
 Jung>   from
 Jung>   public.item as ref_0 right join
 Jung>   public.order_line as ref_1
 Jung>   on (ref_0.i_id = 5)

 Jung> - Commit: 84f9a35 (Improve estimate of distinct values in 
estimate_num_groups())

 Jung> - Our analysis: We believe that this regression is related to the
 Jung> new logic for estimating the number of distinct values in the
 Jung> optimizer. This is affecting even queries with point lookups
 Jung> (ref_0.i_id = 5) in the TPC-C benchmark.

So what's happening here is that the old plan was mis-estimating the
result, believed incorrectly that it would fit into work_mem, and
generated a hashaggregate plan accordingly; it ran fast because
hashaggregate doesn't spill to disk but silently overflows work_mem.

The new plan correctly estimates the result size, and therefore is
forbidden from generating the hashaggregate plan at the default work_mem
setting; it generates a sort plan, and the sort of course spills to disk
since work_mem is exceeded.

Had the value of work_mem been set to something appropriate for the
workload, then the query plan would not have changed.

So the problem (from an automated testing perspective) is that an actual
_improvement_ in the code is being reported as a regression.

 Jung> ### QUERY 3:

 Jung>   select
 Jung> cast(ref_1.ol_i_id as int4) as c0
 Jung>   from
 Jung> public.stock as ref_0
 Jung>   left join public.order_line as ref_1
 Jung>   on (ref_1.ol_number is not null)
 Jung>   where ref_1.ol_number is null

 Jung> - Commit: 77cd477 (Enable parallel query by default.)

 Jung> - Our analysis: We believe that this regression is due to
 Jung> parallel queries being enabled by default. Surprisingly, we found
 Jung> that even on a larger TPC-C database (scale factor of 50, roughly
 Jung> 4GB), parallel scan is still slower than the non-parallel one in
 Jung> the old version, when the query is not returning any tuples.

The problem here is not actually with parallel scans as such, but rather
the omission of a Materialize node in the parallel plan, and what looks
like some rather serious mis-costing of the nestloop antijoin.

 Jung> ### QUERY 4:

 Jung>   select
 Jung> ref_0.s_dist_06 as c0
 Jung>   from
 Jung> public.stock as ref_0
 Jung>   where (ref_0.s_w_id < cast(least(0, 1) as int8))

 Jung> - Commit: 5edc63b (Account for the effect of lossy pages when costing 
bitmap scans)

 Jung> - Our analysis: We believe that this regression has to do with
 Jung> two factors: 1) conditional expression (e.g., LEAST or NULLIF)
 Jung> are not reduced to constants unlike string functions (e.g.,
 Jung> CHAR_LENGTH) 2) change in the cost estimation function for bitmap
 Jung> scan. Execution time grows by 3 orders of magnitude. We note that
 Jung> this regression is only observed on large databases (e.g., scale
 Jung> factor of 50).

Again, this is showing up because of a large database and a small
work_mem. The bitmap scan on stock only becomes lossy if the number of
rows matched in the index is very large relative to work_mem; the lack
of plan-time evaluation of LEAST means that the planner doesn't have any
good way to estimate the selectivity, so it's taking a default estimate.

 Jung> ### QUERY 1:

 Jung>   select
 Jung> ref_0.o_d_id as c0
 Jung>   from
 Jung> public.oorder as ref_0
 Jung>   where EXISTS (
 Jung> select
 Jung>   1
 Jung> from
 Jung>   (select distinct
 Jung>   ref_0.o_entry_d as c0,
 Jung>   ref_1.c_credit as c1
 Jung>from
 Jung>   public.customer as ref_1
 Jung>where (false)
 Jung>   ) as subq_1
 Jung>   );

 Jung> - Commit: bf6c614 (Do execGrouping.c via expression eval machinery, take 
two)

 Jung> - Our analysis: We are not sure about the root cause of this
 Jung> regression. This might have to do with grouping logic.

What this query is basically exercising is how fast one can do
ExecReScan on a DISTINCT query, without also considering the performance
effects of actually doing the grouping (the constant-false qual here
means that the grouping comparison is never actually performed). An
optimization tradeoff that speeds up comparisons within a scan at the
cost of a fixed overhead for the scan will therefore make this query
slower, but it still seems a good tradeoff to make (of course it would
be even better to make the overhead per-query rather than per-scan, and
there were other issues with this commit that should have been caught at
the time).

-- 
Andrew (irc:RhodiumToad)

Re: Performance regressions found using sqlfuzz

[Andres Freund]

Hi,

On 2019-02-14 17:27:40 +, Jung, Jinho wrote:
> ### QUERY 2:
> 
>   select distinct
> ref_0.i_im_id as c0,
> ref_1.ol_dist_info as c1
>   from
>   public.item as ref_0 right join
>   public.order_line as ref_1
>   on (ref_0.i_id = 5)
> 
> - Commit: 84f9a35 (Improve estimate of distinct values in 
> estimate_num_groups())
> 
> - Our analysis: We believe that this regression is related to the new logic 
> for estimating the number of distinct values in the optimizer. This is 
> affecting even queries with point lookups (ref_0.i_id = 5) in the TPC-C 
> benchmark.
> 
> - Query Execution Plans
> 
>   [OLD version]
>   HashAggregate  (cost=11972.38..12266.20 rows=29382 width=29) (actual 
> time=233.543..324.973 rows=300144 loops=1)
> Group Key: ref_0.i_im_id, ref_1.ol_dist_info
> ->  Nested Loop Left Join  (cost=0.29..10471.64 rows=300148 width=29) 
> (actual time=0.012..114.955 rows=300148 loops=1)
>   ->  Seq Scan on order_line ref_1  (cost=0.00..6711.48 rows=300148 
> width=25) (actual time=0.004..25.061 rows=300148 loops=1)
>   ->  Materialize  (cost=0.29..8.32 rows=1 width=4) (actual 
> time=0.000..0.000 rows=1 loops=300148)
> ->  Index Scan using item_pkey on item ref_0  
> (cost=0.29..8.31 rows=1 width=4) (actual time=0.005..0.005 rows=1 loops=1)
>   Index Cond: (i_id = 10)
>   Planning time: 0.267 ms
>   Execution time: 338.027 ms
> 
> 
>   [NEW version]
>   Unique  (cost=44960.08..47211.19 rows=300148 width=29) (actual 
> time=646.545..885.502 rows=300144 loops=1)
> ->  Sort  (cost=44960.08..45710.45 rows=300148 width=29) (actual 
> time=646.544..838.933 rows=300148 loops=1)
>   Sort Key: ref_0.i_im_id, ref_1.ol_dist_info
>   Sort Method: external merge  Disk: 11480kB
>   ->  Nested Loop Left Join  (cost=0.29..10471.64 rows=300148 
> width=29) (actual time=0.016..111.889 rows=300148 loops=1)
> ->  Seq Scan on order_line ref_1  (cost=0.00..6711.48 
> rows=300148 width=25) (actual time=0.004..24.612 rows=300148 loops=1)
> ->  Materialize  (cost=0.29..8.32 rows=1 width=4) (actual 
> time=0.000..0.000 rows=1 loops=300148)
>   ->  Index Scan using item_pkey on item ref_0  
> (cost=0.29..8.31 rows=1 width=4) (actual time=0.008..0.008 rows=1 loops=1)
> Index Cond: (i_id = 10)
>   Planning Time: 0.341 ms
>   Execution Time: 896.662 ms

This seems perfectly alright - the old version used more memory than
work_mem actually should have allowed. I'd bet you get the performance
back if you set work mem to a bigger value.


> ### QUERY 3:
> 
>   select
> cast(ref_1.ol_i_id as int4) as c0
>   from
> public.stock as ref_0
>   left join public.order_line as ref_1
>   on (ref_1.ol_number is not null)
>   where ref_1.ol_number is null
> 
> - Commit: 77cd477 (Enable parallel query by default.)
> 
> - Our analysis: We believe that this regression is due to parallel queries 
> being enabled by default. Surprisingly, we found that even on a larger TPC-C 
> database (scale factor of 50, roughly 4GB), parallel scan is still slower 
> than the non-parallel one in the old version, when the query is not returning 
> any tuples.
> 
> - Query Execution Plans
> 
>   [OLD version]
>   Nested Loop Anti Join  (cost=0.00..18006.81 rows=1 width=4) (actual 
> time=28.689..28.689 rows=0 loops=1)
> ->  Seq Scan on stock ref_0  (cost=0.00..5340.00 rows=10 width=0) 
> (actual time=0.028..15.722 rows=10 loops=1)
> ->  Materialize  (cost=0.00..9385.22 rows=300148 width=4) (actual 
> time=0.000..0.000 rows=1 loops=10)
>   ->  Seq Scan on order_line ref_1  (cost=0.00..6711.48 rows=300148 
> width=4) (actual time=0.004..0.004 rows=1 loops=1)
> Filter: (ol_number IS NOT NULL)
>   Planning time: 0.198 ms
>   Execution time: 28.745 ms
> 
>   [NEW version]
>   Gather  (cost=1000.00..15164.93 rows=1 width=4) (actual time=91.022..92.634 
> rows=0 loops=1)
> Workers Planned: 2
> Workers Launched: 2
> ->  Nested Loop Anti Join  (cost=0.00..14164.83 rows=1 width=4) (actual 
> time=88.889..88.889 rows=0 loops=3)
>   ->  Parallel Seq Scan on stock ref_0  (cost=0.00..4756.67 
> rows=41667 width=0) (actual time=0.025..7.331 rows=3 loops=3)
>   ->  Seq Scan on order_line ref_1  (cost=0.00..6711.48 rows=300148 
> width=4) (actual time=0.002..0.002 rows=1 loops=10)
> Filter: (ol_number IS NOT NULL)
>   Planning Time: 0.258 ms
>   Execution Time: 92.699 ms

I'm not particularly bothered - this is a pretty small difference. Most
of the time here is likely spent starting the workers, the cost of which
is hard to predict/model accurately.


> ### QUERY 4:
> 
>   select
> ref_0.s_dist_06 as c0
>   from
> public.stock as ref_0
>   where (ref_0.s_w_id < cast(least(0, 1) as int8))
> 
> - Commit: 5edc63b (Account for the effect of lossy pages when cos

Re: constraint exclusion with ineq condition (Re: server hardware tuning.)

[Benedict Holland]

Hi Sugathi,

That sounds like a perfect task for a view if the joins are all the same.

~Ben



On Fri, Feb 15, 2019 at 9:36 AM suganthi Sekar 
wrote:

> Hi,
>
>
> yes i accept , but when i will do for existing tables, i am facing issue.
>
>
>
> I have created 100 Function , all the function having  five table join(*now
> all partition by date*) , now its not possible to change where condition
> in all 100 Function.
>
> so that i am trying any other possibilities are there.
>
>
>
> Regards,
>
> Suganthi Sekar
> --
> *From:* Michael Lewis 
> *Sent:* 15 February 2019 00:20:00
> *To:* suganthi Sekar
> *Cc:* Justin Pryzby; pgsql-performance@lists.postgresql.org
> *Subject:* Re: constraint exclusion with ineq condition (Re: server
> hardware tuning.)
>
> Yeah, the planner doesn't know that call_created_date can be limited on
> both tables unless you tell it specify it in the where condition as Laurenz
> said on another thread.
>
>
> *Michael Lewis*
>
> On Thu, Feb 14, 2019 at 7:35 AM suganthi Sekar 
> wrote:
>
> Both table Portion by  same column call_created_date
> 
> From: Michael Lewis 
> Sent: 14 February 2019 19:35:48
> To: suganthi Sekar
> Cc: Justin Pryzby; pgsql-performance@lists.postgresql.org
> Subject: Re: constraint exclusion with ineq condition (Re: server hardware
> tuning.)
>
> What are these two tables partitioned by?
>
> On Thu, Feb 14, 2019, 5:03 AM suganthi Sekar   wrote:
> Hi,
>
> Thanks, i know if explicitly we give in where condition it is working.
>
> i thought with below parameter in Postgresq11 this issue is fixed ?
>
>  enable_partitionwise_join  to 'on';
>
>  what is the use of enable_partitionwise_join  to 'on';
>
> Thanks for your response.
>
> Regards
> Suganthi Sekar
> 
> From: Justin Pryzby mailto:pry...@telsasoft.com>>
> Sent: 14 February 2019 16:10:01
> To: suganthi Sekar
> Cc: pgsql-performance@lists.postgresql.org pgsql-performance@lists.postgresql.org>
> Subject: Re: constraint exclusion with ineq condition (Re: server hardware
> tuning.)
>
> On Thu, Feb 14, 2019 at 10:38:36AM +, suganthi Sekar wrote:
> > u mean the below parameter need to set on . its already on only.
> >   alter system set  constraint_exclusion  to 'on';
>
> No, I said:
> > You can work around it by specifying the same condition on
> b.call_created_date:
> > >  AND b.call_created_date >='2017-11-01' AND
> b.call_created_date<'2017-11-30'
>
>

Re: ERROR: unrecognized parameter "autovacuum_analyze_scale_factor"

[Mariel Cherkassky]

'but then I don't have accurate statistics on my toasted table..

On Fri, Feb 15, 2019, 3:39 PM Alvaro Herrera  On 2019-Feb-14, Mariel Cherkassky wrote:
>
> > I meant the anaylze, if anaylze will run very often on the original
> table,
> > arent there disadvantages for it ?
>
> It'll waste time and resources pointlessly.  Don't do it -- it won't do
> any good.
>
>
> --
> Álvaro Herrerahttps://www.2ndQuadrant.com/
> PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
>