Re: VACUUM vs VACUUM FULL (was: SELECT * FROM huge_table LIMIT 10; Why does it take more than 10 min to complete, with cold caches)

Christophe Pettus writes: >> On Feb 1, 2023, at 10:45, David G. Johnston >> wrote: >> The system just isn't that intelligent for "sequential scan", instead it >> does literally what the label says, goes through the table one page at a >> time and returns any live rows it finds. > Although th

Re: VACUUM vs VACUUM FULL (was: SELECT * FROM huge_table LIMIT 10; Why does it take more than 10 min to complete, with cold caches)

> On Feb 1, 2023, at 10:45, David G. Johnston > wrote: > The system just isn't that intelligent for "sequential scan", instead it does > literally what the label says, goes through the table one page at a time and > returns any live rows it finds. Although this does raise a question: Could

Re: VACUUM vs VACUUM FULL (was: SELECT * FROM huge_table LIMIT 10; Why does it take more than 10 min to complete, with cold caches)

On Wed, Feb 1, 2023 at 11:27 AM Dimitrios Apostolou wrote: > I have now run simple VACUUM but it didn't change anything, the simple > SELECT is still slow. > > My understanding by reading the docs is that it should reclaim all unused > space, just not return it to the OS. Which is fine by me. Any

VACUUM vs VACUUM FULL (was: SELECT * FROM huge_table LIMIT 10; Why does it take more than 10 min to complete, with cold caches)

Thanks everyone for the help. I have now run simple VACUUM but it didn't change anything, the simple SELECT is still slow. My understanding by reading the docs is that it should reclaim all unused space, just not return it to the OS. Which is fine by me. Any idea why it failed to reclaim the spa

Re: vacuum vs vacuum full

On 11/18/20 6:02 AM, Laurenz Albe wrote: On Wed, 2020-11-18 at 10:57 +0100, Thomas Kellerer wrote: No matter how long it takes, this is an excellent argument for partitioning Very Large Tables: many maintenance tasks are made *much* easier. The problem is, you can't partition every table as lon

Re: vacuum vs vacuum full

On Wed, Nov 18, 2020 at 1:33 AM Atul Kumar wrote: > Hi, > > We have a table of 3113GB, and we are planning to vacuum it in non > Just making sure that isn't a typo (repeated 1s)... business hours i.e. 12AM to 4AM, So my queries are: > > 1. What should be perform on the table Vacuum or Vacuum fu

Re: vacuum vs vacuum full

Hi Ravi, > On 18. Nov, 2020, at 15:30, Ravi Krishna wrote: > > ALTER TABLE TABLE_NAME DROP PARTITION PARTITION_NAME UPDATE INDEXES; IIRC the statement is alter table drop partition update *GLOBAL* indexes; But we experienced big problems in the past which is why we changed all to local ind

Re: vacuum vs vacuum full

> > Experience shows that global index in Oracle lead to problems when dropping a > partition. rebuilding an index, or other such nice administrative stuff, > often leading to unnecessarily long downtimes. > > I think Oracle fixed it later by allowing asynchronous update of global index afte

Re: vacuum vs vacuum full

Hi Laurenz, > On 18. Nov, 2020, at 13:02, Laurenz Albe wrote: > > I personally hope that we will never have global indexes. > I am not looking forward to helping customers with the problems that > they create (long duration of ATTACH/DETACH PARTITION, index fragmentation). +1. Experience shows

Re: vacuum vs vacuum full

On Wed, 2020-11-18 at 10:57 +0100, Thomas Kellerer wrote: > > No matter how long it takes, this is an excellent argument for > > partitioning Very Large Tables: many maintenance tasks are made > > *much* easier. > > The problem is, you can't partition every table as long as Postgres > does not sup

Re: vacuum vs vacuum full

On Wed, Nov 18, 2020 at 10:45 AM Ron wrote: > On 11/18/20 3:41 AM, Olivier Gautherot wrote: > > Hi Atul, > > On Wed, Nov 18, 2020 at 9:33 AM Atul Kumar wrote: > >> Hi, >> >> We have a table of 3113GB, and we are planning to vacuum it in non >> business hours i.e. 12AM to 4AM, So my queries are:

Re: vacuum vs vacuum full

Ron schrieb am 18.11.2020 um 10:44: > No matter how long it takes, this is an excellent argument for > partitioning Very Large Tables: many maintenance tasks are made > *much* easier. The problem is, you can't partition every table as long as Postgres does not support a primary key that is indepen

Re: vacuum vs vacuum full

On 11/18/20 3:41 AM, Olivier Gautherot wrote: Hi Atul, On Wed, Nov 18, 2020 at 9:33 AM Atul Kumar > wrote: Hi, We have a table of 3113GB, and we are planning to vacuum it in non business hours i.e. 12AM to 4AM, So my queries are: [snip] 3. Wil

Re: vacuum vs vacuum full

Hi Atul, On Wed, Nov 18, 2020 at 9:33 AM Atul Kumar wrote: > Hi, > > We have a table of 3113GB, and we are planning to vacuum it in non > business hours i.e. 12AM to 4AM, So my queries are: > > 1. What should be perform on the table Vacuum or Vacuum full ? > Vacuum full will do a complete rewri

Re: vacuum vs vacuum full

On 11/18/20 2:33 AM, Atul Kumar wrote: Hi, We have a table of 3113GB, and we are planning to vacuum it in non business hours i.e. 12AM to 4AM, So my queries are: 1. What should be perform on the table Vacuum or Vacuum full ? The documentation *clearly states* the difference between VACUUM and

vacuum vs vacuum full

Hi, We have a table of 3113GB, and we are planning to vacuum it in non business hours i.e. 12AM to 4AM, So my queries are: 1. What should be perform on the table Vacuum or Vacuum full ? 2. Do we need to perform Analyze also? 3. Will the operation be completed in the given time frame? how to check