Can I add Index to make a query faster which involves joins on unnest ?

2018-09-13 Thread Arup Rakshit 
The below query basically gives the result by maintaining the order of the 
sizes in the list.

explain analyze select
"price_levels"."name",
"price_levels"."size"
from
"price_levels"
join unnest(array['M',
'L',
'XL',
'2XL',
'3XL',
'4XL',
'5XL',
'6XL',
'S']) with ordinality t(size,
ord)
using (size)
order by
t.size


I have a Btree index on the size column.

Explain output is:

Merge Join  (cost=4.61..5165.38 rows=6 width=46) (actual time=0.157..57.872 
rows=6 loops=1)
  Merge Cond: ((price_levels.size)::text = t.size)
  ->  Index Scan using price_levels_size_idx on price_levels  
(cost=0.29..4111.05 rows=6 width=14) (actual time=0.044..25.941 rows=6 
loops=1)
  ->  Sort  (cost=4.32..4.57 rows=100 width=32) (actual time=0.108..3.946 
rows=53289 loops=1)
Sort Key: t.size
Sort Method: quicksort  Memory: 25kB
->  Function Scan on unnest t  (cost=0.00..1.00 rows=100 width=32) 
(actual time=0.030..0.033 rows=9 loops=1)
Planning time: 0.667 ms
Execution time: 62.846 ms



Thanks,

Arup Rakshit
a...@zeit.io

Re: Can I add Index to make a query faster which involves joins on unnest ?

2018-09-13 Thread Rob Sargent 


> On Sep 13, 2018, at 12:17 PM, Arup Rakshit  wrote:
> 
> The below query basically gives the result by maintaining the order of the 
> sizes in the list.
> 
> explain analyze select
> "price_levels"."name",
> "price_levels"."size"
> from
> "price_levels"
> join unnest(array['M',
> 'L',
> 'XL',
> '2XL',
> '3XL',
> '4XL',
> '5XL',
> '6XL',
> 'S']) with ordinality t(size,
> ord)
> using (size)
> order by
> t.size
> 
> 
> I have a Btree index on the size column.
> 
> Explain output is:
> 
> Merge Join  (cost=4.61..5165.38 rows=6 width=46) (actual 
> time=0.157..57.872 rows=6 loops=1)
>   Merge Cond: ((price_levels.size)::text = t.size)
>   ->  Index Scan using price_levels_size_idx on price_levels  
> (cost=0.29..4111.05 rows=6 width=14) (actual time=0.044..25.941 
> rows=6 loops=1)
>   ->  Sort  (cost=4.32..4.57 rows=100 width=32) (actual time=0.108..3.946 
> rows=53289 loops=1)
> Sort Key: t.size
> Sort Method: quicksort  Memory: 25kB
> ->  Function Scan on unnest t  (cost=0.00..1.00 rows=100 width=32) 
> (actual time=0.030..0.033 rows=9 loops=1)
> Planning time: 0.667 ms
> Execution time: 62.846 ms
> 
> 
> 
> Thanks,
> 
> Arup Rakshit
> a...@zeit.io 
> 
> 
There are not value of size fit it to be a worthwhile key.
>

Estimate time without running the query

2018-09-13 Thread Neto pr 
Dear all,
Only a doubt.
The Explain  command only estimates the cost of execution of a
query, and does not estimate time for execution.
I would like know if exists  some way to estimate the time, without running
the query?

Best Regards
[]`s Neto

RE: Estimate time without running the query

2018-09-13 Thread Johnes Castro 
Hi netoprbr,

Use a command explain analyse.

Best Regards.
Johnes Castro

De: Neto pr 
Enviado: quinta-feira, 13 de setembro de 2018 19:38
Para: PostgreSQL General
Assunto: Estimate time without running the query

Dear all,
Only a doubt.
The Explain  command only estimates the cost of execution of a query, 
and does not estimate time for execution.
I would like know if exists  some way to estimate the time, without running the 
query?

Best Regards
[]`s Neto

Re: [External] RE: Estimate time without running the query

2018-09-13 Thread Vijaykumar Jain 
explain analyze would *run* the query and it can be dangerous if it is a DML 
statement like insert/update/delete 😊

If you still want to go with explain analyze,
You can do

begin;
explain analyze ;
rollback;

thanks,
Vijay

From: Johnes Castro 
Date: Friday, September 14, 2018 at 3:12 AM
To: Neto pr , PostgreSQL General 

Subject: [External] RE: Estimate time without running the query

Hi netoprbr,

Use a command explain analyse.

Best Regards.
Johnes Castro

De: Neto pr 
Enviado: quinta-feira, 13 de setembro de 2018 19:38
Para: PostgreSQL General
Assunto: Estimate time without running the query

Dear all,
Only a doubt.
The Explain  command only estimates the cost of execution of a query, 
and does not estimate time for execution.
I would like know if exists  some way to estimate the time, without running the 
query?
Best Regards
[]`s Neto

Slow shutdowns sometimes on RDS Postgres

2018-09-13 Thread Chris Williams 
Hi,

I'm using AWS RDS Postgres (9.6.6) and have run into very slow shutdowns
(10+ minutes) a few times when making database modifications (e.g. reboot,
changing instance size, etc.).  Other times, it shuts down quickly (1
minute or so).  I have not been able to figure out why sometimes it takes a
long time to shutdown.

When it happens, I see a bunch of lines in the postgres log like the
following over and over (almost every second or two) during this 10 minute
shutdown period:
2018-09-12 06:37:01 UTC:XXX.XXX.XXX.XXX(19712):my_user@my_db
:[16495]:FATAL:
2018-09-12 06:37:01 UTC:localhost(31368):rdsadmin@rdsadmin:[16488]:FATAL:
the database system is shutting down

Once I start seeing these messages, I start manually shutting down all of
our applications that are connected to the db.  I'm not sure if shutting
down the apps fixes it or if there's some timeout on the RDS side, but it
seems like once I start doing this, the database finally shuts down.

When it takes this long to shut down, it ends up causing a lot more
downtime than I would like.  I've tried asking AWS's support why it takes
so long to shutdown sometimes, but they basically just told me that's "how
it works" and that I should try to shut down all of my connections ahead of
time before making database modifications.

We just have a few ruby on rails applications connected to the database,
and don't really have any long running or heavy queries and the db is under
very light load, so I don't understand why it takes so long to shutdown.
We do have a sizeable number of connections though (about 600) and there
are two replicas connected to it.  I also tried
setting idle_in_transaction_session_timeout to 300 seconds to see if that
would help, but it made no difference.

I was wondering if anyone else had seen this behavior on their RDS Postgres
instances or had any suggestions on how I could shorten the shutdown time?

Thanks,
Chris

Re: Slow shutdowns sometimes on RDS Postgres

2018-09-13 Thread Joshua D. Drake 

On 09/13/2018 03:04 PM, Chris Williams wrote:

Hi,

I'm using AWS RDS Postgres (9.6.6) and have run into very slow 
shutdowns (10+ minutes) a few times when making database modifications 
(e.g. reboot, changing instance size, etc.).  Other times, it shuts 
down quickly (1 minute or so).  I have not been able to figure out why 
sometimes it takes a long time to shutdown.


This is probably something you would have to talk to Amazon about. AWS 
RDS Postgres is a fork of PostgreSQL and not 100% compatible from an 
administrative perspective.


JD





When it happens, I see a bunch of lines in the postgres log like the 
following over and over (almost every second or two) during this 10 
minute shutdown period:
2018-09-12 06:37:01 
UTC:XXX.XXX.XXX.XXX(19712):my_user@my_db:[16495]:FATAL:
2018-09-12 06:37:01 
UTC:localhost(31368):rdsadmin@rdsadmin:[16488]:FATAL: the database 
system is shutting down


Once I start seeing these messages, I start manually shutting down all 
of our applications that are connected to the db.  I'm not sure if 
shutting down the apps fixes it or if there's some timeout on the RDS 
side, but it seems like once I start doing this, the database finally 
shuts down.


When it takes this long to shut down, it ends up causing a lot more 
downtime than I would like.  I've tried asking AWS's support why it 
takes so long to shutdown sometimes, but they basically just told me 
that's "how it works" and that I should try to shut down all of my 
connections ahead of time before making database modifications.


We just have a few ruby on rails applications connected to the 
database, and don't really have any long running or heavy queries and 
the db is under very light load, so I don't understand why it takes so 
long to shutdown.  We do have a sizeable number of connections though 
(about 600) and there are two replicas connected to it.  I also tried 
setting idle_in_transaction_session_timeout to 300 seconds to see if 
that would help, but it made no difference.


I was wondering if anyone else had seen this behavior on their RDS 
Postgres instances or had any suggestions on how I could shorten the 
shutdown time?


Thanks,
Chris



--
Command Prompt, Inc. || http://the.postgres.company/ || @cmdpromptinc
***  A fault and talent of mine is to tell it exactly how it is.  ***
PostgreSQL centered full stack support, consulting and development.
Advocate: @amplifypostgres || Learn: https://postgresconf.org
* Unless otherwise stated, opinions are my own.   *

Re: [External] RE: Estimate time without running the query

2018-09-13 Thread Neto pr 
Em qui, 13 de set de 2018 às 18:49, Vijaykumar Jain 
escreveu:

> explain analyze would *run* the query and it can be dangerous if it is a
> DML statement like insert/update/delete 😊
>
>
>
> If you still want to go with explain analyze,
>
> You can do
>
>
>
> begin;
>
> explain analyze ;
>
> rollback;
>
>
>

Dear all,

The problem is that using the explain analyze  I have to wait for
the query to execute.
I would like to estimate the time without having to wait for the query
execution.
Does anyone know how to estimate the time without waiting for the query to
be executed?

Best regards
Neto


> thanks,
>
> Vijay
>
>
>
> *From: *Johnes Castro 
> *Date: *Friday, September 14, 2018 at 3:12 AM
> *To: *Neto pr , PostgreSQL General <
> pgsql-gene...@postgresql.org>
> *Subject: *[External] RE: Estimate time without running the query
>
>
>
> Hi netoprbr,
>
>
>
> Use a command explain analyse.
>
>
>
> Best Regards.
>
> Johnes Castro
> --
>
> *De:* Neto pr 
> *Enviado:* quinta-feira, 13 de setembro de 2018 19:38
> *Para:* PostgreSQL General
> *Assunto:* Estimate time without running the query
>
>
>
> Dear all,
> Only a doubt.
> The Explain  command only estimates the cost of execution of a
> query, and does not estimate time for execution.
> I would like know if exists  some way to estimate the time, without
> running the query?
>
> Best Regards
>
> []`s Neto
>

Behaviour when autovacuum is canceled

2018-09-13 Thread Martín Fernández 
Hello,

I'm working on a high volume transaction database and we are starting to tune 
our autovacuum setting to improve our vacuuming performance.

Once thing that we know about autovacuum is that is can be automatically 
canceled if a dependent transaction is blocked by the autovacuum transaction. 
In a situation like this one, autovacuum would be canceled. 

My question is, what will happen to the work that autovacuum has achieved 
before being canceled ? Is that work lost ? I tried to look for the answer in 
the code and I'm not completely sure what will happen. From what I could 
understand (that can be totally wrong), the vacuum process is split in multiple 
small transactions. If the autovacuum is canceled, could it be possible that 
only the latest transaction work be lost ?

Sorry if my understanding is not the correct one, understanding how to tune the 
autovacuum seems really complicated to me.

Thanks before hand! 

Martín

Re: [External] RE: Estimate time without running the query

2018-09-13 Thread David G. Johnston 
On Thu, Sep 13, 2018 at 3:30 PM, Neto pr  wrote:

> The problem is that using the explain analyze  I have to wait for
> the query to execute.
> I would like to estimate the time without having to wait for the query
> execution.
> Does anyone know how to estimate the time without waiting for the query to
> be executed?
>

On the machine in question you have to experiment to obtain data to
construct a formula to convert cost to time.  Then when using the function
remember that lots of things can play into individual executions taking
more time (and sometimes less too I suspect) such as locks, caching,
physical data locality.

It seems more useful to log actual execution times and look for trends.  If
you are writing a query odds are it needs to be run regardless of how
efficient it may be - or used in a relative comparison to an alternate
query.

David J.

Re: Behaviour when autovacuum is canceled

2018-09-13 Thread David G. Johnston 
On Thu, Sep 13, 2018 at 3:45 PM, Martín Fernández 
wrote:

> From what I could understand (that can be totally wrong), the vacuum
> process is split in multiple small transactions. If the autovacuum is
> canceled, could it be possible that only the latest transaction work be
> lost
>

>From the docs:

"VACUUM cannot be executed inside a transaction block."

As it is non-transactional any work it performs is live immediately and
irrevocably as it occurs.

David J.

Re: Slow shutdowns sometimes on RDS Postgres

2018-09-13 Thread Chris Williams 
Yeah, I figured that.  Unfortunately, every time it happens, I open a
support ticket with them, but they always just tell me that this is normal
behavior for postgres.  Whether it's "normal" or not, I really would like
to get my db to shut down faster, and their suggestion of manually shutting
down all of my apps ahead of time is a real headache.  Given that I haven't
gotten anywhere with their support, I figured I'd try asking on the mailing
list.

Thanks,
Chris

On Thu, Sep 13, 2018 at 3:17 PM Joshua D. Drake 
wrote:

> On 09/13/2018 03:04 PM, Chris Williams wrote:
> > Hi,
> >
> > I'm using AWS RDS Postgres (9.6.6) and have run into very slow
> > shutdowns (10+ minutes) a few times when making database modifications
> > (e.g. reboot, changing instance size, etc.).  Other times, it shuts
> > down quickly (1 minute or so).  I have not been able to figure out why
> > sometimes it takes a long time to shutdown.
>
> This is probably something you would have to talk to Amazon about. AWS
> RDS Postgres is a fork of PostgreSQL and not 100% compatible from an
> administrative perspective.
>
> JD
>
>
>
> >
> > When it happens, I see a bunch of lines in the postgres log like the
> > following over and over (almost every second or two) during this 10
> > minute shutdown period:
> > 2018-09-12 06:37:01
> > UTC:XXX.XXX.XXX.XXX(19712):my_user@my_db:[16495]:FATAL:
> > 2018-09-12 06:37:01
> > UTC:localhost(31368):rdsadmin@rdsadmin:[16488]:FATAL: the database
> > system is shutting down
> >
> > Once I start seeing these messages, I start manually shutting down all
> > of our applications that are connected to the db.  I'm not sure if
> > shutting down the apps fixes it or if there's some timeout on the RDS
> > side, but it seems like once I start doing this, the database finally
> > shuts down.
> >
> > When it takes this long to shut down, it ends up causing a lot more
> > downtime than I would like.  I've tried asking AWS's support why it
> > takes so long to shutdown sometimes, but they basically just told me
> > that's "how it works" and that I should try to shut down all of my
> > connections ahead of time before making database modifications.
> >
> > We just have a few ruby on rails applications connected to the
> > database, and don't really have any long running or heavy queries and
> > the db is under very light load, so I don't understand why it takes so
> > long to shutdown.  We do have a sizeable number of connections though
> > (about 600) and there are two replicas connected to it.  I also tried
> > setting idle_in_transaction_session_timeout to 300 seconds to see if
> > that would help, but it made no difference.
> >
> > I was wondering if anyone else had seen this behavior on their RDS
> > Postgres instances or had any suggestions on how I could shorten the
> > shutdown time?
> >
> > Thanks,
> > Chris
> >
>
> --
> Command Prompt, Inc. || http://the.postgres.company/ || @cmdpromptinc
> ***  A fault and talent of mine is to tell it exactly how it is.  ***
> PostgreSQL centered full stack support, consulting and development.
> Advocate: @amplifypostgres || Learn: https://postgresconf.org
> * Unless otherwise stated, opinions are my own.   *
>
>

Re: Behaviour when autovacuum is canceled

2018-09-13 Thread Martín Fernández 
David,

Thanks a lot for the quick reply. 

I clearly misunderstood the references in the code. 

Best,

Martín

On Thu, Sep 13th, 2018 at 7:55 PM, "David G. Johnston" 
 wrote:

> 
> 
> On Thu, Sep 13, 2018 at 3:45 PM, Martín Fernández < fmarti...@gmail.com > 
> wrote:
> 
> 
>> From what I could understand (that can be totally wrong), the vacuum
>> process is split in multiple small transactions. If the autovacuum is
>> canceled, could it be possible that only the latest transaction work be
>> lost 
>> 
> 
> 
> 
> From the docs:
> 
> 
> "VACUUM cannot be executed inside a transaction block."
> 
> 
> As it is non-transactional any work it performs is live immediately and
> irrevocably as it occurs.
> 
> 
> David J.
> 
> 
>

Re: Slow shutdowns sometimes on RDS Postgres

2018-09-13 Thread Adrian Klaver 

On 9/13/18 3:59 PM, Chris Williams wrote:
Yeah, I figured that.  Unfortunately, every time it happens, I open a 
support ticket with them, but they always just tell me that this is 
normal behavior for postgres.  Whether it's "normal" or not, I really 
would like to get my db to shut down faster, and their suggestion of 
manually shutting down all of my apps ahead of time is a real headache.  
Given that I haven't gotten anywhere with their support, I figured I'd 
try asking on the mailing list.


The thing is, what you are doing ("(e.g. reboot, changing instance size, 
etc.)") are instance operations not database operations. That comes 
under AWS's purview. For what it is worth the behavior is documented:


https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/USER_RebootInstance.html

"The time required to reboot your DB instance depends on the crash 
recovery process of your specific database engine. To improve the reboot 
time, we recommend that you reduce database activity as much as possible 
during the reboot process. Reducing database activity reduces rollback 
activity for in-transit transactions.

"

Pretty sure shutting down those 600 connections and uncoupling the 
replication(s) goes a long way to the time elapsed.




Thanks,
Chris

On Thu, Sep 13, 2018 at 3:17 PM Joshua D. Drake > wrote:


On 09/13/2018 03:04 PM, Chris Williams wrote:
 > Hi,
 >
 > I'm using AWS RDS Postgres (9.6.6) and have run into very slow
 > shutdowns (10+ minutes) a few times when making database
modifications
 > (e.g. reboot, changing instance size, etc.).  Other times, it shuts
 > down quickly (1 minute or so).  I have not been able to figure
out why
 > sometimes it takes a long time to shutdown.

This is probably something you would have to talk to Amazon about. AWS
RDS Postgres is a fork of PostgreSQL and not 100% compatible from an
administrative perspective.

JD



 >
 > When it happens, I see a bunch of lines in the postgres log like the
 > following over and over (almost every second or two) during this 10
 > minute shutdown period:
 > 2018-09-12 06:37:01
 > UTC:XXX.XXX.XXX.XXX(19712):my_user@my_db:[16495]:FATAL:
 > 2018-09-12 06:37:01
 > UTC:localhost(31368):rdsadmin@rdsadmin:[16488]:FATAL: the database
 > system is shutting down
 >
 > Once I start seeing these messages, I start manually shutting
down all
 > of our applications that are connected to the db.  I'm not sure if
 > shutting down the apps fixes it or if there's some timeout on the
RDS
 > side, but it seems like once I start doing this, the database
finally
 > shuts down.
 >
 > When it takes this long to shut down, it ends up causing a lot more
 > downtime than I would like.  I've tried asking AWS's support why it
 > takes so long to shutdown sometimes, but they basically just told me
 > that's "how it works" and that I should try to shut down all of my
 > connections ahead of time before making database modifications.
 >
 > We just have a few ruby on rails applications connected to the
 > database, and don't really have any long running or heavy queries
and
 > the db is under very light load, so I don't understand why it
takes so
 > long to shutdown.  We do have a sizeable number of connections
though
 > (about 600) and there are two replicas connected to it.  I also
tried
 > setting idle_in_transaction_session_timeout to 300 seconds to see if
 > that would help, but it made no difference.
 >
 > I was wondering if anyone else had seen this behavior on their RDS
 > Postgres instances or had any suggestions on how I could shorten the
 > shutdown time?
 >
 > Thanks,
 > Chris
 >

-- 
Command Prompt, Inc. || http://the.postgres.company/ || @cmdpromptinc

***  A fault and talent of mine is to tell it exactly how it is.  ***
PostgreSQL centered full stack support, consulting and development.
Advocate: @amplifypostgres || Learn: https://postgresconf.org
*     Unless otherwise stated, opinions are my own.   *




--
Adrian Klaver
adrian.kla...@aklaver.com

Re: Behaviour when autovacuum is canceled

2018-09-13 Thread Martín Fernández 
David,

Your last comment applies for cleaning up indexes as well ? We performed a 
simple test in our production database to understand behaviour and we got a 
result that surprised us based on your last comment.

We basically started a VACUUM on a given table, waited for one index to process 
(captured cleaned rows count) and cancel the VACUUM. When we run another VACUUM 
on the same table the dead rows removed from the first index was a number 
slightly higher than the value logged on the first VACUUM. This behaviour made 
us feel that the work done to clean dead tuples on the first index was 
performed again. 

Thanks!

Martín

On Thu, Sep 13th, 2018 at 8:0 PM, "Martín Fernández"  
wrote:

> 
> David,
> 
> 
> Thanks a lot for the quick reply. 
> 
> 
> I clearly misunderstood the references in the code. 
> 
> 
> Best,
> 
> Martín
> 
> 
> On Thu, Sep 13th, 2018 at 7:55 PM, "David G. Johnston" < 
> david.g.johns...@gmail.com
> > wrote:
> 
> 
>> 
>> On Thu, Sep 13, 2018 at 3:45 PM, Martín Fernández < fmarti...@gmail.com > 
>> wrote:
>> 
>> 
>>> From what I could understand (that can be totally wrong), the vacuum
>>> process is split in multiple small transactions. If the autovacuum is
>>> canceled, could it be possible that only the latest transaction work be
>>> lost 
>>> 
>> 
>> 
>> 
>> From the docs:
>> 
>> 
>> "VACUUM cannot be executed inside a transaction block."
>> 
>> 
>> As it is non-transactional any work it performs is live immediately and
>> irrevocably as it occurs.
>> 
>> 
>> David J.
>> 
>> 
>> 
> 
> 
> 
>

Re: [External] RE: Estimate time without running the query

2018-09-13 Thread Neto pr 
Em qui, 13 de set de 2018 às 19:53, David G. Johnston <
david.g.johns...@gmail.com> escreveu:

> On Thu, Sep 13, 2018 at 3:30 PM, Neto pr  wrote:
>
>> The problem is that using the explain analyze  I have to wait for
>> the query to execute.
>> I would like to estimate the time without having to wait for the query
>> execution.
>> Does anyone know how to estimate the time without waiting for the query
>> to be executed?
>>
>
> On the machine in question you have to experiment to obtain data to
> construct a formula to convert cost to time.  Then when using the function
> remember that lots of things can play into individual executions taking
> more time (and sometimes less too I suspect) such as locks, caching,
> physical data locality.
>
> It seems more useful to log actual execution times and look for trends.
> If you are writing a query odds are it needs to be run regardless of how
> efficient it may be - or used in a relative comparison to an alternate
> query.
>
>
Okay, David, but does not it have some SQL statement that returns a time
estimate, without having to execute the query?



> David J.
>
>

Re: Behaviour when autovacuum is canceled

2018-09-13 Thread Tom Lane 
=?UTF-8?q?Mart=C3=ADn_Fern=C3=A1ndez?=  writes:
> We basically started a VACUUM on a given table, waited for one index to 
> process (captured cleaned rows count) and cancel the VACUUM. When we run 
> another VACUUM on the same table the dead rows removed from the first index 
> was a number slightly higher than the value logged on the first VACUUM. This 
> behaviour made us feel that the work done to clean dead tuples on the first 
> index was performed again. 

The unit of work that doesn't have to be repeated if VACUUM is canceled
is:

1. Scan a bunch of heap pages to identify dead tuples;
2. Scan *all* the table's indexes to remove the corresponding index entries;
3. Rescan those heap pages to actually remove the tuples.

It sounds like you canceled partway through phase 2.

The actual size of this unit of work is the number of dead-tuple TIDs
that will fit in maintenance_work_mem (at six or eight bytes apiece,
I forget whether it's aligned...).  Normally, people make
maintenance_work_mem big so that they can reduce the number of index
scan cycles needed to complete vacuuming a table.  But if you're
concerned about reducing the amount of work lost to a cancel,
you might try *reducing* maintenance_work_mem.  This will make
vacuum slower overall (more index scans), but you have a better
chance that it will manage to actually remove some tuples before
getting canceled.

Or you could look at fixing the access patterns that are causing
so many autovacuum cancels.

regards, tom lane

Re: Behaviour when autovacuum is canceled

2018-09-13 Thread Martín Fernández 
Tom,

Thanks for the detailed explanation. I can start mapping your explanation with 
the source code I've been reading :)

We are in the process of tuning our autovacuum settings (on some tables) and 
stop relying on crontabs that are performing manual vacuums. 

By performing this changes we are going to start relying more heavily on the 
autovacuum work and the concern of "lost work" caused by autovacuum canceling 
itself when locking contention happen showed up. I'm guessing that we might be 
over thinking this and the canceling is not going to happen as frequently as we 
think it will.

Martín

On Thu, Sep 13th, 2018 at 9:21 PM, Tom Lane  wrote:

> 
> 
> 
> =?UTF-8?q?Mart=C3=ADn_Fern=C3=A1ndez?= < fmarti...@gmail.com > writes:
> > We basically started a VACUUM on a given table, waited for one index to
> process (captured cleaned rows count) and cancel the VACUUM. When we run
> another VACUUM on the same table the dead rows removed from the first
> index was a number slightly higher than the value logged on the first
> VACUUM. This behaviour made us feel that the work done to clean dead
> tuples on the first index was performed again. 
> 
> The unit of work that doesn't have to be repeated if VACUUM is canceled
> is:
> 
> 1. Scan a bunch of heap pages to identify dead tuples;
> 2. Scan *all* the table's indexes to remove the corresponding index
> entries;
> 3. Rescan those heap pages to actually remove the tuples.
> 
> It sounds like you canceled partway through phase 2.
> 
> The actual size of this unit of work is the number of dead-tuple TIDs
> that will fit in maintenance_work_mem (at six or eight bytes apiece,
> I forget whether it's aligned...). Normally, people make
> maintenance_work_mem big so that they can reduce the number of index
> scan cycles needed to complete vacuuming a table. But if you're
> concerned about reducing the amount of work lost to a cancel,
> you might try *reducing* maintenance_work_mem. This will make
> vacuum slower overall (more index scans), but you have a better
> chance that it will manage to actually remove some tuples before
> getting canceled.
> 
> Or you could look at fixing the access patterns that are causing
> so many autovacuum cancels.
> 
> regards, tom lane
> 
> 
> 
>