Explain plan changes - IN CLAUSE ( Passing direct values Vs INNER Query )
Hi,
PostgreSQL version : PostgreSQL 9.6.2 on x86_64-pc-linux-gnu, compiled by
gcc (GCC) 4.8.3 20140911 (Red Hat 4.8.3-9), 64-bit
We have noticed huge difference interms of execution plan ( response time)
, When we pass the direct values Vs inner query to IN clause.
High level details of the use case are as follows
- As part of the SQL there are 2 tables named Process_instance (master)
and Process_activity ( child)
- Wanted to fetch TOP 50 rows from Process_activity table for the given
values of the Process_instance.
- When we used Inner Join / Inner query ( query1) between parent table
and child table , LIMIT is not really taking in to account. Instead it is
fetching more rows and columns that required, and finally limiting the
result
-
*Query1*
web_1=> EXPLAIN (ANALYZE, COSTS, VERBOSE, BUFFERS) SELECT
pa.process_activity_id FROM process_activity pa WHERE pa.app_id =
'427380312000560' AND pa.created > '1970-01-01 00:00:00' AND
pa.process_instance_id in *(SELECT pi.process_instance_id FROM
process_instance pi WHERE pi.user_id = '317079413683604' AND pi.app_id =
'427380312000560')* ORDER BY pa.process_instance_id,pa.created limit 50;
QUERY PLAN
--
Limit (cost=1071.47..1071.55 rows=31 width=24) (actual
time=85.958..85.991 rows=50 loops=1)
Output: pa.process_activity_id, pa.process_instance_id, pa.created
Buffers: shared hit=43065
-> Sort (cost=1071.47..1071.55 rows=31 width=24) (actual
time=85.956..85.971 rows=50 loops=1)
Output: pa.process_activity_id, pa.process_instance_id, pa.created
Sort Key: pa.process_instance_id, pa.created
Sort Method: top-N heapsort Memory: 28kB
Buffers: shared hit=43065
-> Nested Loop (cost=1.14..1070.70 rows=31 width=24) (actual
time=0.031..72.183 rows=46992 loops=1)
Output: pa.process_activity_id, pa.process_instance_id,
pa.created
Buffers: shared hit=43065
-> Index Scan using fki_conv_konotor_user_user_id on
public.process_instance pi (cost=0.43..2.66 rows=1 width=8) (actual
time=0.010..0.013 rows=2 loops=1)
Output: pi.process_instance_id
Index Cond: (pi.user_id = '317079413683604'::bigint)
Filter: (pi.app_id = '427380312000560'::bigint)
Buffers: shared hit=5
-> Index Scan using
process_activity_process_instance_id_app_id_created_idx on
public.process_activity pa (cost=0.70..1053.80 rows=1425 width=24) (actual
time=0.015..20.702 rows=*23496* loops=2)
* Output: pa.process_activity_id, pa.process_activity_type, pa.voice_url,
pa.process_activity_user_id, pa.app_id, pa.process_instance_id, pa.alias,
pa.read_by_user, pa.source, pa.label_category_id, pa.label_id,
pa.csat_response_id, pa.process_activity_fragments, pa.created, pa.updated,
pa.rule_id, pa.marketing_reply_id, pa.delivered_at, pa.reply_fragments,
pa.status_fragment, pa.internal_meta, pa.interaction_id,
pa.do_not_translate, pa.should_translate, pa.in_reply_to*
Index Cond: ((pa.process_instance_id =
pi.process_instance_id) AND (pa.app_id = '427380312000560'::bigint) AND
(pa.created > '1970-01-01 00:00:00'::timestamp without time zone))
Buffers: shared hit=43060
Planning time: 0.499 ms
Execution time: 86.040 ms
(22 rows)
*Query 2*
web_1=> EXPLAIN (ANALYZE, COSTS, VERBOSE, BUFFERS) SELECT
pa.process_activity_id AS m_process_activity_id FROM process_activity m
WHERE pa.app_id = '427380312000560' AND pa.created > '1970-01-01 00:00:00'
AND pa.process_instance_id in (
*240117466018927,325820556706970,433008275197305*) ORDER BY
pa.process_instance_id,pa.created limit 50;
QUERY PLAN
-
Limit (cost=0.70..37.66 rows=50 width=24) (actual time=0.023..0.094
rows=50 loops=1)
Output: process_activity_id, process_instance_id, created
Buffers: shared hit=50
-> Index Scan using
process_activity_process_instance_id_app_id_created_idx on
public.process_activity pa (cost=0.70..3124.97 rows=4226 width=24) (actual
time=0.022..0.079 *rows=50* loops=1)
Output: process_activity_id, process_instance_id, created
Index Cond: ((pa.process_instance_id = ANY
('{140117466018927,225820556706970,233008275197305}'::bigint[])) AND
(pa.app_id = '427380312000560'::bigint) AND (pa.created > '1970-01-01
00:00:00'::timestamp without time zone))
Buffers: shared hi
Re: Explain plan changes - IN CLAUSE ( Passing direct values Vs INNER Query )
On 5/7/20 4:19 AM, Amarendra Konda wrote: Hi, PostgreSQL version : PostgreSQL 9.6.2 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.3 20140911 (Red Hat 4.8.3-9), 64-bit We have noticed huge difference interms of execution plan ( response time) , When we pass the direct values Vs inner query to IN clause. High level details of the use case are as follows * As part of the SQL there are 2 tables named Process_instance (master) and Process_activity ( child) * Wanted to fetch TOP 50 rows from Process_activity table for the given values of the Process_instance. * When we used Inner Join / Inner query ( query1) between parent table and child table , LIMIT is not really taking in to account. Instead it is fetching more rows and columns that required, and finally limiting the result It is doing what you told it to do which is SELECT all process_instance_i's for user_id='317079413683604' and app_id = '427380312000560' and then filtering further. I am going to guess that if you run the inner query alone you will find it returns ~23496 rows. You might have better results if you an actual join between process_activity and process_instance. Something like below(obviously not tested): SELECT pa.process_activity_id FROM process_activity pa JOIN process_instance pi ON pa.process_instance_id = pi.process_instance_id WHERE pa.app_id = '427380312000560' AND pa.created > '1970-01-01 00:00:00' AND pi.user_id = '317079413683604' ORDER BY pa.process_instance_id, pa.created LIMIT 50; The second query is not equivalent as you are not filtering on user_id and you are filtering on only three process_instance_id's. * *Query1* web_1=> EXPLAIN (ANALYZE, COSTS, VERBOSE, BUFFERS) SELECT pa.process_activity_id FROM process_activity pa WHERE pa.app_id = '427380312000560' AND pa.created > '1970-01-01 00:00:00' AND pa.process_instance_id in *_(SELECT pi.process_instance_id FROM process_instance pi WHERE pi.user_id = '317079413683604' AND pi.app_id = '427380312000560')_* ORDER BY pa.process_instance_id,pa.created limit 50; QUERY PLAN -- Limit (cost=1071.47..1071.55 rows=31 width=24) (actual time=85.958..85.991 rows=50 loops=1) Output: pa.process_activity_id, pa.process_instance_id, pa.created Buffers: shared hit=43065 -> Sort (cost=1071.47..1071.55 rows=31 width=24) (actual time=85.956..85.971 rows=50 loops=1) Output: pa.process_activity_id, pa.process_instance_id, pa.created Sort Key: pa.process_instance_id, pa.created Sort Method: top-N heapsort Memory: 28kB Buffers: shared hit=43065 -> Nested Loop (cost=1.14..1070.70 rows=31 width=24) (actual time=0.031..72.183 rows=46992 loops=1) Output: pa.process_activity_id, pa.process_instance_id, pa.created Buffers: shared hit=43065 -> Index Scan using fki_conv_konotor_user_user_id on public.process_instance pi (cost=0.43..2.66 rows=1 width=8) (actual time=0.010..0.013 rows=2 loops=1) Output: pi.process_instance_id Index Cond: (pi.user_id = '317079413683604'::bigint) Filter: (pi.app_id = '427380312000560'::bigint) Buffers: shared hit=5 -> Index Scan using process_activity_process_instance_id_app_id_created_idx on public.process_activity pa (cost=0.70..1053.80 rows=1425 width=24) (actual time=0.015..20.702 rows=*23496* loops=2) * Output: pa.process_activity_id, pa.process_activity_type, pa.voice_url, pa.process_activity_user_id, pa.app_id, pa.process_instance_id, pa.alias, pa.read_by_user, pa.source, pa.label_category_id, pa.label_id, pa.csat_response_id, pa.process_activity_fragments, pa.created, pa.updated, pa.rule_id, pa.market ing_reply_id, pa.delivered_at, pa.reply_fragments, pa.status_fragment, pa.internal_meta, pa.interaction_id, pa.do_not_translate, pa.should_translate, pa.in_reply_to* Index Cond: ((pa.process_instance_id = pi.process_instance_id) AND (pa.app_id = '427380312000560'::bigint) AND (pa.created > '1970-01-01 00:00:00'::timestamp without time zone)) Buffers: shared hit=43060 Planning time: 0.499 ms Execution time: 86.040 ms (22 rows) *_Query 2_* web_1=> EXPLAIN (ANALYZE, COSTS, VERBOSE, BUFFERS) SELECT pa.process_activity_id AS m_process_activity_id
Re: Explain plan changes - IN CLAUSE ( Passing direct values Vs INNER Query )
On Thu, May 7, 2020 at 7:40 AM Adrian Klaver wrote: > On 5/7/20 4:19 AM, Amarendra Konda wrote: > > Hi, > > > > PostgreSQL version : PostgreSQL 9.6.2 on x86_64-pc-linux-gnu, compiled > > by gcc (GCC) 4.8.3 20140911 (Red Hat 4.8.3-9), 64-bit > > > > We have noticed huge difference interms of execution plan ( response > > time) , When we pass the direct values Vs inner query to IN clause. > > > > High level details of the use case are as follows > > > > * As part of the SQL there are 2 tables named Process_instance > > (master) and Process_activity ( child) > > * Wanted to fetch TOP 50 rows from Process_activity table for the > > given values of the Process_instance. > > * When we used Inner Join / Inner query ( query1) between parent > > table and child table , LIMIT is not really taking in to account. > > Instead it is fetching more rows and columns that required, and > > finally limiting the result > > It is doing what you told it to do which is SELECT all > process_instance_i's for user_id='317079413683604' and app_id = > '427380312000560' and then filtering further. I am going to guess that > if you run the inner query alone you will find it returns ~23496 rows. > You might have better results if you an actual join between > process_activity and process_instance. Something like below(obviously > not tested): > What the OP seems to want is a semi-join: (not tested) SELECT pa.process_activity_id FROM process_activity pa WHERE pa.app_id = '427380312000560' AND pa.created > '1970-01-01 00:00:00' AND EXISTS ( SELECT 1 FROM process_instance pi WHERE pi.app_id = pa.app_id AND pi.user_id = '317079413683604' ) ORDER BY pa.process_instance_id, pa.created limit 50; I'm unsure exactly how this will impact the plan choice but it should be an improvement, and in any case more correctly defines what it is you are looking for. David J.
Re: Explain plan changes - IN CLAUSE ( Passing direct values Vs INNER Query )
Hi Adrian, Thanks for the reply. And i have kept latest execution plans, for various SQL statements ( inner join, sub queries and placing values instead of sub query) . As suggested, tried with INNER JOIN, however result was similar to subquery. Is there any way we can tell the optimiser to process less number of rows based on the LIMIT value ? ( i.e. may be SQL re-write) ? *INNER SQL* EXPLAIN (ANALYZE, COSTS, VERBOSE, BUFFERS) SELECT pi.process_instance_id AS pi_process_instance_id FROM process_instance pi WHERE pi.user_id = '137074931866340' AND pi.app_id = '126502930200650'; QUERY PLAN - Index Scan using fki_conv_konotor_user_user_id on public.process_instance pi (cost=0.43..2.66 rows=1 width=8) *(actual time=0.018..0.019 rows=2 loops=1)* Output: process_instance_id Index Cond: (pi.user_id = '137074931866340'::bigint) Filter: (pi.app_id = '126502930200650'::bigint) Buffers: shared hit=5 Planning time: 0.119 ms Execution time: 0.041 ms *Full query - Sub query* EXPLAIN (ANALYZE, COSTS, VERBOSE, BUFFERS) SELECT pa.process_activity_id AS pa_process_activity_id FROM process_activity pa WHERE pa.app_id = '126502930200650' AND pa.created > '1970-01-01 00:00:00' AND pa.process_instance_id in (SELECT pi.process_instance_id AS pi_process_instance_id FROM process_instance pi WHERE pi.user_id = '137074931866340' AND pi.app_id = '126502930200650') ORDER BY pa.process_instance_id, pa.created limit 50; QUERY PLAN -- -- --- Limit (cost=1072.91..1072.99 rows=31 width=24) (actual time=744.386..744.415 rows=50 loops=1) Output: pa.process_activity_id, pa.process_instance_id, pa.created Buffers: shared hit=3760 read=39316 -> Sort (cost=1072.91..1072.99 rows=31 width=24) (actual time=744.384..744.396 rows=50 loops=1) Output: pa.process_activity_id, pa.process_instance_id, pa.created Sort Key: pa.process_instance_id, pa.created Sort Method: top-N heapsort Memory: 28kB Buffers: shared hit=3760 read=39316 -> Nested Loop (cost=1.14..1072.14 rows=31 width=24) (actual time=0.044..727.297 rows=47011 loops=1) Output: pa.process_activity_id, pa.process_instance_id, pa.created Buffers: shared hit=3754 read=39316 -> Index Scan using fki_conv_konotor_user_user_id on public.process_instance pi (cost=0.43..2.66 rows=1 width=8) *(actual time=0.009..0.015 rows=2 loops=1)* Output: pi.process_instance_id Index Cond: (pi.user_id = '137074931866340'::bigint) Filter: (pi.app_id = '126502930200650'::bigint) Buffers: shared hit=5 -> Index Scan using process_activity_process_instance_id_app_id_created_idx on public.process_activity pa (cost=0.70..1055.22 rows=1427 width=24) *(actual time=0.029..349.000 rows=23506 loops=2)* Output: pa.process_activity_id, pa.process_activity_type, pa.voice_url, pa.process_activity_user_id, pa.app_id, pa.process_instance_id, pa.alias, pa.read_by_user, pa.source, pa.label_category_id, pa.label_id, pa.csat_respons e_id, pa.process_activity_fragments, pa.created, pa.updated, pa.rule_id, pa.marketing_reply_id, pa.delivered_at, pa.reply_fragments, pa.status_fragment, pa.internal_meta, pa.interaction_id, pa.do_not_translate, pa.should_tr anslate, pa.in_reply_to Index Cond: ((pa.process_instance_id = pi.process_instance_id) AND (pa.app_id = '126502930200650'::bigint) AND (pa.created > '1970-01-01 00:00:00'::timestamp without time zone)) Buffers: shared hit=3749 read=39316 Planning time: 2.547 ms Execution time: 744.499 ms (22 rows) *Full query - INNER JOIN* EXPLAIN (ANALYZE, COSTS, VERBOSE, BUFFERS) SELECT pa.process_activity_id AS pa_process_activity_id FROM process_activity pa INNER JOIN process_instance pi ON pi.process_instance_id = pa.process_instance_id AND pa.app_id = '126502930200650' AND pa.created > '1970-01-01 00:00:00' AND pi.user_id = '137074931866340' AND pi.app_id = '126502930200650' ORDER BY pa.process_instance_id, pa.created limit 50; QUERY PLAN -- ---
Re: Explain plan changes - IN CLAUSE ( Passing direct values Vs INNER Query )
Hi David, Thanks for the reply.This has optimized number of rows. Can you please explain, why it is getting more columns in output, even though we have asked for only one column ? EXPLAIN (ANALYZE, COSTS, VERBOSE, BUFFERS) SELECT pa.process_activity_id AS pa_process_activity_id FROM process_activity pa WHERE pa.app_id = '126502930200650' AND pa.created > '1970-01-01 00:00:00' AND EXISTS ( SELECT 1 FROM process_instance pi where pi.app_id = pa.app_id AND pi.user_id = '137074931866340') ORDER BY pa.process_instance_id,m.created limit 50; QUERY PLAN -- -- - Limit (cost=1.14..37.39 rows=50 width=24) (actual time=821.283..891.629 rows=50 loops=1) Output: pa.process_activity_id, pa.process_instance_id, pa.created Buffers: shared hit=274950 -> Nested Loop Semi Join (cost=1.14..20108.78 rows=367790473 width=24) (actual time=821.282..891.607 rows=50 loops=1) Output: pa.process_activity_id, pa.process_instance_id, pa.created Buffers: shared hit=274950 -> Index Scan using process_activity_process_instance_id_app_id_created_idx on public.process_activity pa (cost=0.70..262062725.21 rows=367790473 width=32) (actual time=821.253..891.517 rows=50 loops=1) * Output: pa.process_activity_id, pa.process_activity_type, pa.voice_url, pa.process_activity_user_id, pa.app_id, pa.process_instance_id, pa.alias, pa.read_by_user, pa.source, pa.label_category_id, pa.label_id, pa.csat_response_id, m.process_activity_fragments, pa.created, pa.updated, pa.rule_id, pa.marketing_reply_id, pa.delivered_at, pa.reply_fragments, pa.status_fragment, pa.internal_meta, pa.interaction_id, pa.do_not_translate, pa.should_translate, pa.in_reply_to* Index Cond: ((m.app_id = '126502930200650'::bigint) AND (m.created > '1970-01-01 00:00:00'::timestamp without time zone)) Buffers: shared hit=274946 -> Materialize (cost=0.43..2.66 rows=1 width=8) (actual time=0.001..0.001 rows=1 loops=50) Output: pi.app_id Buffers: shared hit=4 -> Index Scan using fki_conv_konotor_user_user_id on public.process_instance pi (cost=0.43..2.66 rows=1 width=8) (actual time=0.020..0.020 rows=1 loops=1) Output: pi.app_id Index Cond: (pi.user_id = '137074931866340'::bigint) Filter: (pi.app_id = '126502930200650'::bigint) Buffers: shared hit=4 Planning time: 0.297 ms Execution time: 891.686 ms (20 rows) On Thu, May 7, 2020 at 9:17 PM David G. Johnston wrote: > On Thu, May 7, 2020 at 7:40 AM Adrian Klaver > wrote: > >> On 5/7/20 4:19 AM, Amarendra Konda wrote: >> > Hi, >> > >> > PostgreSQL version : PostgreSQL 9.6.2 on x86_64-pc-linux-gnu, compiled >> > by gcc (GCC) 4.8.3 20140911 (Red Hat 4.8.3-9), 64-bit >> > >> > We have noticed huge difference interms of execution plan ( response >> > time) , When we pass the direct values Vs inner query to IN clause. >> > >> > High level details of the use case are as follows >> > >> > * As part of the SQL there are 2 tables named Process_instance >> > (master) and Process_activity ( child) >> > * Wanted to fetch TOP 50 rows from Process_activity table for the >> > given values of the Process_instance. >> > * When we used Inner Join / Inner query ( query1) between parent >> > table and child table , LIMIT is not really taking in to account. >> > Instead it is fetching more rows and columns that required, and >> > finally limiting the result >> >> It is doing what you told it to do which is SELECT all >> process_instance_i's for user_id='317079413683604' and app_id = >> '427380312000560' and then filtering further. I am going to guess that >> if you run the inner query alone you will find it returns ~23496 rows. >> You might have better results if you an actual join between >> process_activity and process_instance. Something like below(obviously >> not tested): >> > > What the OP seems to want is a semi-join: > > (not tested) > > SELECT pa.process_activity_id > FROM process_activity pa WHERE pa.app_id = '427380312000560' AND > pa.created > '1970-01-01 00:00:00' > AND EXISTS ( > SELECT 1 FROM process_instance pi WHERE pi.app_id = pa.app_id AND > pi.user_id = '317079413683604' > ) > ORDER BY > pa.process_instance_id, > pa.created limit 50; > > I'm unsure exactly how this will impact the plan choice but it should be > an improvement, and in any case more correctly defines what it is you are > looking for. > > David J. > >
Re: Explain plan changes - IN CLAUSE ( Passing direct values Vs INNER Query )
Hi David, In earlier reply, Over looked another condition, hence please ignore that one Here is the correct one with all the needed conditions. According to the latest one, exists also not limiting rows from the process_activity table. EXPLAIN (ANALYZE, COSTS, VERBOSE, BUFFERS) SELECT pa.process_activity_id AS pa_process_activity_id FROM process_activity pa WHERE pa.app_id = '126502930200650' AND pa.created > '1970-01-01 00:00:00' AND EXISTS ( SELECT 1 FROM process_instance pi where pi.app_id = pa.app_id AND *pi.process_instance_id = pa.process_instance_id * AND pi.user_id = '137074931866340') ORDER BY pa.process_instance_id, pa.created limit 50; QUERY PLAN -- -- --- Limit (cost=1079.44..1079.52 rows=32 width=24) (actual time=85.747..85.777 rows=50 loops=1) Output: pa.process_activity_id, pa.process_instance_id, pa.created Buffers: shared hit=43070 -> Sort (cost=1079.44..1079.52 rows=32 width=24) (actual time=85.745..85.759 rows=50 loops=1) Output: pa.process_activity_id, pa.process_instance_id, pa.created Sort Key: pa.process_instance_id, pa.created Sort Method: top-N heapsort Memory: 28kB Buffers: shared hit=43070 -> Nested Loop (cost=1.14..1078.64 rows=32 width=24) (actual time=0.025..72.115 rows=47011 loops=1) Output: pa.process_activity_id, pa.process_instance_id, pa.created Buffers: shared hit=43070 -> Index Scan using fki_conv_konotor_user_user_id on public.process_instance pi (cost=0.43..2.66 rows=1 width=16) (actual time=0.010..0.015 rows=2 loops=1) Output: pi.app_id, pi.process_instance_id Index Cond: (c.user_id = '137074931866340'::bigint) Filter: (c.app_id = '126502930200650'::bigint) Buffers: shared hit=5 -> Index Scan using process_activity_process_instance_id_app_id_created_idx on public.process_activity pa (cost=0.70..1061.62 rows=1436 width=32) *(actual time=0.011..20.320 rows=23506 loops=2)* Output: pa.process_activity_id, pa.process_activity_type, pa.voice_url, pa.process_activity_user_id, pa.app_id, pa.process_instance_id, pa.alias, pa.read_by_user, pa.source, pa.label_category_id, pa.label_id, pa.csat_respons e_id, pa.process_activity_fragments, pa.created, pa.updated, pa.rule_id, pa.marketing_reply_id, pa.delivered_at, pa.reply_fragments, pa.status_fragment, pa.internal_meta, pa.interaction_id, pa.do_not_translate, pa.should_tr anslate, pa.in_reply_to Index Cond: ((m.process_instance_id = pi.process_instance_id) AND (m.app_id = '126502930200650'::bigint) AND (m.created > '1970-01-01 00:00:00'::timestamp without time zone)) Buffers: shared hit=43065 Planning time: 0.455 ms Execution time: 85.830 ms On Thu, May 7, 2020 at 11:19 PM Amarendra Konda wrote: > Hi David, > > Thanks for the reply.This has optimized number of rows. > > Can you please explain, why it is getting more columns in output, even > though we have asked for only one column ? > > > EXPLAIN (ANALYZE, COSTS, VERBOSE, BUFFERS) SELECT pa.process_activity_id > AS pa_process_activity_id FROM process_activity pa WHERE pa.app_id = > '126502930200650' AND pa.created > '1970-01-01 00:00:00' AND EXISTS ( > SELECT 1 FROM process_instance pi where pi.app_id = pa.app_id AND > pi.user_id = '137074931866340') ORDER BY pa.process_instance_id,m.created > limit 50; > > > >QUERY PLAN > > > > > -- > > -- > - > Limit (cost=1.14..37.39 rows=50 width=24) (actual time=821.283..891.629 > rows=50 loops=1) >Output: pa.process_activity_id, pa.process_instance_id, pa.created >Buffers: shared hit=274950 >-> Nested Loop Semi Join (cost=1.14..20108.78 rows=367790473 > width=24) (actual time=821.282..891.607 rows=50 loops=1) > Output: pa.process_activity_id, pa.process_instance_id, pa.created > Buffers: shared hit=274950 > -> Index Scan using > process_activity_process_instance_id_app_id_created_idx on > public.process_activity pa (cost=0.70..262062725.21 rows=367790473 > width=32) (act
AutoVacuum and growing transaction XID's
Hello Team, We are using a PostgreSQL version -9.6.12 version and from last 4 weeks our Transaction ID's (XID's) have increased by 195 million to 341 million transactions. I see the below from pg_stat_activity from the postGreSQL DB. 1) Viewing the pg_stat-activity I noticed that the vacuum query is running for a runtime interval of few hours to 3-5 days whenever I check the pg_stat-activity. Is this a common process postgreSQL runs ? I have noticed this running and show in the pg_stat activity from last few weeks only. Also the query shows the table name with (to prevent wrap around) for each of the tables in the vacuum query as output. What does this mean ? 2) Does it mean I need to run a manual auto vacuum process for these tables ? as the transaction ids have increased from 195 million to 341 million ?. What other things I need to check in the database around this ?. Thanks !!
Re: AutoVacuum and growing transaction XID's
It is trying to do a vacuum freeze. Do you have autovacuum turned off? Any settings changed from default related to autovacuum? https://www.postgresql.org/docs/9.6/routine-vacuuming.html Read 24.1.5. Preventing Transaction ID Wraparound Failures These may also be of help- https://info.crunchydata.com/blog/managing-transaction-id-wraparound-in-postgresql https://www.2ndquadrant.com/en/blog/managing-freezing/ Note that you need to ensure the server gets caught up, or you risk being locked out to prevent data corruption.
Re: Explain plan changes - IN CLAUSE ( Passing direct values Vs INNER Query )
Hi Virendra, Thanks for your time. Here is the table and index structure * process_activity* Table "public.process_activity" Column |Type | Modifiers +-+ process_activity_id | bigint | not null default next_id() process_activity_type | smallint| not null voice_url | text| process_activity_user_id| bigint | not null app_id | bigint | not null process_instance_id| bigint | not null alias | text| not null read_by_user | smallint| default 0 source | smallint| default 0 label_category_id | bigint | label_id | bigint | csat_response_id | bigint | process_activity_fragments | jsonb | created| timestamp without time zone | not null updated| timestamp without time zone | rule_id| bigint | marketing_reply_id | bigint | delivered_at | timestamp without time zone | reply_fragments| jsonb | status_fragment| jsonb | internal_meta | jsonb | interaction_id | text| do_not_translate | boolean | should_translate | integer | in_reply_to| jsonb | Indexes: "process_activity_pkey" PRIMARY KEY, btree (process_activity_id) "fki_process_activity_konotor_user_user_id" btree (process_activity_user_id) WITH (fillfactor='70') "*process_activity_process_instance_id_app_id_created_idx*" btree (process_instance_id, app_id, created) WITH (fillfactor='70') "process_activity_process_instance_id_app_id_read_by_user_created_idx" btree (process_instance_id, app_id, read_by_user, created) WITH (fillfactor='70') "process_activity_process_instance_id_idx" btree (process_instance_id) WITH (fillfactor='70') *process_instance* Table "public.process_instance" Column |Type | Modifiers -+-+- process_instance_id | bigint | not null default next_id() process_instance_alias | text| not null app_id | bigint | not null user_id | bigint | not null Indexes: "process_instance_pkey" PRIMARY KEY, btree (process_instance_id) "*fki_conv_konotor_user_user_id*" btree (user_id) WITH (fillfactor='70') Regards, Amarendra On Fri, May 8, 2020 at 12:01 AM Virendra Kumar wrote: > Sending table structure with indexes might help little further in > understanding. > > Regards, > Virendra > > On Thursday, May 7, 2020, 11:08:14 AM PDT, Amarendra Konda < > [email protected]> wrote: > > > Hi David, > > In earlier reply, Over looked another condition, hence please ignore that > one > > Here is the correct one with all the needed conditions. According to the > latest one, exists also not limiting rows from the process_activity table. > > > EXPLAIN (ANALYZE, COSTS, VERBOSE, BUFFERS) SELECT pa.process_activity_id > AS pa_process_activity_id FROM process_activity pa WHERE pa.app_id = > '126502930200650' AND pa.created > '1970-01-01 00:00:00' AND EXISTS ( > SELECT 1 FROM process_instance pi where pi.app_id = pa.app_id AND > *pi.process_instance_id > = pa.process_instance_id * AND pi.user_id = '137074931866340') ORDER BY > pa.process_instance_id, pa.created limit 50; > > > > QUERY PLAN > > > > > -- > > -- > --- > Limit (cost=1079.44..1079.52 rows=32 width=24) (actual > time=85.747..85.777 rows=50 loops=1) >Output: pa.process_activity_id, pa.process_instance_id, pa.created >Buffers: shared hit=43070 >-> Sort (cost=1079.44..1079.52 rows=32 width=24) (actual > time=85.745..85.759 rows=50 loops=1) > Output: pa.process_activity_id, pa.process_instance_id, pa.created > Sort Key: pa.process_instance_id, pa.created > Sort Method: top-N heapsort Memory: 28kB > Buffers: shared hit=43070 > -> Nest
Re: Explain plan changes - IN CLAUSE ( Passing direct values Vs INNER Query )
On 5/7/20 10:49 AM, Amarendra Konda wrote: Hi David, Thanks for the reply.This has optimized number of rows. Yeah, but your execution time has increased an order of magnitude. Not sure if that is what you want. Can you please explain, why it is getting more columns in output, even though we have asked for only one column ? EXPLAIN (ANALYZE, COSTS, VERBOSE, BUFFERS) SELECT pa.process_activity_id AS pa_process_activity_id FROM process_activity pa WHERE pa.app_id = '126502930200650' AND pa.created > '1970-01-01 00:00:00' AND EXISTS ( SELECT 1 FROM process_instance pi where pi.app_id = pa.app_id AND pi.user_id = '137074931866340') ORDER BY pa.process_instance_id,m.created limit 50; QUERY PLAN -- -- - Limit (cost=1.14..37.39 rows=50 width=24) (actual time=821.283..891.629 rows=50 loops=1) Output: pa.process_activity_id, pa.process_instance_id, pa.created Buffers: shared hit=274950 -> Nested Loop Semi Join (cost=1.14..20108.78 rows=367790473 width=24) (actual time=821.282..891.607 rows=50 loops=1) Output: pa.process_activity_id, pa.process_instance_id, pa.created Buffers: shared hit=274950 -> Index Scan using process_activity_process_instance_id_app_id_created_idx on public.process_activity pa (cost=0.70..262062725.21 rows=367790473 width=32) (actual time=821.253..891.517 rows=50 loops=1) * Output: pa.process_activity_id, pa.process_activity_type, pa.voice_url, pa.process_activity_user_id, pa.app_id, pa.process_instance_id, pa.alias, pa.read_by_user, pa.source, pa.label_category_id, pa.label_id, pa.csat_response_id, m.process_activity_fragments, pa.created, pa.updated, pa.rule_id, pa.marketing_reply_id, pa.delivered_at, pa.reply_fragments, pa.status_fragment, pa.internal_meta, pa.interaction_id, pa.do_not_translate, pa.should_translat e, pa.in_reply_to* Index Cond: ((m.app_id = '126502930200650'::bigint) AND (m.created > '1970-01-01 00:00:00'::timestamp without time zone)) Buffers: shared hit=274946 -> Materialize (cost=0.43..2.66 rows=1 width=8) (actual time=0.001..0.001 rows=1 loops=50) Output: pi.app_id Buffers: shared hit=4 -> Index Scan using fki_conv_konotor_user_user_id on public.process_instance pi (cost=0.43..2.66 rows=1 width=8) (actual time=0.020..0.020 rows=1 loops=1) Output: pi.app_id Index Cond: (pi.user_id = '137074931866340'::bigint) Filter: (pi.app_id = '126502930200650'::bigint) Buffers: shared hit=4 Planning time: 0.297 ms Execution time: 891.686 ms (20 rows) On Thu, May 7, 2020 at 9:17 PM David G. Johnston mailto:[email protected]>> wrote: On Thu, May 7, 2020 at 7:40 AM Adrian Klaver mailto:[email protected]>> wrote: On 5/7/20 4:19 AM, Amarendra Konda wrote: > Hi, > > PostgreSQL version : PostgreSQL 9.6.2 on x86_64-pc-linux-gnu, compiled > by gcc (GCC) 4.8.3 20140911 (Red Hat 4.8.3-9), 64-bit > > We have noticed huge difference interms of execution plan ( response > time) , When we pass the direct values Vs inner query to IN clause. > > High level details of the use case are as follows > > * As part of the SQL there are 2 tables named Process_instance > (master) and Process_activity ( child) > * Wanted to fetch TOP 50 rows from Process_activity table for the > given values of the Process_instance. > * When we used Inner Join / Inner query ( query1) between parent > table and child table , LIMIT is not really taking in to account. > Instead it is fetching more rows and columns that required, and > finally limiting the result It is doing what you told it to do which is SELECT all process_instance_i's for user_id='317079413683604' and app_id = '427380312000560' and then filtering further. I am going to guess that if you run the inner query alone you will find it returns ~23496 rows. You might have better results if you an actual join between process_activity and process_instance. Something like below(obviously not tested): What the OP seems to want is a semi-join: (not tested) SELECT pa.process_activity_id FROM process_ac
Re: Explain plan changes - IN CLAUSE ( Passing direct values Vs INNER Query )
Here is my thought on why row is not limiting when joined vs why it is limiting when not joined. When not joined and where clause is having IN, it is using index process_activity_process_instance_id_app_id_created_idx which has columns process_instance_id, created which is in order by and hence no additional ordering is required and a direct rows limit can be applied here. When in join condition it has to fetch rows according to filter clause, join them and then order ( sort node in plan) hence it cannot limit rows while fetching it first time from the table. You are also missing pi.user_id = '317079413683604' in exists clause. It is worth trying to put there and run explain again and see where it takes. But to your point row limitation cannot happen in case of join as such in the query. Regards, Virendra On Thursday, May 7, 2020, 11:52:00 AM PDT, Amarendra Konda wrote: Hi Virendra, Thanks for your time. Here is the table and index structure process_activity Table "public.process_activity" Column | Type | Modifiers +-+ process_activity_id | bigint | not null default next_id() process_activity_type | smallint | not null voice_url | text | process_activity_user_id | bigint | not null app_id | bigint | not null process_instance_id | bigint | not null alias | text | not null read_by_user | smallint | default 0 source | smallint | default 0 label_category_id | bigint | label_id | bigint | csat_response_id | bigint | process_activity_fragments | jsonb | created | timestamp without time zone | not null updated | timestamp without time zone | rule_id | bigint | marketing_reply_id | bigint | delivered_at | timestamp without time zone | reply_fragments | jsonb | status_fragment | jsonb | internal_meta | jsonb | interaction_id | text | do_not_translate | boolean | should_translate | integer | in_reply_to | jsonb | Indexes: "process_activity_pkey" PRIMARY KEY, btree (process_activity_id) "fki_process_activity_konotor_user_user_id" btree (process_activity_user_id) WITH (fillfactor='70') "process_activity_process_instance_id_app_id_created_idx" btree (process_instance_id, app_id, created) WITH (fillfactor='70') "process_activity_process_instance_id_app_id_read_by_user_created_idx" btree (process_instance_id, app_id, read_by_user, created) WITH (fillfactor='70') "process_activity_process_instance_id_idx" btree (process_instance_id) WITH (fillfactor='70') process_instance Table "public.process_instance" Column | Type | Modifiers -+-+- process_instance_id | bigint | not null default next_id() process_instance_alias | text | not null app_id | bigint | not null user_id | bigint | not null Indexes: "process_instance_pkey" PRIMARY KEY, btree (process_instance_id) "fki_conv_konotor_user_user_id" btree (user_id) WITH (fillfactor='70') Regards, Amarendra On Fri, May 8, 2020 at 12:01 AM Virendra Kumar wrote: Sending table structure with indexes might help little further in understanding. Regards, Virendra On Thursday, May 7, 2020, 11:08:14 AM PDT, Amarendra Konda wrote: Hi David, In earlier reply, Over looked another condition, hence please ignore that one Here is the correct one with all the needed conditions. According to the latest one, exists also not limiting rows from the process_activity table. EXPLAIN (ANALYZE, COSTS, VERBOSE, BUFFERS) SELECT pa.process_activity_id AS pa_process_activity_id FROM process_activity pa WHERE pa.app_id = '126502930200650' AND pa.created > '1970-01-01 00:00:00' AND EXISTS ( SELECT 1 FROM process_instance pi where pi.app_id = pa.app_id AND pi.process_instance_id = pa.process_instance_id AND pi.user_id = '137074931866340') ORDER BY pa.process_instance_id, pa.created limit 50;
pg_attribute, pg_class, pg_depend grow huge in count and size with multiple tenants.
Hi experts, Our application serves multiple tenants. Each tenant has the schema with a few hundreds of tables and few functions. We have 2000 clients so we have to create 2000 schemas in a single database. While doing this, i observed that the catalog tables pg_attribute, pg_class, pg_depend grow huge in count and size. Do you think this will be a challenge during execution of every query ? When Postgres parses an sql to find the best execution plan, does it scan any of these catalogs that could eventually take more time? Any other challenges you have come across or foresee in such cases ? Thanks, Sammy.
Re: pg_attribute, pg_class, pg_depend grow huge in count and size with multiple tenants.
On Thu, May 7, 2020 at 1:05 PM samhitha g wrote: > Our application serves multiple tenants. Each tenant has the schema with a > few hundreds of tables and few functions. > We have 2000 clients so we have to create 2000 schemas in a single > database. > That is one option but I wouldn't say you must. If you cannot get individual tables to be multi-tenant you are probably better off having one database per client on a shared cluster - at least given the size of the schema and number of clients. David J.
Re: pg_attribute, pg_class, pg_depend grow huge in count and size with multiple tenants.
Hi, On Thu, May 7, 2020 at 5:18 PM David G. Johnston wrote: > On Thu, May 7, 2020 at 1:05 PM samhitha g > wrote: > >> Our application serves multiple tenants. Each tenant has the schema >> with a few hundreds of tables and few functions. >> We have 2000 clients so we have to create 2000 schemas in a single >> database. >> > > That is one option but I wouldn't say you must. If you cannot get > individual tables to be multi-tenant you are probably better off having one > database per client on a shared cluster - at least given the size of the > schema and number of clients. > I am working on a similar problem. 1 database per each client may be a killer when you have a connection pooler that creates a pool for a unique combination of (user,database). > > David J. > > -- Regards, Avinash Vallarapu +1-902-221-5976
Re: pg_attribute, pg_class, pg_depend grow huge in count and size with multiple tenants.
On 07/05/20, Avinash Kumar ([email protected]) wrote: > >> Our application serves multiple tenants. Each tenant has the schema > >> with a few hundreds of tables and few functions. > >> We have 2000 clients so we have to create 2000 schemas in a single > >> database. > > That is one option but I wouldn't say you must. If you cannot get > > individual tables to be multi-tenant you are probably better off having one > > database per client on a shared cluster - at least given the size of the > > schema and number of clients. > > > I am working on a similar problem. > 1 database per each client may be a killer when you have a connection > pooler that creates a pool for a unique combination of (user,database). One of our clusters has well over 500 databases fronted by pg_bouncer. We get excellent connection "flattening" using pg_bouncer with per-database connection spikes dealt with through a reserve pool. The nice thing about separate databases is that it is easy to scale horizontally. Rory
Re: pg_attribute, pg_class, pg_depend grow huge in count and size with multiple tenants.
Hi, On Thu, May 7, 2020 at 6:08 PM Rory Campbell-Lange wrote: > On 07/05/20, Avinash Kumar ([email protected]) wrote: > > >> Our application serves multiple tenants. Each tenant has the schema > > >> with a few hundreds of tables and few functions. > > >> We have 2000 clients so we have to create 2000 schemas in a single > > >> database. > > > > That is one option but I wouldn't say you must. If you cannot get > > > individual tables to be multi-tenant you are probably better off > having one > > > database per client on a shared cluster - at least given the size of > the > > > schema and number of clients. > > > > > I am working on a similar problem. > > 1 database per each client may be a killer when you have a connection > > pooler that creates a pool for a unique combination of (user,database). > > One of our clusters has well over 500 databases fronted by pg_bouncer. > > We get excellent connection "flattening" using pg_bouncer with > per-database connection spikes dealt with through a reserve pool. > What if you see at least 4 connections being established by each client during peak ? And if you serve 4 or 2 connections per each DB, then you are creating 1000 or more reserved connections with 500 DBs in a cluster. > > The nice thing about separate databases is that it is easy to scale > horizontally. > Agreed. But, how about autovacuum ? Workers shift from DB to DB and 500 clusters means you may have to have a lot of manual vacuuming in place as well. > > Rory > -- Regards, Avinash Vallarapu +1-902-221-5976
Re: AutoVacuum and growing transaction XID's
On Thu, May 7, 2020 at 1:33 PM Michael Lewis wrote: > It is trying to do a vacuum freeze. Do you have autovacuum turned off? Any > settings changed from default related to autovacuum? > > https://www.postgresql.org/docs/9.6/routine-vacuuming.html > Read 24.1.5. Preventing Transaction ID Wraparound Failures > > These may also be of help- > > https://info.crunchydata.com/blog/managing-transaction-id-wraparound-in-postgresql > https://www.2ndquadrant.com/en/blog/managing-freezing/ > > Note that you need to ensure the server gets caught up, or you risk being > locked out to prevent data corruption. > Thanks Mike. 1) We haven't changed anything related to autovacuum except a work_mem parameter which was increased to 4 GB which I believe is not related to autovacuum 2) The vacuum was not turned off and few parameters we had on vacuum are *autovacuum_analyze_scale_factor = 0.02* and *autovacuum_vacuum_scale_factor = 0.05* *3) *The database curently we are running is 2 years old for now and we have around close to 40 partitions and the datfrozenxid on the table is 343 million whereas the default is 200 million. I would try doing a manual auto vacuum on those tables where the autovacuum_freeze_max_age > 200 million. Do you think It's a right thing to do ?. I will also go through this documents. Tahnks
Re: Explain plan changes - IN CLAUSE ( Passing direct values Vs INNER Query )
On Thu, May 7, 2020 at 10:49 AM Amarendra Konda wrote: > Can you please explain, why it is getting more columns in output, even > though we have asked for only one column ? > > > > * Output: pa.process_activity_id, pa.process_activity_type, pa.voice_url, > pa.process_activity_user_id, pa.app_id, pa.process_instance_id, pa.alias, > pa.read_by_user, pa.source, pa.label_category_id, pa.label_id, > pa.csat_response_id, m.process_activity_fragments, pa.created, pa.updated, > pa.rule_id, pa.marketing_reply_id, pa.delivered_at, pa.reply_fragments, > pa.status_fragment, pa.internal_meta, pa.interaction_id, > pa.do_not_translate, pa.should_translate, pa.in_reply_to* > Not knowing the source code in this area at all... I'm pretty sure its because it doesn't matter. The executor retrieves data "pages", 8k blocks containing multiple records, then extracts specific full tuples from there. At that point its probably just data pointers being passed around. Its not until the end that the planner/executor has to decide which subset of columns to return to the user, or when a new tuple structure has to be created anyway (say because of joining), maybe, does it take the effort of constructing a minimally necessary output column set. David J.
Re: Explain plan changes - IN CLAUSE ( Passing direct values Vs INNER Query )
"David G. Johnston" writes: > On Thu, May 7, 2020 at 10:49 AM Amarendra Konda > wrote: >> Can you please explain, why it is getting more columns in output, even >> though we have asked for only one column ? >> * Output: pa.process_activity_id, pa.process_activity_type, pa.voice_url, >> pa.process_activity_user_id, pa.app_id, pa.process_instance_id, pa.alias, >> pa.read_by_user, pa.source, pa.label_category_id, pa.label_id, >> pa.csat_response_id, m.process_activity_fragments, pa.created, pa.updated, >> pa.rule_id, pa.marketing_reply_id, pa.delivered_at, pa.reply_fragments, >> pa.status_fragment, pa.internal_meta, pa.interaction_id, >> pa.do_not_translate, pa.should_translate, pa.in_reply_to* > Not knowing the source code in this area at all... > I'm pretty sure its because it doesn't matter. It's actually intentional, to save a projection step within that plan node. We'll discard the extra columns once it matters, at some higher plan level. (There have been some debates on -hackers about whether this optimization is still worth anything, given all the executor improvements that have been made since it went in. But it was clearly a win at the time.) regards, tom lane
Re: Explain plan changes - IN CLAUSE ( Passing direct values Vs INNER Query )
On Thu, May 7, 2020 at 11:07 AM Amarendra Konda wrote: > EXPLAIN (ANALYZE, COSTS, VERBOSE, BUFFERS) SELECT pa.process_activity_id > AS pa_process_activity_id FROM process_activity pa WHERE pa.app_id = > '126502930200650' AND pa.created > '1970-01-01 00:00:00' AND EXISTS ( > SELECT 1 FROM process_instance pi where pi.app_id = pa.app_id AND > *pi.process_instance_id > = pa.process_instance_id * AND pi.user_id = '137074931866340') ORDER BY > pa.process_instance_id, pa.created limit 50; > > > > >-> Index Scan using > process_activity_process_instance_id_app_id_created_idx on > public.process_activity pa (cost=0.70..1061.62 rows=1436 width=32) *(actual > time=0.011..20.320 rows=23506 loops=2)* > > Index Cond: ((m.process_instance_id = pi.process_instance_id) AND (m.app_id = '126502930200650'::bigint) AND (m.created > '1970-01-01 00:00:00'::timestamp without time zone)) I suppose during the nested loop the inner index scan could limit itself to the first 50 entries it finds (since the first two index columns are being held constant on each scan, m.created should define the traversal order...) so that the output of the nested loop ends up being (max 2 x 50) 100 entries which are then sorted and only the top 50 returned. Whether the executor could but isn't doing that here or isn't programmed to do that (or my logic is totally off) I do not know. David J.
Re: Explain plan changes - IN CLAUSE ( Passing direct values Vs INNER Query )
On Fri, 8 May 2020 at 10:00, David G. Johnston wrote: > > On Thu, May 7, 2020 at 11:07 AM Amarendra Konda wrote: >> >> EXPLAIN (ANALYZE, COSTS, VERBOSE, BUFFERS) SELECT pa.process_activity_id AS >> pa_process_activity_id FROM process_activity pa WHERE pa.app_id = >> '126502930200650' AND pa.created > '1970-01-01 00:00:00' AND EXISTS ( >> SELECT 1 FROM process_instance pi where pi.app_id = pa.app_id AND >> pi.process_instance_id = pa.process_instance_id AND pi.user_id = >> '137074931866340') ORDER BY pa.process_instance_id, pa.created limit 50; >> >> >>-> Index Scan using >> process_activity_process_instance_id_app_id_created_idx on >> public.process_activity pa (cost=0.70..1061.62 rows=1436 width=32) (actual >> time=0.011..20.320 rows=23506 loops=2) > > > Index Cond: ((m.process_instance_id = pi.process_instance_id) AND (m.app_id > > = '126502930200650'::bigint) AND (m.created > '1970-01-01 > > 00:00:00'::timestamp without time zone)) > > I suppose during the nested loop the inner index scan could limit itself to > the first 50 entries it finds (since the first two index columns are being > held constant on each scan, m.created should define the traversal order...) > so that the output of the nested loop ends up being (max 2 x 50) 100 entries > which are then sorted and only the top 50 returned. > > Whether the executor could but isn't doing that here or isn't programmed to > do that (or my logic is totally off) I do not know. I think the planner is likely not putting the process_activity table on the outer side of the nested loop join due to the poor row estimates. If it knew that so many rows would match the join then it likely would have done that to save from having to perform the sort at all. However, because the planner has put the process_instance on the outer side of the nested loop join, it's the pathkeys from that path that the nested loop node has, which is not the same as what the ORDER BY needs, so the planner must add a sort step, which means that all rows from the nested loop plan must be read so that they can be sorted. It might be worth trying: create index on process_instance (user_id,app_id); as that might lower the cost of performing the join in the opposite order and have the planner prefer that order instead. If doing that, the OP could then ditch the fki_conv_konotor_user_user_id index to save space. If that's not enough to convince the planner that the opposite order is better then certainly SET enable_sort TO off; would. David
Re: AutoVacuum and growing transaction XID's
On Thu, May 7, 2020 at 4:18 PM github kran wrote: > > > On Thu, May 7, 2020 at 1:33 PM Michael Lewis wrote: > >> It is trying to do a vacuum freeze. Do you have autovacuum turned off? >> Any settings changed from default related to autovacuum? >> >> https://www.postgresql.org/docs/9.6/routine-vacuuming.html >> Read 24.1.5. Preventing Transaction ID Wraparound Failures >> >> These may also be of help- >> >> https://info.crunchydata.com/blog/managing-transaction-id-wraparound-in-postgresql >> https://www.2ndquadrant.com/en/blog/managing-freezing/ >> >> Note that you need to ensure the server gets caught up, or you risk being >> locked out to prevent data corruption. >> > > Thanks Mike. > 1) We haven't changed anything related to autovacuum except a work_mem > parameter which was increased to 4 GB which I believe is not related to > autovacuum > 2) The vacuum was not turned off and few parameters we had on vacuum are > *autovacuum_analyze_scale_factor = 0.02* and > *autovacuum_vacuum_scale_factor > = 0.05* > *3) *The database curently we are running is 2 years old for now and we > have around close to 40 partitions and the *datfrozenxid on the table is > 343 million whereas the default is 200 million*. I would try doing a > manual auto vacuum on those tables > where the *autovacuum_freeze_max_age > 200 million*. Do you think It's a > right thing to do ?. > > I will also go through this documents. > * Few more things 5/7 - 8:40 PM CDT* 1) I see there are *8 Vacuum workers* ( Not sure what changed) running in the background and the concern I have is all of these vacuum processes are running with wrap around and while they are running I can't either DROP or ALTER any other tables ( REMOVE Inheritance for any of old tables where the WRITES are not getting written to).* Any of the ALTER TABLE OR DROP TABLE DDL's arer not getting exeucted even I WAITED FOR SEVERAL MINUTES , so I have terminated those queries as I didn't have luck.* 2) T*he VACUUM Process wrap around is running for last 1 day and several hrs on other tables. * 3) *Can I increase the autovacuum_freeze_max_age on the tables on production system* ? > > Thanks > > >
Re: AutoVacuum and growing transaction XID's
On Fri, 8 May 2020 at 09:18, github kran wrote: > 1) We haven't changed anything related to autovacuum except a work_mem > parameter which was increased to 4 GB which I believe is not related to > autovacuum It might want to look into increasing vacuum_cost_limit to something well above 200 or dropping autovacuum_vacuum_cost_delay down from 20 to something much lower. However, you say you've not changed the autovacuum settings, but you've also said: >1) I see there are 8 Vacuum workers ( Not sure what changed) running in > the background and the concern I have is all of these vacuum processes are > running with wrap around and while they are running The default is 3, so if you have 8 then the settings are non-standard. It might be good to supply the output of: SELECT name,setting from pg_Settings where name like '%vacuum%'; You should know that the default speed that autovacuum runs at is quite slow in 9.6. If you end up with all your autovacuum workers tied up with anti-wraparound vacuums then other tables are likely to get neglected and that could lead to stale stats or bloated tables. Best to aim to get auto-vacuum running faster or aim to perform some manual vacuums of tables that are over their max freeze age during an off-peak period to make use of the lower load during those times. Start with tables in pg_class with the largest age(relfrozenxid). You'll still likely want to look at the speed autovacuum runs at either way. Please be aware that the first time a new cluster crosses the autovacuum_freeze_max_age threshold can be a bit of a pain point as it can mean that many tables require auto-vacuum activity all at once. The impact of this is compounded if you have many tables that never receive an UPDATE/DELETE as auto-vacuum, in 9.6, does not visit those tables for any other reason. After the first time, the relfrozenxids of tables tend to be more staggered so their vacuum freeze requirements are also more staggered and that tends to cause fewer problems. David
Re: AutoVacuum and growing transaction XID's
On Fri, 8 May 2020 at 13:51, github kran wrote: > I can't either DROP or ALTER any other tables ( REMOVE Inheritance for > any of old tables where the WRITES are not getting written to). Any of the > ALTER TABLE OR DROP TABLE DDL's arer not getting exeucted even I WAITED FOR > SEVERAL MINUTES , so I have terminated those queries as I didn't have luck. The auto-vacuum freeze holds an SharedUpdateExclusiveLock on the table being vacuumed. If you try any DDL that requires an AccessExclusiveLock, it'll have to wait until the vacuum has completed. If you leave the DDL running then all accesses to the table will be queued behind the ungranted AccessExclusiveLock. It's likely a good idea to always run DDL with a fairly short lock_timeout, just in case this happens. >3) Can I increase the autovacuum_freeze_max_age on the tables on > production system ? Yes, but you cannot increase the per-table setting above the global setting. Changing the global setting requires a restart. David
Re: AutoVacuum and growing transaction XID's
Thanks David for your replies. On Thu, May 7, 2020 at 11:01 PM David Rowley wrote: > On Fri, 8 May 2020 at 09:18, github kran wrote: > > 1) We haven't changed anything related to autovacuum except a work_mem > parameter which was increased to 4 GB which I believe is not related to > autovacuum > > It might want to look into increasing vacuum_cost_limit to something > well above 200 or dropping autovacuum_vacuum_cost_delay down from 20 > to something much lower. However, you say you've not changed the > autovacuum settings, but you've also said: > > >1) I see there are 8 Vacuum workers ( Not sure what changed) running > in the background and the concern I have is all of these vacuum processes > are running with wrap around and while they are running > - Yes I said it was originally 3 but I noticed the work_mem parameter was changed few weeks back to 4 GB and then from that day onwards there is an increasing trend of the MaxUsedTransactionIds from 200 Million to 347 million ( It's growing day by day from last 2 -3 weeks) - Do you think there could be a formula on how the workers could have increased based on this increase in WORK_MEM controlled by database ?. > The default is 3, so if you have 8 then the settings are non-standard. > > It might be good to supply the output of: > > SELECT name,setting from pg_Settings where name like '%vacuum%'; > Output of vacuum name setting min_val max_val boot_val reset_val autovacuum on null null on on autovacuum_analyze_scale_factor 0.02 0 100 0.1 0.02 autovacuum_analyze_threshold 50 0 2147483647 50 50 autovacuum_freeze_max_age 2 10 20 2 2 autovacuum_max_workers 8 1 262143 3 8 autovacuum_multixact_freeze_max_age 4 1 20 4 4 autovacuum_naptime 5 1 2147483 60 5 autovacuum_vacuum_cost_delay 5 -1 100 20 5 autovacuum_vacuum_cost_limit -1 -1 1 -1 -1 autovacuum_vacuum_scale_factor 0.05 0 100 0.2 0.05 autovacuum_vacuum_threshold 50 0 2147483647 50 50 autovacuum_work_mem -1 -1 2147483647 -1 -1 > > You should know that the default speed that autovacuum runs at is > quite slow in 9.6. If you end up with all your autovacuum workers tied > up with anti-wraparound vacuums then other tables are likely to get > neglected and that could lead to stale stats or bloated tables. Best > to aim to get auto-vacuum running faster or aim to perform some manual > vacuums of tables that are over their max freeze age during an > off-peak period to make use of the lower load during those times. > Start with tables in pg_class with the largest age(relfrozenxid). > You'll still likely want to look at the speed autovacuum runs at > either way. > > Please be aware that the first time a new cluster crosses the > autovacuum_freeze_max_age threshold can be a bit of a pain point as it > can mean that many tables require auto-vacuum activity all at once. > The impact of this is compounded if you have many tables that never > receive an UPDATE/DELETE as auto-vacuum, in 9.6, does not visit those > tables for any other reason. After the first time, the relfrozenxids > of tables tend to be more staggered so their vacuum freeze > requirements are also more staggered and that tends to cause fewer > problems. > The current situation I have is the auto vacuum kicked with 8 tables with each of those tied to each worker and it's running very slow in 9.6 as you mentioned i observed VACUUM on those 8 tables is running from last 15 hrs and other process are running for 1 hr+ and others for few minutes for different tables. Finally I would wait for your reply to see what could be done for this VACUUM and growing TXIDs values. -Do you think I should consider changing back the work_mem back to 4 MB what it was originally ? - Can I apply your recommendations on a production instance directly or you prefer me to apply initially in other environment before applying on Prod ? - Also like I said I want to clean up few unused tables OR MANUAL VACUUM but current system doesn't allow me to do it considering these factors. - I will try to run VACUUM Manually during off peak hrs , Can I STOP the Manual VACUUM process if its take more than 10 minutes or what is the allowed time in mins I can have it running ?. David >
Re: AutoVacuum and growing transaction XID's
On Thu, May 7, 2020 at 11:04 PM David Rowley wrote: > On Fri, 8 May 2020 at 13:51, github kran wrote: > > I can't either DROP or ALTER any other tables ( REMOVE Inheritance > for any of old tables where the WRITES are not getting written to). Any of > the ALTER TABLE OR DROP TABLE DDL's arer not getting exeucted even I > WAITED FOR SEVERAL MINUTES , so I have terminated those queries as I didn't > have luck. > > The auto-vacuum freeze holds an SharedUpdateExclusiveLock on the table > being vacuumed. If you try any DDL that requires an > AccessExclusiveLock, it'll have to wait until the vacuum has > completed. If you leave the DDL running then all accesses to the table > will be queued behind the ungranted AccessExclusiveLock. It's likely > a good idea to always run DDL with a fairly short lock_timeout, just > in case this happens. > * How much value I can assign to lock_timeout so that I dont get into trouble to test my DDL commands and without impacting other sessions.* > > >3) Can I increase the autovacuum_freeze_max_age on the tables on > production system ? > > > Yes, but you cannot increase the per-table setting above the global > setting. Changing the global setting requires a restart. > >How can I change the value of the global setting of the autovacuum_freeze_max_Age value. > David >
Re: pg_attribute, pg_class, pg_depend grow huge in count and size with multiple tenants.
On Thu, 2020-05-07 at 18:17 -0300, Avinash Kumar wrote: > > The nice thing about separate databases is that it is easy to scale > > horizontally. > > Agreed. But, how about autovacuum ? Workers shift from DB to DB and 500 > clusters > means you may have to have a lot of manual vacuuming in place as well. Just set "autovacuum_max_workers" higher. Yours, Laurenz Albe -- Cybertec | https://www.cybertec-postgresql.com
Re: pg_attribute, pg_class, pg_depend grow huge in count and size with multiple tenants.
Hi, On Fri, May 8, 2020 at 3:31 AM Laurenz Albe wrote: > On Thu, 2020-05-07 at 18:17 -0300, Avinash Kumar wrote: > > > The nice thing about separate databases is that it is easy to scale > > > horizontally. > > > > Agreed. But, how about autovacuum ? Workers shift from DB to DB and 500 > clusters > > means you may have to have a lot of manual vacuuming in place as well. > > Just set "autovacuum_max_workers" higher. > No, that wouldn't help. If you just increase autovacuum_max_workers, the total cost limit of autovacuum_vacuum_cost_limit (or vacuum_cost_limit) is shared by so many workers and it further delays autovacuum per each worker. Instead you need to increase autovacuum_vacuum_cost_limit as well when you increase the number of workers. But, if you do that and also increase workers, well, you would easily reach the limitations of the disk. I am not sure it is anywhere advised to have 20 autovacuum_max_workers unless i have a disk with lots of IOPS and with very tiny tables across all the databases. > > Yours, > Laurenz Albe > -- > Cybertec | https://www.cybertec-postgresql.com > > -- Regards, Avinash Vallarapu
Re: pg_attribute, pg_class, pg_depend grow huge in count and size with multiple tenants.
On Fri, 2020-05-08 at 03:47 -0300, Avinash Kumar wrote: > > Just set "autovacuum_max_workers" higher. > > No, that wouldn't help. If you just increase autovacuum_max_workers, the > total cost limit of > autovacuum_vacuum_cost_limit (or vacuum_cost_limit) is shared by so many > workers and it > further delays autovacuum per each worker. Instead you need to increase > autovacuum_vacuum_cost_limit > as well when you increase the number of workers. True, I should have mentioned that. > But, if you do that and also increase workers, well, you would easily reach > the limitations > of the disk. I am not sure it is anywhere advised to have 20 > autovacuum_max_workers unless > i have a disk with lots of IOPS and with very tiny tables across all the > databases. Sure, if you have a high database load, you will at some point exceed the limits of the machine, which is not surprising. What I am trying to say is that you have to ramp up the resources for autovacuum together with increasing the overall workload. You should consider autovacuum as part of that workload. If your machine cannot cope with the workload any more, you have to scale, which is easily done by adding more machines if you have many databases. Yours, Laurenz Albe -- Cybertec | https://www.cybertec-postgresql.com
