Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1rys50-000uLP-Jh for pgsql-general@arkaria.postgresql.org; Mon, 22 Apr 2024 11:43:26 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1rys4z-001TyD-9S for pgsql-general@arkaria.postgresql.org; Mon, 22 Apr 2024 11:43:25 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1rys4y-001Txm-QU for pgsql-general@lists.postgresql.org; Mon, 22 Apr 2024 11:43:24 +0000 Received: from mail-ej1-x62d.google.com ([2a00:1450:4864:20::62d]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1rys4w-00448H-9X for pgsql-general@postgresql.org; Mon, 22 Apr 2024 11:43:23 +0000 Received: by mail-ej1-x62d.google.com with SMTP id a640c23a62f3a-a5200afe39eso469623766b.1 for ; Mon, 22 Apr 2024 04:43:21 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=f10-com-br.20230601.gappssmtp.com; s=20230601; t=1713786200; x=1714391000; darn=postgresql.org; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:from:to:cc:subject:date:message-id:reply-to; bh=1huZyRMcyeBqo11cNrSe+yB1BXGLd1VYTKlZaFOoCmA=; b=pUUFPqU3/J+Tpm6DihQlQhKW+h0efPS5VsXYAi8jIUKKhhzk8V1TTMRv4CiNsdKZyK ok/Wjf8TynPFV/vjSngnvjRAey8g+mdVgQp+A9T3etulTB+9IiH9uK31D8tQruJpFog3 +fUPy0aAen1HZoawIBFsHIIm9sXGc4/K7KxcXw6TUQFmFzOteaJxVA1VRTf5blI1uH/B CjTa30ahSmcdSmEs1Gfakfdc1lWYUwnv0SuqTW4GETONTPw0l/LCZC0BbLyFz+Bp66nP iBrrgXamo79kfDubyFOuJUuOLNzvAdt4sDHXwhaB0/3FMp8tHI+mADdpOoQyJrYaeR3F 0ydg== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1713786200; x=1714391000; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:x-gm-message-state:from:to:cc:subject:date:message-id :reply-to; bh=1huZyRMcyeBqo11cNrSe+yB1BXGLd1VYTKlZaFOoCmA=; b=rwQfP9t5AAIDga2MV1jdgOqijNvdUcV6wDwr+OiP63zzABqfES4GlnTN0nCd0Tqs6L MdIRjF6Pl1PM59FtUtjkfm0p6ux/OasIXvilm2nFH9++yr8wV0r1Pxrjtd39hSof9bBc FIxGri/ecP+s7oFbpamjAgAH6d+rKotWbWhYoUGHnnb0sp3YMwq/80A8EdSG6blGgZoN osX+uu1K08/ZNKyt+877TT0aHAnvbDnu+kDimu+5+bfvKKEkXOMglMhoUF9vkuMmiK1P R4k0xxP7Cez+UjdoqjMVV/301cTQO2yMplqNZZLu/8S0bRw0Kx8kDlb57YLh03awCInK Rm7g== X-Forwarded-Encrypted: i=1; AJvYcCVyCVX7W7zsK1BShUPOVtGRR/pEpQyiNCSFVFaussbsrqEvw+BjLBJmy/TUlUf+gQjiA9ulrrtDCMUWXyrS5QvkktAl8sqWWbrqga/B X-Gm-Message-State: AOJu0Yy3bjc04z9phcUqycSFQl5Sb9aqCIGT+15d5g1Z+8Z3rndAdBbT EtMAm1RS9bcLzH+My7yMFXOjZQkToKrMLuUIstmEDcblFld8NjmW3HtrwZ7jg/Pwq/RhK5MXLXr SceBjeZVSWFBdp5Ly8hvHiCkV1O4opteFh7eu X-Google-Smtp-Source: AGHT+IGjiqLM+4v2kC1UaqXWp4WwdTLFIjxr8SiDcanQyhvDBlYDZSXCRCGko++fellfKfbRiBHwRewKeYQM4FkXmKs= X-Received: by 2002:a17:906:4555:b0:a53:ed1b:f3ba with SMTP id s21-20020a170906455500b00a53ed1bf3bamr8984053ejq.28.1713786199849; Mon, 22 Apr 2024 04:43:19 -0700 (PDT) MIME-Version: 1.0 References: <2870091.1713739514@sss.pgh.pa.us> In-Reply-To: From: Marcos Pegoraro Date: Mon, 22 Apr 2024 08:42:36 -0300 Message-ID: Subject: Re: CLUSTER vs. VACUUM FULL To: David Rowley Cc: Ron Johnson , pgsql-general Content-Type: multipart/alternative; boundary="000000000000a9d6da0616adf163" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --000000000000a9d6da0616adf163 Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable Em dom., 21 de abr. de 2024 =C3=A0s 22:35, David Rowley escreveu: > > Both VACUUM FULL and CLUSTER go through a very similar code path. Both > use cluster_rel(). VACUUM FULL just won't make use of an existing > index to provide presorted input or perform a sort, whereas CLUSTER > will attempt to choose the cheapest out of these two to get sorted > results. But wouldn't it be good that VACUUM FULL uses that index defined by Cluster, if it exists ? Maybe an additional option for VACUUM FULL ? Because if I periodically reorganize all tables I have to run CLUSTER once, which will run on clustered tables, and VACUUM FULL on every table that is not clustered, because if I run VACUUM FULL for entire database it'll just ignore cluster index defined for every table. So, INDISCLUSTERED is used when running CLUSTER but is ignored when running VACUUM FULL. regards Marcos --000000000000a9d6da0616adf163 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
Em dom., 21 de abr. de = 2024 =C3=A0s 22:35, David Rowley <dgrowleyml@gmail.com> escreveu:

Both VACUUM FULL and CLUSTER go through a very similar code path. Both
use cluster_rel().=C2=A0 VACUUM FULL just won't make use of an existing=
index to provide presorted input or perform a sort, whereas CLUSTER
will attempt to choose the cheapest out of these two to get sorted
results.

But wouldn't it b= e good that VACUUM FULL uses that index defined by Cluster,=C2=A0if it exis= ts=C2=A0? Maybe an additional option for VACUUM FULL ?
Because if I periodically reorganize all tables I have to run CLUSTER on= ce, which will run on clustered tables, and VACUUM FULL on every table that= is not clustered, because if I run VACUUM FULL for entire database it'= ll just ignore cluster index defined for every table. So, INDISCLUSTERED is= used when running CLUSTER but is ignored when running VACUUM FULL.

regards
= Marcos
--000000000000a9d6da0616adf163--