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

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