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 1t18Z7-00EsvJ-DU for pgsql-hackers@arkaria.postgresql.org; Wed, 16 Oct 2024 18:16:09 +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 1t18Z5-007g8S-P3 for pgsql-hackers@arkaria.postgresql.org; Wed, 16 Oct 2024 18:16:08 +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 1t18Z5-007g8J-0W for pgsql-hackers@lists.postgresql.org; Wed, 16 Oct 2024 18:16:07 +0000 Received: from mail-qv1-xf33.google.com ([2607:f8b0:4864:20::f33]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1t18Z2-001DrX-76 for pgsql-hackers@lists.postgresql.org; Wed, 16 Oct 2024 18:16:06 +0000 Received: by mail-qv1-xf33.google.com with SMTP id 6a1803df08f44-6cbe90c7c95so869286d6.1 for ; Wed, 16 Oct 2024 11:16:04 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1729102563; x=1729707363; darn=lists.postgresql.org; h=references:to:cc:in-reply-to:date:subject:mime-version:message-id :from:from:to:cc:subject:date:message-id:reply-to; bh=dcy+CFbfMr2I3uIENgmfwJ0jeiUw/DM0a1fM++TcDDk=; b=m/VDCjIlH8wtUsS7VZF26Jw5513NxUavaEjm8kjuiZrA12CYUr8sNcyL/Lo1qn46WY ydZ55V/BtPfUKLfGfHQz7osaaKulZ6OqYNfm2wOvDO/SQAYwg2tGx2cjcSwz7PogDwHF 5oBjwvPw9QuJwqBxFRW2MGHY70T8BQDyMd8e4XFxEGusVF82o6ZG1hoQDmJZTVGcWEqs 5TIBlFL49c6RIj+60VG9qhbb3kA88TKrL39cjvMX/9mAaU5FM1qSUoseYUAzv5BGt1Bb iTiy/69/7dtxrmETLRB4vZEfOFC9s04tL/dN44LDzLGw7RjL3T4y2MHsOHPpI3AgTfp4 EgtQ== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1729102563; x=1729707363; h=references:to:cc:in-reply-to:date:subject:mime-version:message-id :from:x-gm-message-state:from:to:cc:subject:date:message-id:reply-to; bh=dcy+CFbfMr2I3uIENgmfwJ0jeiUw/DM0a1fM++TcDDk=; b=WBKxFGY+KLgRVaaDvC/o4ICxyIcy2uPsd638ZH//ubgp0X/PZLregBDNjj/RSueD1H 0drcE3qkelSneVIAQ6q+ZOohEnmNtzFTfr8c2taNFS33IIyIE8a9vdm3VFw6diVdz85v ubtxCq1OFQKoc1bSuD3DbsZCIbbvyqzq5yR0ByZneNd0n2YCq0n+g37afBzC4/PcleK8 kxwIjNB4wMELA+j2uNU6Ol6NtjKDFAn2j4KzKblsRe1Oetf6oCdmaGKBzTzgjz2S66pV xr6mHRVYo3ykptHv6LyTys0dkZgPP4FOVjhitKkvY1l7KgCUHjlw2Fe1Yp2JLIr4QrNv ty5g== X-Forwarded-Encrypted: i=1; AJvYcCUL/uVk3KgdgW/RLW1SnCTdjklq+d8hU1rMMLTn0uyRwlGvj1V7c3XsNQh+th1rVIFq11mJ+G2PGBgL/XFA@lists.postgresql.org X-Gm-Message-State: AOJu0YzQBB5DleqA2lH4BN6nG3Ih2NLpOk9CefHdOZ1Fv0xJFS5bo4gX WKPx1r9AGZ8X5Hn4OFbpMvOI0U54hW5yQILBUlnaUjjGezSy0wFx X-Google-Smtp-Source: AGHT+IHdXi0HpSUuNUEL3a6fXP0YSYDn5pIH2nqlEqVmeHVPD5F+8YOmZv72nudnRco2LnAyxwNiJw== X-Received: by 2002:a05:6214:5d0b:b0:6cc:1e1c:b325 with SMTP id 6a1803df08f44-6cc1e1cb376mr137050216d6.42.1729102563129; Wed, 16 Oct 2024 11:16:03 -0700 (PDT) Received: from smtpclient.apple ([2601:192:4e00:11c7::c7]) by smtp.gmail.com with ESMTPSA id 6a1803df08f44-6cc22961bdbsm19946916d6.104.2024.10.16.11.16.02 (version=TLS1_2 cipher=ECDHE-ECDSA-AES128-GCM-SHA256 bits=128/128); Wed, 16 Oct 2024 11:16:02 -0700 (PDT) From: Shayon Mukherjee Message-Id: <6060730B-DFEA-4913-A696-99AFE61DC2BE@gmail.com> Content-Type: multipart/alternative; boundary="Apple-Mail=_4CA67527-9F2C-457A-ABBA-F92623F5DB75" Mime-Version: 1.0 (Mac OS X Mail 16.0 \(3731.300.101.1.3\)) Subject: Re: Proposal to Enable/Disable Index using ALTER INDEX (with patch) Date: Wed, 16 Oct 2024 14:15:51 -0400 In-Reply-To: Cc: Robert Haas , Peter Eisentraut , PostgreSQL Hackers To: David Rowley References: <15238d97-f667-48df-8319-ab73b37d4511@eisentraut.org> <31040EAB-AB69-466C-A41B-B34EB6324150@gmail.com> X-Mailer: Apple Mail (2.3731.300.101.1.3) List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --Apple-Mail=_4CA67527-9F2C-457A-ABBA-F92623F5DB75 Content-Transfer-Encoding: quoted-printable Content-Type: text/plain; charset=utf-8 > On Oct 16, 2024, at 12:19 PM, Shayon Mukherjee = wrote: >=20 > - ALTER INDEX ... ENABLE/DISABLE performs an in-place update of the = pg_index=20 > catalog to protect against indcheckxmin [2] (older unrelated = thread). Performing the in place update of the pg_index row from = ATExecEnableDisableIndex using systable_inplace_update_begin was failing = in CI weirdly but not on my local MacBook when running spec suite. I am = also wondering what is causing the requirement [1] to update the row = in-place vs say using CatalogTupleUpdate since using the later is = working well locally + CI?=20 I have attached a v2 patch (following from the last v1 patch [1]) that = uses CatalogTupleUpdate and local + CI [2] is passing.=20 [1] = https://www.postgresql.org/message-id/20180618215635.m5vrnxdxhxytvmcm@alap= 3.anarazel.de [2] = https://www.postgresql.org/message-id/EF2313B8-A017-4869-9B7F-A24EDD8795DE= %40gmail.com [3] https://github.com/shayonj/postgres/pull/1 Thanks Shayon =EF=BF=BC --Apple-Mail=_4CA67527-9F2C-457A-ABBA-F92623F5DB75 Content-Type: multipart/mixed; boundary="Apple-Mail=_A720B644-54CD-484B-895F-BABE27930CC7" --Apple-Mail=_A720B644-54CD-484B-895F-BABE27930CC7 Content-Transfer-Encoding: quoted-printable Content-Type: text/html; charset=us-ascii
On= Oct 16, 2024, at 12:19 PM, Shayon Mukherjee <shayonj@gmail.com> = wrote:

- ALTER INDEX ... ENABLE/DISABLE performs an = in-place update of the pg_index 
  catalog to = protect against indcheckxmin [2] (older unrelated = thread).

Performing the in place = update of the pg_index row from ATExecEnableDisableIndex = using systable_inplace_update_begin = was failing in CI weirdly but not on my local MacBook when = running spec suite. I am also wondering what is causing the requirement = [1] to update the row in-place vs say using CatalogTupleUpdate = since using the later is working well locally + = CI? 

I have = attached a v2 patch (following from the last v1 patch [1]) that = uses CatalogTupleUpdate and local + CI [2] is = passing. 


Thanks
Shayon<= /div>
= --Apple-Mail=_A720B644-54CD-484B-895F-BABE27930CC7 Content-Disposition: attachment; filename=v2-0001-Introduce-the-ability-to-enable-disable-indexes-u.patch Content-Type: application/octet-stream; x-unix-mode=0644; name="v2-0001-Introduce-the-ability-to-enable-disable-indexes-u.patch" Content-Transfer-Encoding: quoted-printable =46rom=20b7d8e240b6f7cbe873fad1595d2bc79ebe38464b=20Mon=20Sep=2017=20= 00:00:00=202001=0AFrom:=20Shayon=20Mukherjee=20=0A= Date:=20Wed,=2016=20Oct=202024=2014:06:59=20-0400=0ASubject:=20[PATCH=20= v2]=20Introduce=20the=20ability=20to=20enable/disable=20indexes=20using=0A= =20ALTER=20INDEX=0A=0A---=0A=20doc/src/sgml/catalogs.sgml=20=20=20=20=20=20= =20=20=20=20=20=20=20=20=20=20=20|=20=2011=20+=0A=20= doc/src/sgml/ref/alter_index.sgml=20=20=20=20=20=20=20=20=20=20|=20=2043=20= ++=0A=20doc/src/sgml/ref/create_index.sgml=20=20=20=20=20=20=20=20=20|=20= =2029=20++=0A=20src/backend/bootstrap/bootparse.y=20=20=20=20=20=20=20=20= =20=20|=20=20=202=20+=0A=20src/backend/catalog/index.c=20=20=20=20=20=20=20= =20=20=20=20=20=20=20=20=20|=20=2031=20+-=0A=20= src/backend/catalog/toasting.c=20=20=20=20=20=20=20=20=20=20=20=20=20|=20= =20=202=20+-=0A=20src/backend/commands/indexcmds.c=20=20=20=20=20=20=20=20= =20=20=20|=20=20=204=20+=0A=20src/backend/commands/tablecmds.c=20=20=20=20= =20=20=20=20=20=20=20|=20=2058=20++-=0A=20= src/backend/optimizer/util/plancat.c=20=20=20=20=20=20=20|=20=2013=20+=0A= =20src/backend/parser/gram.y=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20= =20=20=20|=20=2048=20+-=0A=20src/backend/parser/parse_utilcmd.c=20=20=20=20= =20=20=20=20=20|=20=20=203=20+=0A=20src/backend/utils/adt/ruleutils.c=20=20= =20=20=20=20=20=20=20=20|=20=20=204=20+=0A=20= src/backend/utils/cache/relcache.c=20=20=20=20=20=20=20=20=20|=20=20=201=20= +=0A=20src/include/catalog/index.h=20=20=20=20=20=20=20=20=20=20=20=20=20= =20=20=20|=20=20=202=20+-=0A=20src/include/catalog/pg_index.h=20=20=20=20= =20=20=20=20=20=20=20=20=20|=20=20=201=20+=0A=20= src/include/nodes/parsenodes.h=20=20=20=20=20=20=20=20=20=20=20=20=20|=20= =20=203=20+=0A=20src/include/nodes/pathnodes.h=20=20=20=20=20=20=20=20=20= =20=20=20=20=20|=20=20=202=20+=0A=20= src/test/regress/expected/create_index.out=20|=20494=20= +++++++++++++++++++++=0A=20src/test/regress/sql/create_index.sql=20=20=20= =20=20=20|=20181=20++++++++=0A=2019=20files=20changed,=20922=20= insertions(+),=2010=20deletions(-)=0A=0Adiff=20--git=20= a/doc/src/sgml/catalogs.sgml=20b/doc/src/sgml/catalogs.sgml=0Aindex=20= 964c819a02..124ca3dc35=20100644=0A---=20a/doc/src/sgml/catalogs.sgml=0A= +++=20b/doc/src/sgml/catalogs.sgml=0A@@=20-4590,6=20+4590,17=20@@=20= SCRAM-SHA-256$<iteration=20= count>:&l=0A=20=20=20=20=20=20=20=20= partial=20index.=0A=20=20=20=20=20=20=20=0A=20=20=20=20=20= =20=0A+=0A+=20=20=20=20=20=0A+=20=20=20=20=20=20=0A+=20=20= =20=20=20=20=20indisenabled=20= bool=0A+=20=20=20=20=20=20=0A+=20=20=20=20=20=20= =0A+=20=20=20=20=20=20=20If=20true,=20the=20index=20is=20currently=20= enabled=20and=20should=20be=20used=20for=20queries.=0A+=20=20=20=20=20=20= =20If=20false,=20the=20index=20is=20disabled=20and=20should=20not=20be=20= used=20for=20queries,=0A+=20=20=20=20=20=20=20but=20is=20still=20= maintained=20when=20the=20table=20is=20modified.=20Default=20is=20true.=0A= +=20=20=20=20=20=20=0A+=20=20=20=20=20=0A=20=20=20=20= =20=0A=20=20=20=20=0A=20=20=20=0Adiff=20--git=20= a/doc/src/sgml/ref/alter_index.sgml=20= b/doc/src/sgml/ref/alter_index.sgml=0Aindex=20e26efec064..c5c13a3cb3=20= 100644=0A---=20a/doc/src/sgml/ref/alter_index.sgml=0A+++=20= b/doc/src/sgml/ref/alter_index.sgml=0A@@=20-31,6=20+31,8=20@@=20ALTER=20= INDEX=20[=20IF=20EXISTS=20]=20name=20ALTE=0A=20=20=20=20=20SET=20= STATISTICS=20integer=0A=20= ALTER=20INDEX=20ALL=20IN=20TABLESPACE=20name=20[=20OWNED=20BY=20role_name=20[,=20...=20]=20]=0A=20=20=20= =20=20SET=20TABLESPACE=20new_tablespace=20[=20NOWAIT=20]=0A= +ALTER=20INDEX=20[=20IF=20EXISTS=20]=20name=20ENABLE=0A+ALTER=20INDEX=20[=20= IF=20EXISTS=20]=20name=20= DISABLE=0A=20=0A=20=20=0A=20=0A@@=20-158,6=20= +160,33=20@@=20ALTER=20INDEX=20ALL=20IN=20TABLESPACE=20name=0A=20=20=20=20=20=0A=20= =20=20=20=0A=20=0A+=20=20=20=0A+=20=20=20=20= ENABLE=0A+=20=20=20=20=0A+=20=20= =20=20=20=0A+=20=20=20=20=20=20Enable=20the=20specified=20index.=20= The=20index=20will=20be=20used=20by=20the=20query=20planner=0A+=20=20=20=20= =20=20for=20query=20optimization.=20This=20is=20the=20default=20state=20= for=20newly=20created=20indexes.=0A+=20=20=20=20=20=0A+=20=20=20=20= =0A+=20=20=20=0A+=0A+=20=20=20=0A= +=20=20=20=20DISABLE=0A+=20=20=20=20= =0A+=20=20=20=20=20=0A+=20=20=20=20=20=20Disable=20the=20= specified=20index.=20A=20disabled=20index=20is=20not=20used=20by=20the=20= query=20planner=0A+=20=20=20=20=20=20for=20query=20optimization,=20but=20= it=20is=20still=20maintained=20when=20the=20underlying=20table=0A+=20=20=20= =20=20=20data=20changes.=20This=20can=20be=20useful=20for=20testing=20= query=20performance=20with=20and=20without=0A+=20=20=20=20=20=20specific=20= indexes,=20temporarily=20reducing=20the=20overhead=20of=20index=20= maintenance=0A+=20=20=20=20=20=20during=20bulk=20data=20loading=20= operations,=20or=20verifying=20an=20index=20is=20not=20being=20used=0A+=20= =20=20=20=20=20before=20dropping=20it.=20If=20performance=20degrades=20= after=20disabling=20an=20index,=20it=20can=20be=0A+=20=20=20=20=20=20= easily=20re-enabled.=20Before=20disabling,=20it's=20recommended=20to=20= check=0A+=20=20=20=20=20=20= pg_stat_user_indexes.idx_scan=0A+=20=20=20=20=20=20to=20identify=20potentially=20unused=20= indexes.=0A+=20=20=20=20=20=0A+=20=20=20=20=0A+=20=20=20= =0A+=0A=20=20=20=0A=20=20=20=0A=20=0A= @@=20-300,6=20+329,20=20@@=20CREATE=20INDEX=20coord_idx=20ON=20measured=20= (x,=20y,=20(z=20+=20t));=0A=20ALTER=20INDEX=20coord_idx=20ALTER=20COLUMN=20= 3=20SET=20STATISTICS=201000;=0A=20=0A=20=0A+=20=20= =0A+=20=20=20To=20enable=20an=20index:=0A+=0A= +ALTER=20INDEX=20idx_name=20ENABLE;=0A+=0A+=20=20= =0A+=0A+=20=20=0A+=20=20=20To=20disable=20an=20index:=0A= +=0A+ALTER=20INDEX=20idx_name=20DISABLE;=0A= +=0A+=20=20=0A+=0A=20=20=0A=20=0A=20=20= =0Adiff=20--git=20a/doc/src/sgml/ref/create_index.sgml=20= b/doc/src/sgml/ref/create_index.sgml=0Aindex=20621bc0e253..f1eebfa250=20= 100644=0A---=20a/doc/src/sgml/ref/create_index.sgml=0A+++=20= b/doc/src/sgml/ref/create_index.sgml=0A@@=20-28,6=20+28,7=20@@=20CREATE=20= [=20UNIQUE=20]=20INDEX=20[=20CONCURRENTLY=20]=20[=20[=20IF=20NOT=20= EXISTS=20]=20storage_parameter=20[=3D=20= value]=20[,=20...=20]=20= )=20]=0A=20=20=20=20=20[=20TABLESPACE=20tablespace_name=20]=0A=20=20=20=20=20[=20= WHERE=20predicate=20]=0A= +=20=20=20=20[=20DISABLE=20]=0A=20=0A=20=20=0A= =20=0A@@=20-380,6=20+381,19=20@@=20CREATE=20[=20UNIQUE=20]=20INDEX=20[=20= CONCURRENTLY=20]=20[=20[=20IF=20NOT=20EXISTS=20]=20=0A=20=20=20=20=20=20=0A= =20=0A+=20=20=20=20=0A+=20=20=20=20=20=20= DISABLE=0A+=20=20=20=20=20=20=0A= +=20=20=20=20=20=20=20=0A+=20=20=20=20=20=20=20=20Creates=20the=20= index=20in=20a=20disabled=20state=20(default=20enabled).=20A=20disabled=20= index=20is=20not=20used=20by=20the=0A+=20=20=20=20=20=20=20=20query=20= planner=20for=20query=20optimization,=20but=20it=20is=20still=20= maintained=20when=20the=0A+=20=20=20=20=20=20=20=20underlying=20table=20= data=20changes.=20This=20can=20be=20useful=20when=20you=20want=20to=20= create=0A+=20=20=20=20=20=20=20=20an=20index=20without=20immediately=20= impacting=20query=20performance,=20allowing=20you=20to=0A+=20=20=20=20=20= =20=20=20enable=20it=20later=20at=20a=20more=20convenient=20time.=20The=20= index=20can=20be=20enabled=20later=0A+=20=20=20=20=20=20=20=20using=20= ALTER=20INDEX=20...=20ENABLE.=0A+=20=20=20=20=20=20=20= =0A+=20=20=20=20=20=20=0A+=20=20=20=20=20= =0A=20=20=20=20=20=0A=20=0A=20=20=20= =0A@@=20-701,6=20+715,14=20@@=20Indexes:=0A=20=20= =20=20=20partitioned=20index=20is=20a=20metadata=20only=20operation.=0A=20= =20=20=20=0A=20=0A+=20=20=0A+=20=20=20When=20creating=20an=20= index=20with=20the=20DISABLE=20option,=20the=20index=0A= +=20=20=20will=20be=20created=20but=20not=20used=20for=20query=20= planning.=20This=20can=20be=20useful=20for=0A+=20=20=20preparing=20an=20= index=20in=20advance=20of=20its=20use,=20or=20for=20testing=20purposes.=20= The=20index=0A+=20=20=20will=20still=20be=20maintained=20as=20the=20= table=20is=20modified,=20so=20it=20can=20be=20enabled=0A+=20=20=20later=20= without=20needing=20to=20be=20rebuilt.=20By=20default=20all=20new=20= indexes=20are=20enabled.=0A+=20=20=0A+=0A=20=20=20=0A=20= =20=0A=20=0A@@=20-980,6=20+1002,13=20@@=20SELECT=20*=20FROM=20= points=0A=20CREATE=20INDEX=20CONCURRENTLY=20sales_quantity_index=20ON=20= sales_table=20(quantity);=0A=20=0A=20=0A+=20=20= =0A+=20=20=20To=20create=20an=20index=20on=20the=20table=20= test_table=20with=20the=20default=0A+=20=20=20name,=20= but=20have=20it=20initially=20disabled:=0A+=0A+CREATE=20= INDEX=20ON=20test_table=20(col1)=20DISABLE;=0A+=0A+=20=20= =0A=20=20=0A=20=0A=20=20=0Adiff=20--git=20= a/src/backend/bootstrap/bootparse.y=20= b/src/backend/bootstrap/bootparse.y=0Aindex=2073a7592fb7..6023d58490=20= 100644=0A---=20a/src/backend/bootstrap/bootparse.y=0A+++=20= b/src/backend/bootstrap/bootparse.y=0A@@=20-302,6=20+302,7=20@@=20= Boot_DeclareIndexStmt:=0A=20=09=09=09=09=09stmt->concurrent=20=3D=20= false;=0A=20=09=09=09=09=09stmt->if_not_exists=20=3D=20false;=0A=20=09=09= =09=09=09stmt->reset_default_tblspc=20=3D=20false;=0A+=09=09=09=09=09= stmt->isenabled=20=3D=20true;=0A=20=0A=20=09=09=09=09=09/*=20locks=20and=20= races=20need=20not=20concern=20us=20in=20bootstrap=20mode=20*/=0A=20=09=09= =09=09=09relationId=20=3D=20RangeVarGetRelid(stmt->relation,=20NoLock,=0A= @@=20-355,6=20+356,7=20@@=20Boot_DeclareUniqueIndexStmt:=0A=20=09=09=09=09= =09stmt->concurrent=20=3D=20false;=0A=20=09=09=09=09=09= stmt->if_not_exists=20=3D=20false;=0A=20=09=09=09=09=09= stmt->reset_default_tblspc=20=3D=20false;=0A+=09=09=09=09=09= stmt->isenabled=20=3D=20true;=0A=20=0A=20=09=09=09=09=09/*=20locks=20and=20= races=20need=20not=20concern=20us=20in=20bootstrap=20mode=20*/=0A=20=09=09= =09=09=09relationId=20=3D=20RangeVarGetRelid(stmt->relation,=20NoLock,=0A= diff=20--git=20a/src/backend/catalog/index.c=20= b/src/backend/catalog/index.c=0Aindex=2012822d0b14..15773ebb13=20100644=0A= ---=20a/src/backend/catalog/index.c=0A+++=20= b/src/backend/catalog/index.c=0A@@=20-118,7=20+118,8=20@@=20static=20= void=20UpdateIndexRelation(Oid=20indexoid,=20Oid=20heapoid,=0A=20=09=09=09= =09=09=09=09=09bool=20isexclusion,=0A=20=09=09=09=09=09=09=09=09bool=20= immediate,=0A=20=09=09=09=09=09=09=09=09bool=20isvalid,=0A-=09=09=09=09=09= =09=09=09bool=20isready);=0A+=09=09=09=09=09=09=09=09bool=20isready,=0A+=09= =09=09=09=09=09=09=09bool=20isenabled);=0A=20static=20void=20= index_update_stats(Relation=20rel,=0A=20=09=09=09=09=09=09=09=20=20=20= bool=20hasindex,=0A=20=09=09=09=09=09=09=09=20=20=20double=20reltuples);=0A= @@=20-569,7=20+570,8=20@@=20UpdateIndexRelation(Oid=20indexoid,=0A=20=09=09= =09=09=09bool=20isexclusion,=0A=20=09=09=09=09=09bool=20immediate,=0A=20=09= =09=09=09=09bool=20isvalid,=0A-=09=09=09=09=09bool=20isready)=0A+=09=09=09= =09=09bool=20isready,=0A+=09=09=09=09=09bool=20isenabled)=0A=20{=0A=20=09= int2vector=20*indkey;=0A=20=09oidvector=20=20*indcollation;=0A@@=20= -647,6=20+649,7=20@@=20UpdateIndexRelation(Oid=20indexoid,=0A=20=09= values[Anum_pg_index_indisready=20-=201]=20=3D=20BoolGetDatum(isready);=0A= =20=09values[Anum_pg_index_indislive=20-=201]=20=3D=20= BoolGetDatum(true);=0A=20=09values[Anum_pg_index_indisreplident=20-=201]=20= =3D=20BoolGetDatum(false);=0A+=09values[Anum_pg_index_indisenabled=20-=20= 1]=20=3D=20BoolGetDatum(isenabled);=0A=20=09values[Anum_pg_index_indkey=20= -=201]=20=3D=20PointerGetDatum(indkey);=0A=20=09= values[Anum_pg_index_indcollation=20-=201]=20=3D=20= PointerGetDatum(indcollation);=0A=20=09values[Anum_pg_index_indclass=20-=20= 1]=20=3D=20PointerGetDatum(indclass);=0A@@=20-712,6=20+715,8=20@@=20= UpdateIndexRelation(Oid=20indexoid,=0A=20=20*=09=09=09already=20exists.=0A= =20=20*=09=09INDEX_CREATE_PARTITIONED:=0A=20=20*=09=09=09create=20a=20= partitioned=20index=20(table=20must=20be=20partitioned)=0A+=20*=09=09= INDEX_CREATE_DISABLED:=0A+*=09=09=09=09create=20the=20index=20as=20= disabled=20if=20instructed,=20defaults=20to=20being=20enabled.=0A=20=20*=20= constr_flags:=20flags=20passed=20to=20index_constraint_create=0A=20=20*=09= =09(only=20if=20INDEX_CREATE_ADD_CONSTRAINT=20is=20set)=0A=20=20*=20= allow_system_table_mods:=20allow=20table=20to=20be=20a=20system=20= catalog=0A@@=20-757,6=20+762,7=20@@=20index_create(Relation=20= heapRelation,=0A=20=09bool=09=09invalid=20=3D=20(flags=20&=20= INDEX_CREATE_INVALID)=20!=3D=200;=0A=20=09bool=09=09concurrent=20=3D=20= (flags=20&=20INDEX_CREATE_CONCURRENT)=20!=3D=200;=0A=20=09bool=09=09= partitioned=20=3D=20(flags=20&=20INDEX_CREATE_PARTITIONED)=20!=3D=200;=0A= +=09bool=20=09=09isenabled=20=20=3D=20(flags=20&=20INDEX_CREATE_ENABLED)=20= !=3D=200;=0A=20=09char=09=09relkind;=0A=20=09TransactionId=20= relfrozenxid;=0A=20=09MultiXactId=20relminmxid;=0A@@=20-1040,13=20= +1046,15=20@@=20index_create(Relation=20heapRelation,=0A=20=09=20*=09=20=20= (Or,=20could=20define=20a=20rule=20to=20maintain=20the=20predicate)=20= --Nels,=20Feb=20'92=0A=20=09=20*=20----------------=0A=20=09=20*/=0A+=0A=20= =09UpdateIndexRelation(indexRelationId,=20heapRelationId,=20= parentIndexRelid,=0A=20=09=09=09=09=09=09indexInfo,=0A=20=09=09=09=09=09=09= collationIds,=20opclassIds,=20coloptions,=0A=20=09=09=09=09=09=09= isprimary,=20is_exclusion,=0A=20=09=09=09=09=09=09(constr_flags=20&=20= INDEX_CONSTR_CREATE_DEFERRABLE)=20=3D=3D=200,=0A=20=09=09=09=09=09=09= !concurrent=20&&=20!invalid,=0A-=09=09=09=09=09=09!concurrent);=0A+=09=09= =09=09=09=09!concurrent,=0A+=09=09=09=09=09=09isenabled);=0A=20=0A=20=09= /*=0A=20=09=20*=20Register=20relcache=20invalidation=20on=20the=20= indexes'=20heap=20relation,=20to=0A@@=20-1315,6=20+1323,8=20@@=20= index_concurrently_create_copy(Relation=20heapRelation,=20Oid=20= oldIndexId,=0A=20=09List=09=20=20=20*indexColNames=20=3D=20NIL;=0A=20=09= List=09=20=20=20*indexExprs=20=3D=20NIL;=0A=20=09List=09=20=20=20= *indexPreds=20=3D=20NIL;=0A+=09Form_pg_index=20indexForm;=0A+=09bits16=20= =09=20createFlags;=0A=20=0A=20=09indexRelation=20=3D=20= index_open(oldIndexId,=20RowExclusiveLock);=0A=20=0A@@=20-1342,6=20= +1352,9=20@@=20index_concurrently_create_copy(Relation=20heapRelation,=20= Oid=20oldIndexId,=0A=20=09=09=09=09=09=09=09=09=09=09=09= Anum_pg_index_indoption);=0A=20=09indcoloptions=20=3D=20(int2vector=20*)=20= DatumGetPointer(colOptionDatum);=0A=20=0A+=09/*=20Get=20the=20enabled=20= state=20of=20the=20original=20index=20*/=0A+=09indexForm=20=3D=20= (Form_pg_index)=20GETSTRUCT(indexTuple);=0A+=0A=20=09/*=20Fetch=20= reloptions=20of=20index=20if=20any=20*/=0A=20=09classTuple=20=3D=20= SearchSysCache1(RELOID,=20ObjectIdGetDatum(oldIndexId));=0A=20=09if=20= (!HeapTupleIsValid(classTuple))=0A@@=20-1433,6=20+1446,16=20@@=20= index_concurrently_create_copy(Relation=20heapRelation,=20Oid=20= oldIndexId,=0A=20=09=09stattargets[i].isnull=20=3D=20isnull;=0A=20=09}=0A= =20=0A+=09/*=0A+=09*=20Determine=20the=20create=20flags=20for=20the=20= new=20index.=0A+=09*=20We=20always=20use=20SKIP_BUILD=20and=20CONCURRENT=20= for=20concurrent=20reindexing.=0A+=09*=20If=20the=20original=20index=20= was=20enabled,=20we=20also=20set=20the=20ENABLED=20flag=0A+=09*=20to=20= maintain=20the=20same=20state=20in=20the=20new=20index.=0A+=09*/=0A+=09= createFlags=20=3D=20INDEX_CREATE_SKIP_BUILD=20|=20= INDEX_CREATE_CONCURRENT;=0A+=09if=20(indexForm->indisenabled)=0A+=09=09=09= createFlags=20|=3D=20INDEX_CREATE_ENABLED;=0A+=0A=20=09/*=0A=20=09=20*=20= Now=20create=20the=20new=20index.=0A=20=09=20*=0A@@=20-1456,7=20+1479,7=20= @@=20index_concurrently_create_copy(Relation=20heapRelation,=20Oid=20= oldIndexId,=0A=20=09=09=09=09=09=09=09=20=20indcoloptions->values,=0A=20=09= =09=09=09=09=09=09=20=20stattargets,=0A=20=09=09=09=09=09=09=09=20=20= reloptionsDatum,=0A-=09=09=09=09=09=09=09=20=20INDEX_CREATE_SKIP_BUILD=20= |=20INDEX_CREATE_CONCURRENT,=0A+=09=09=09=09=09=09=09=20=20createFlags,=0A= =20=09=09=09=09=09=09=09=20=200,=0A=20=09=09=09=09=09=09=09=20=20true,=20= /*=20allow=20table=20to=20be=20a=20system=20catalog?=20*/=0A=20=09=09=09=09= =09=09=09=20=20false,=09/*=20is_internal?=20*/=0Adiff=20--git=20= a/src/backend/catalog/toasting.c=20b/src/backend/catalog/toasting.c=0A= index=20ad3082c62a..dead993a74=20100644=0A---=20= a/src/backend/catalog/toasting.c=0A+++=20= b/src/backend/catalog/toasting.c=0A@@=20-325,7=20+325,7=20@@=20= create_toast_table(Relation=20rel,=20Oid=20toastOid,=20Oid=20= toastIndexOid,=0A=20=09=09=09=09=20BTREE_AM_OID,=0A=20=09=09=09=09=20= rel->rd_rel->reltablespace,=0A=20=09=09=09=09=20collationIds,=20= opclassIds,=20NULL,=20coloptions,=20NULL,=20(Datum)=200,=0A-=09=09=09=09=20= INDEX_CREATE_IS_PRIMARY,=200,=20true,=20true,=20NULL);=0A+=09=09=09=09=20= INDEX_CREATE_IS_PRIMARY=20|=20INDEX_CREATE_ENABLED,=200,=20true,=20true,=20= NULL);=0A=20=0A=20=09table_close(toast_rel,=20NoLock);=0A=20=0Adiff=20= --git=20a/src/backend/commands/indexcmds.c=20= b/src/backend/commands/indexcmds.c=0Aindex=20e33ad81529..d0604e9a00=20= 100644=0A---=20a/src/backend/commands/indexcmds.c=0A+++=20= b/src/backend/commands/indexcmds.c=0A@@=20-1192,6=20+1192,10=20@@=20= DefineIndex(Oid=20tableId,=0A=20=09=09flags=20|=3D=20= INDEX_CREATE_PARTITIONED;=0A=20=09if=20(stmt->primary)=0A=20=09=09flags=20= |=3D=20INDEX_CREATE_IS_PRIMARY;=0A+=20=20=20if=20(stmt->isenabled)=0A+=09= =09flags=20|=3D=20INDEX_CREATE_ENABLED;=0A+=20=20=20else=0A+=09=09flags=20= &=3D=20~INDEX_CREATE_ENABLED;=0A=20=0A=20=09/*=0A=20=09=20*=20If=20the=20= table=20is=20partitioned,=20and=20recursion=20was=20declined=20but=20= partitions=0Adiff=20--git=20a/src/backend/commands/tablecmds.c=20= b/src/backend/commands/tablecmds.c=0Aindex=201ccc80087c..7ccd15b06d=20= 100644=0A---=20a/src/backend/commands/tablecmds.c=0A+++=20= b/src/backend/commands/tablecmds.c=0A@@=20-664,7=20+664,7=20@@=20static=20= List=20*GetParentedForeignKeyRefs(Relation=20partition);=0A=20static=20= void=20ATDetachCheckNoForeignKeyRefs(Relation=20partition);=0A=20static=20= char=20GetAttributeCompression(Oid=20atttypid,=20const=20char=20= *compression);=0A=20static=20char=20GetAttributeStorage(Oid=20atttypid,=20= const=20char=20*storagemode);=0A-=0A+static=20void=20= ATExecEnableDisableIndex(Relation=20rel,=20bool=20enable);=0A=20=0A=20/*=20= ----------------------------------------------------------------=0A=20=20= *=09=09DefineRelation=0A@@=20-4558,6=20+4558,8=20@@=20= AlterTableGetLockLevel(List=20*cmds)=0A=20=09=09=09case=20= AT_SetExpression:=0A=20=09=09=09case=20AT_DropExpression:=0A=20=09=09=09= case=20AT_SetCompression:=0A+=09=09=09case=20AT_EnableIndex:=0A+=09=09=09= case=20AT_DisableIndex:=0A=20=09=09=09=09cmd_lockmode=20=3D=20= AccessExclusiveLock;=0A=20=09=09=09=09break;=0A=20=0A@@=20-5131,6=20= +5133,12=20@@=20ATPrepCmd(List=20**wqueue,=20Relation=20rel,=20= AlterTableCmd=20*cmd,=0A=20=09=09=09/*=20No=20command-specific=20prep=20= needed=20*/=0A=20=09=09=09pass=20=3D=20AT_PASS_MISC;=0A=20=09=09=09= break;=0A+=09=09case=20AT_EnableIndex:=0A+=09=09case=20AT_DisableIndex:=0A= +=09=09=09ATSimplePermissions(cmd->subtype,=20rel,=20ATT_INDEX=20|=20= ATT_PARTITIONED_INDEX);=0A+=09=09=09/*=20No=20command-specific=20prep=20= needed=20*/=0A+=09=09=09pass=20=3D=20AT_PASS_MISC;=0A+=09=09=09break;=0A=20= =09=09default:=09=09=09=09/*=20oops=20*/=0A=20=09=09=09elog(ERROR,=20= "unrecognized=20alter=20table=20type:=20%d",=0A=20=09=09=09=09=20(int)=20= cmd->subtype);=0A@@=20-5527,6=20+5535,12=20@@=20ATExecCmd(List=20= **wqueue,=20AlteredTableInfo=20*tab,=0A=20=09=09case=20= AT_DetachPartitionFinalize:=0A=20=09=09=09address=20=3D=20= ATExecDetachPartitionFinalize(rel,=20((PartitionCmd=20*)=20= cmd->def)->name);=0A=20=09=09=09break;=0A+=09=09case=20AT_EnableIndex:=0A= +=09=09=09=09ATExecEnableDisableIndex(rel,=20true);=0A+=09=09=09=09= break;=0A+=09=09case=20AT_DisableIndex:=0A+=09=09=09=09= ATExecEnableDisableIndex(rel,=20false);=0A+=09=09=09=09break;=0A=20=09=09= default:=09=09=09=09/*=20oops=20*/=0A=20=09=09=09elog(ERROR,=20= "unrecognized=20alter=20table=20type:=20%d",=0A=20=09=09=09=09=20(int)=20= cmd->subtype);=0A@@=20-6426,6=20+6440,8=20@@=20= alter_table_type_to_string(AlterTableType=20cmdtype)=0A=20=09=09=09= return=20"DROP=20COLUMN";=0A=20=09=09case=20AT_AddIndex:=0A=20=09=09case=20= AT_ReAddIndex:=0A+=09=09case=20AT_EnableIndex:=0A+=09=09case=20= AT_DisableIndex:=0A=20=09=09=09return=20NULL;=09=09/*=20not=20real=20= grammar=20*/=0A=20=09=09case=20AT_AddConstraint:=0A=20=09=09case=20= AT_ReAddConstraint:=0A@@=20-20203,3=20+20219,43=20@@=20= GetAttributeStorage(Oid=20atttypid,=20const=20char=20*storagemode)=0A=20=0A= =20=09return=20cstorage;=0A=20}=0A+=0A+/*=0A+=20*=20= ATExecEnableDisableIndex=0A+=20*=20Performs=20a=20catalog=20update=20to=20= enable=20or=20disable=20an=20index=20in=20pg_index.=0A+=20*/=0A+static=20= void=0A+ATExecEnableDisableIndex(Relation=20rel,=20bool=20enable)=0A+{=0A= +=09Oid=20=20=20=20=20=20=20=20=20indexOid=20=3D=20= RelationGetRelid(rel);=0A+=09Relation=20=20=20=20pg_index;=0A+=09= HeapTuple=20=20=20indexTuple;=0A+=09Form_pg_index=20indexForm;=0A+=09= bool=20=20=20=20=20=20=20=20updated=20=3D=20false;=0A+=0A+=09pg_index=20= =3D=20table_open(IndexRelationId,=20RowExclusiveLock);=0A+=0A+=09= indexTuple=20=3D=20SearchSysCacheCopy1(INDEXRELID,=20= ObjectIdGetDatum(indexOid));=0A+=09if=20(!HeapTupleIsValid(indexTuple))=0A= +=09=09elog(ERROR,=20"could=20not=20find=20tuple=20for=20index=20%u",=20= indexOid);=0A+=0A+=09indexForm=20=3D=20(Form_pg_index)=20= GETSTRUCT(indexTuple);=0A+=0A+=09if=20(indexForm->indisenabled=20!=3D=20= enable)=0A+=09{=0A+=09=09indexForm->indisenabled=20=3D=20enable;=0A+=0A+=09= =09CatalogTupleUpdate(pg_index,=20&indexTuple->t_self,=20indexTuple);=0A= +=09=09updated=20=3D=20true;=0A+=09}=0A+=0A+=09= heap_freetuple(indexTuple);=0A+=09table_close(pg_index,=20= RowExclusiveLock);=0A+=0A+=09if=20(updated)=0A+=09{=0A+=09=09= CacheInvalidateRelcache(rel);=0A+=09=09= InvokeObjectPostAlterHook(IndexRelationId,=20indexOid,=200);=0A+=09=09= CommandCounterIncrement();=0A+=09}=0A+}=0Adiff=20--git=20= a/src/backend/optimizer/util/plancat.c=20= b/src/backend/optimizer/util/plancat.c=0Aindex=20b913f91ff0..eaa15a1f53=20= 100644=0A---=20a/src/backend/optimizer/util/plancat.c=0A+++=20= b/src/backend/optimizer/util/plancat.c=0A@@=20-295,6=20+295,19=20@@=20= get_relation_info(PlannerInfo=20*root,=20Oid=20relationObjectId,=20bool=20= inhparent,=0A=20=09=09=09info->opcintype=20=3D=20(Oid=20*)=20= palloc(sizeof(Oid)=20*=20nkeycolumns);=0A=20=09=09=09info->canreturn=20=3D= =20(bool=20*)=20palloc(sizeof(bool)=20*=20ncolumns);=0A=20=0A+=09=09=09= /*=0A+=09=09=09=20*=20Skip=20disabled=20indexes=20altogether,=20as=20= they=20should=20not=20be=20considered=0A+=09=09=09=20*=20for=20query=20= planning.=20This=20builds=20the=20data=20structure=20for=20the=20= planner's=0A+=09=09=09=20*=20use=20and=20we=20make=20it=20part=20of=20= IndexOptInfo=20since=20the=20index=20is=20already=20open.=0A+=09=09=09=20= *=20We=20also=20close=20the=20relation=20before=20continuing=20to=20the=20= next=20index.=0A+=09=09=09=20*/=0A+=09=09=09info->enabled=20=3D=20= index->indisenabled;=0A+=09=09=09if=20(!info->enabled)=0A+=09=09=09{=0A+=09= =09=09=09index_close(indexRelation,=20NoLock);=0A+=09=09=09=09continue;=0A= +=09=09=09}=0A+=0A=20=09=09=09for=20(i=20=3D=200;=20i=20<=20ncolumns;=20= i++)=0A=20=09=09=09{=0A=20=09=09=09=09info->indexkeys[i]=20=3D=20= index->indkey.values[i];=0Adiff=20--git=20a/src/backend/parser/gram.y=20= b/src/backend/parser/gram.y=0Aindex=204aa8646af7..c5d0d66445=20100644=0A= ---=20a/src/backend/parser/gram.y=0A+++=20b/src/backend/parser/gram.y=0A= @@=20-333,7=20+333,7=20@@=20static=20Node=20= *makeRecursiveViewSelect(char=20*relname,=20List=20*aliases,=20Node=20= *query);=0A=20%type=20=09add_drop=20opt_asc_desc=20opt_nulls_order=0A= =20=0A=20%type=20=09alter_table_cmd=20alter_type_cmd=20= opt_collate_clause=0A-=09=20=20=20replica_identity=20partition_cmd=20= index_partition_cmd=0A+=09=20=20=20replica_identity=20partition_cmd=20= index_partition_cmd=20index_alter_cmd=0A=20%type=20=09= alter_table_cmds=20alter_type_cmds=0A=20%type=20=20=20=20=20= alter_identity_column_option_list=0A=20%type=20=20=20= alter_identity_column_option=0A@@=20-496,6=20+496,7=20@@=20static=20Node=20= *makeRecursiveViewSelect(char=20*relname,=20List=20*aliases,=20Node=20= *query);=0A=20%type=20=20opt_unique=20opt_verbose=20opt_full=0A=20= %type=20=20opt_freeze=20opt_analyze=20opt_default=0A=20%type=20= =09opt_binary=20copy_delimiter=0A+%type=20=20=20= opt_index_enabled=0A=20=0A=20%type=20=20copy_from=20opt_program=0A= =20=0A@@=20-2168,6=20+2169,24=20@@=20AlterTableStmt:=0A=20=09=09=09=09=09= n->nowait=20=3D=20$13;=0A=20=09=09=09=09=09$$=20=3D=20(Node=20*)=20n;=0A=20= =09=09=09=09}=0A+=09=09|=20ALTER=20INDEX=20qualified_name=20= index_alter_cmd=0A+=09=09=09=09{=0A+=09=09=09=09=09AlterTableStmt=20*n=20= =3D=20makeNode(AlterTableStmt);=0A+=09=09=09=09=09n->relation=20=3D=20= $3;=0A+=09=09=09=09=09n->cmds=20=3D=20list_make1($4);=0A+=09=09=09=09=09= n->objtype=20=3D=20OBJECT_INDEX;=0A+=09=09=09=09=09n->missing_ok=20=3D=20= false;=0A+=09=09=09=09=09$$=20=3D=20(Node=20*)=20n;=0A+=09=09=09=09}=0A+=09= =09|=20ALTER=20INDEX=20IF_P=20EXISTS=20qualified_name=20index_alter_cmd=0A= +=09=09=09=09{=0A+=09=09=09=09=09AlterTableStmt=20*n=20=3D=20= makeNode(AlterTableStmt);=0A+=09=09=09=09=09n->relation=20=3D=20$5;=0A+=09= =09=09=09=09n->cmds=20=3D=20list_make1($6);=0A+=09=09=09=09=09n->objtype=20= =3D=20OBJECT_INDEX;=0A+=09=09=09=09=09n->missing_ok=20=3D=20true;=0A+=09=09= =09=09=09$$=20=3D=20(Node=20*)=20n;=0A+=09=09=09=09}=0A=20=09=09|=09= ALTER=20INDEX=20qualified_name=20alter_table_cmds=0A=20=09=09=09=09{=0A=20= =09=09=09=09=09AlterTableStmt=20*n=20=3D=20makeNode(AlterTableStmt);=0A= @@=20-2393,6=20+2412,21=20@@=20index_partition_cmd:=0A=20=09=09=09=09}=0A= =20=09=09;=0A=20=0A+index_alter_cmd:=0A+=09=09/*=20ALTER=20INDEX=20= =20ENABLE|DISABLE=20*/=0A+=09=09ENABLE_P=0A+=09=09=09{=0A+=09=09=09= =09AlterTableCmd=20*n=20=3D=20makeNode(AlterTableCmd);=0A+=09=09=09=09= n->subtype=20=3D=20AT_EnableIndex;=0A+=09=09=09=09$$=20=3D=20(Node=20*)=20= n;=0A+=09=09=09}=0A+=09=09|=20DISABLE_P=0A+=09=09=09{=0A+=09=09=09=09= AlterTableCmd=20*n=20=3D=20makeNode(AlterTableCmd);=0A+=09=09=09=09= n->subtype=20=3D=20AT_DisableIndex;=0A+=09=09=09=09$$=20=3D=20(Node=20*)=20= n;=0A+=09=09=09}=0A+=09=09;=0A=20alter_table_cmd:=0A=20=09=09=09/*=20= ALTER=20TABLE=20=20ADD=20=20*/=0A=20=09=09=09ADD_P=20= columnDef=0A@@=20-8126,7=20+8160,7=20@@=20defacl_privilege_target:=0A=20=0A= =20IndexStmt:=09CREATE=20opt_unique=20INDEX=20opt_concurrently=20= opt_single_name=0A=20=09=09=09ON=20relation_expr=20access_method_clause=20= '('=20index_params=20')'=0A-=09=09=09opt_include=20= opt_unique_null_treatment=20opt_reloptions=20OptTableSpace=20= where_clause=0A+=09=09=09opt_include=20opt_unique_null_treatment=20= opt_reloptions=20OptTableSpace=20where_clause=20opt_index_enabled=0A=20=09= =09=09=09{=0A=20=09=09=09=09=09IndexStmt=20*n=20=3D=20= makeNode(IndexStmt);=0A=20=0A@@=20-8141,6=20+8175,7=20@@=20IndexStmt:=09= CREATE=20opt_unique=20INDEX=20opt_concurrently=20opt_single_name=0A=20=09= =09=09=09=09n->options=20=3D=20$14;=0A=20=09=09=09=09=09n->tableSpace=20= =3D=20$15;=0A=20=09=09=09=09=09n->whereClause=20=3D=20$16;=0A+=09=09=09=09= =09n->isenabled=20=3D=20$17;=0A=20=09=09=09=09=09n->excludeOpNames=20=3D=20= NIL;=0A=20=09=09=09=09=09n->idxcomment=20=3D=20NULL;=0A=20=09=09=09=09=09= n->indexOid=20=3D=20InvalidOid;=0A@@=20-8158,7=20+8193,7=20@@=20= IndexStmt:=09CREATE=20opt_unique=20INDEX=20opt_concurrently=20= opt_single_name=0A=20=09=09=09=09}=0A=20=09=09=09|=20CREATE=20opt_unique=20= INDEX=20opt_concurrently=20IF_P=20NOT=20EXISTS=20name=0A=20=09=09=09ON=20= relation_expr=20access_method_clause=20'('=20index_params=20')'=0A-=09=09= =09opt_include=20opt_unique_null_treatment=20opt_reloptions=20= OptTableSpace=20where_clause=0A+=09=09=09opt_include=20= opt_unique_null_treatment=20opt_reloptions=20OptTableSpace=20= where_clause=20opt_index_enabled=0A=20=09=09=09=09{=0A=20=09=09=09=09=09= IndexStmt=20*n=20=3D=20makeNode(IndexStmt);=0A=20=0A@@=20-8173,6=20= +8208,7=20@@=20IndexStmt:=09CREATE=20opt_unique=20INDEX=20= opt_concurrently=20opt_single_name=0A=20=09=09=09=09=09n->options=20=3D=20= $17;=0A=20=09=09=09=09=09n->tableSpace=20=3D=20$18;=0A=20=09=09=09=09=09= n->whereClause=20=3D=20$19;=0A+=09=09=09=09=09n->isenabled=20=3D=20$20;=0A= =20=09=09=09=09=09n->excludeOpNames=20=3D=20NIL;=0A=20=09=09=09=09=09= n->idxcomment=20=3D=20NULL;=0A=20=09=09=09=09=09n->indexOid=20=3D=20= InvalidOid;=0A@@=20-8195,6=20+8231,12=20@@=20opt_unique:=0A=20=09=09=09|=20= /*EMPTY*/=09=09=09=09=09=09=09=09{=20$$=20=3D=20false;=20}=0A=20=09=09;=0A= =20=0A+opt_index_enabled:=0A+=09=09=09ENABLE_P=20=20=20=20=20=20=20=20=20= =20=20=20=20=20=20=20=20=20=20=20=20=20{=20$$=20=3D=20true;=20}=0A+=09=09= =09|=20DISABLE_P=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20= {=20$$=20=3D=20false;=20}=0A+=09=09=09|=20/*EMPTY*/=20=20=20=20=20=20=20=20= =20=20=20=20=20=20=20=20=20=09{=20$$=20=3D=20true;=20}=0A+=09=09;=0A+=0A=20= access_method_clause:=0A=20=09=09=09USING=20name=09=09=09=09=09=09=09=09= {=20$$=20=3D=20$2;=20}=0A=20=09=09=09|=20/*EMPTY*/=09=09=09=09=09=09=09=09= {=20$$=20=3D=20DEFAULT_INDEX_TYPE;=20}=0Adiff=20--git=20= a/src/backend/parser/parse_utilcmd.c=20= b/src/backend/parser/parse_utilcmd.c=0Aindex=201e15ce10b4..32554612ed=20= 100644=0A---=20a/src/backend/parser/parse_utilcmd.c=0A+++=20= b/src/backend/parser/parse_utilcmd.c=0A@@=20-1588,6=20+1588,7=20@@=20= generateClonedIndexStmt(RangeVar=20*heapRel,=20Relation=20source_idx,=0A=20= =09index->concurrent=20=3D=20false;=0A=20=09index->if_not_exists=20=3D=20= false;=0A=20=09index->reset_default_tblspc=20=3D=20false;=0A+=09= index->isenabled=20=3D=20idxrec->indisenabled;=0A=20=0A=20=09/*=0A=20=09=20= *=20We=20don't=20try=20to=20preserve=20the=20name=20of=20the=20source=20= index;=20instead,=20just=0A@@=20-2214,6=20+2215,8=20@@=20= transformIndexConstraint(Constraint=20*constraint,=20CreateStmtContext=20= *cxt)=0A=20=09index->concurrent=20=3D=20false;=0A=20=09= index->if_not_exists=20=3D=20false;=0A=20=09index->reset_default_tblspc=20= =3D=20constraint->reset_default_tblspc;=0A+=09/*=20Ensure=20indexes=20= for=20constraints=20are=20created=20as=20enabled=20by=20default=20*/=0A+=09= index->isenabled=20=3D=20true;=0A=20=0A=20=09/*=0A=20=09=20*=20If=20it's=20= ALTER=20TABLE=20ADD=20CONSTRAINT=20USING=20INDEX,=20look=20up=20the=20= index=20and=0Adiff=20--git=20a/src/backend/utils/adt/ruleutils.c=20= b/src/backend/utils/adt/ruleutils.c=0Aindex=202177d17e27..05b27ca232=20= 100644=0A---=20a/src/backend/utils/adt/ruleutils.c=0A+++=20= b/src/backend/utils/adt/ruleutils.c=0A@@=20-1559,6=20+1559,10=20@@=20= pg_get_indexdef_worker(Oid=20indexrelid,=20int=20colno,=0A=20=09=09=09= else=0A=20=09=09=09=09appendStringInfo(&buf,=20"=20WHERE=20%s",=20str);=0A= =20=09=09}=0A+=0A+=09=09/*=20Add=20DISABLE=20clause=20if=20the=20index=20= is=20disabled=20*/=0A+=09=09if=20(!idxrec->indisenabled)=0A+=09=09=09=09= appendStringInfoString(&buf,=20"=20DISABLE");=0A=20=09}=0A=20=0A=20=09/*=20= Clean=20up=20*/=0Adiff=20--git=20a/src/backend/utils/cache/relcache.c=20= b/src/backend/utils/cache/relcache.c=0Aindex=20c326f687eb..b3b43df080=20= 100644=0A---=20a/src/backend/utils/cache/relcache.c=0A+++=20= b/src/backend/utils/cache/relcache.c=0A@@=20-2344,6=20+2344,7=20@@=20= RelationReloadIndexInfo(Relation=20relation)=0A=20=09=09= relation->rd_index->indisready=20=3D=20index->indisready;=0A=20=09=09= relation->rd_index->indislive=20=3D=20index->indislive;=0A=20=09=09= relation->rd_index->indisreplident=20=3D=20index->indisreplident;=0A+=09=09= relation->rd_index->indisenabled=20=3D=20index->indisenabled;=0A=20=0A=20= =09=09/*=20Copy=20xmin=20too,=20as=20that=20is=20needed=20to=20make=20= sense=20of=20indcheckxmin=20*/=0A=20=09=09= HeapTupleHeaderSetXmin(relation->rd_indextuple->t_data,=0Adiff=20--git=20= a/src/include/catalog/index.h=20b/src/include/catalog/index.h=0Aindex=20= 2dea96f47c..8a47cfd174=20100644=0A---=20a/src/include/catalog/index.h=0A= +++=20b/src/include/catalog/index.h=0A@@=20-65,6=20+65,7=20@@=20extern=20= void=20index_check_primary_key(Relation=20heapRel,=0A=20#define=09= INDEX_CREATE_IF_NOT_EXISTS=09=09=09(1=20<<=204)=0A=20#define=09= INDEX_CREATE_PARTITIONED=09=09=09(1=20<<=205)=0A=20#define=20= INDEX_CREATE_INVALID=09=09=09=09(1=20<<=206)=0A+#define=20= INDEX_CREATE_ENABLED=20=20=20=20=20=20=20=20(1=20<<=207)=0A=20=0A=20= extern=20Oid=09index_create(Relation=20heapRelation,=0A=20=09=09=09=09=09= =09=20const=20char=20*indexRelationName,=0A@@=20-175,7=20+176,6=20@@=20= extern=20void=20RestoreReindexState(const=20void=20*reindexstate);=0A=20=0A= =20extern=20void=20IndexSetParentIndex(Relation=20partitionIdx,=20Oid=20= parentOid);=0A=20=0A-=0A=20/*=0A=20=20*=20itemptr_encode=20-=20Encode=20= ItemPointer=20as=20int64/int8=0A=20=20*=0Adiff=20--git=20= a/src/include/catalog/pg_index.h=20b/src/include/catalog/pg_index.h=0A= index=206c89639a9e..b0c7e5f365=20100644=0A---=20= a/src/include/catalog/pg_index.h=0A+++=20= b/src/include/catalog/pg_index.h=0A@@=20-44,6=20+44,7=20@@=20= CATALOG(pg_index,2610,IndexRelationId)=20BKI_SCHEMA_MACRO=0A=20=09bool=09= =09indisready;=09=09/*=20is=20this=20index=20ready=20for=20inserts?=20*/=0A= =20=09bool=09=09indislive;=09=09/*=20is=20this=20index=20alive=20at=20= all?=20*/=0A=20=09bool=09=09indisreplident;=20/*=20is=20this=20index=20= the=20identity=20for=20replication?=20*/=0A+=09bool=09=09indisenabled;=09= /*=20is=20this=20index=20enabled=20for=20use=20by=20queries?=20*/=0A=20=0A= =20=09/*=20variable-length=20fields=20start=20here,=20but=20we=20allow=20= direct=20access=20to=20indkey=20*/=0A=20=09int2vector=09indkey=20= BKI_FORCE_NOT_NULL;=09/*=20column=20numbers=20of=20indexed=20cols,=0A= diff=20--git=20a/src/include/nodes/parsenodes.h=20= b/src/include/nodes/parsenodes.h=0Aindex=20c92cef3d16..8b85e91985=20= 100644=0A---=20a/src/include/nodes/parsenodes.h=0A+++=20= b/src/include/nodes/parsenodes.h=0A@@=20-2414,6=20+2414,8=20@@=20typedef=20= enum=20AlterTableType=0A=20=09AT_SetIdentity,=09=09=09=09/*=20SET=20= identity=20column=20options=20*/=0A=20=09AT_DropIdentity,=09=09=09/*=20= DROP=20IDENTITY=20*/=0A=20=09AT_ReAddStatistics,=09=09=09/*=20internal=20= to=20commands/tablecmds.c=20*/=0A+=09AT_EnableIndex,=09=09=09=09=09/*=20= ENABLE=20INDEX=20*/=0A+=09AT_DisableIndex,=09=09=09=09/*=20DISABLE=20= INDEX=20*/=0A=20}=20AlterTableType;=0A=20=0A=20typedef=20struct=20= ReplicaIdentityStmt=0A@@=20-3392,6=20+3394,7=20@@=20typedef=20struct=20= IndexStmt=0A=20=09bool=09=09if_not_exists;=09/*=20just=20do=20nothing=20= if=20index=20already=20exists?=20*/=0A=20=09bool=09=09= reset_default_tblspc;=09/*=20reset=20default_tablespace=20prior=20to=0A=20= =09=09=09=09=09=09=09=09=09=09=20*=20executing=20*/=0A+=20=20bool=09=09= isenabled;=09=09/*=20true=20if=20ENABLE=20(default),=20false=20if=20= DISABLE=20*/=0A=20}=20IndexStmt;=0A=20=0A=20/*=20----------------------=0A= diff=20--git=20a/src/include/nodes/pathnodes.h=20= b/src/include/nodes/pathnodes.h=0Aindex=2007e2415398..67a4d80d92=20= 100644=0A---=20a/src/include/nodes/pathnodes.h=0A+++=20= b/src/include/nodes/pathnodes.h=0A@@=20-1207,6=20+1207,8=20@@=20struct=20= IndexOptInfo=0A=20=09/*=20AM's=20cost=20estimator=20*/=0A=20=09/*=20= Rather=20than=20include=20amapi.h=20here,=20we=20declare=20= amcostestimate=20like=20this=20*/=0A=20=09void=09=09(*amcostestimate)=20= ()=20pg_node_attr(read_write_ignore);=0A+=09/*=20true=20if=20this=20= index=20is=20enabled=20*/=0A+=09bool=09=09enabled;=0A=20};=0A=20=0A=20/*=0A= diff=20--git=20a/src/test/regress/expected/create_index.out=20= b/src/test/regress/expected/create_index.out=0Aindex=20= d3358dfc39..22513ae978=20100644=0A---=20= a/src/test/regress/expected/create_index.out=0A+++=20= b/src/test/regress/expected/create_index.out=0A@@=20-2965,6=20+2965,500=20= @@=20ERROR:=20=20REINDEX=20SCHEMA=20cannot=20run=20inside=20a=20= transaction=20block=0A=20END;=0A=20--=20concurrently=0A=20REINDEX=20= SCHEMA=20CONCURRENTLY=20schema_to_reindex;=0A+--=20Test=20enable/disable=20= functionality=20for=20indexes=0A+--=20Setup=0A+CREATE=20TABLE=20= index_test(=0A+=20=20=20=20id=20INT=20PRIMARY=20KEY,=0A+=20=20=20=20data=20= TEXT,=0A+=20=20=20=20num=20INT,=0A+=20=20=20=20vector=20INT[],=0A+=20=20=20= =20range=20INT4RANGE=0A+);=0A+INSERT=20INTO=20index_test=0A+SELECT=0A+=20= =20=20=20g,=0A+=20=20=20=20'data=20'=20||=20g,=0A+=20=20=20=20g=20%=20= 100,=0A+=20=20=20=20ARRAY[g,=20g+1,=20g+2],=0A+=20=20=20=20int4range(g,=20= g+10)=0A+FROM=20generate_series(1,=201000)=20g;=0A+--=20Function=20for=20= testing=0A+CREATE=20FUNCTION=20get_data_length(text)=20RETURNS=20INT=20= AS=20$$=0A+=20=20=20=20SELECT=20length($1);=0A+$$=20LANGUAGE=20SQL=20= IMMUTABLE;=0A+--=20Helper=20function=20to=20show=20index=20status=0A= +CREATE=20OR=20REPLACE=20FUNCTION=20show_index_status(index_name=20text)=0A= +RETURNS=20TABLE=20(=0A+=20=20=20=20indexrelid=20regclass,=0A+=20=20=20=20= indisvalid=20boolean,=0A+=20=20=20=20indisready=20boolean,=0A+=20=20=20=20= indislive=20boolean,=0A+=20=20=20=20indisenabled=20boolean=0A+)=20AS=20= $$=0A+BEGIN=0A+=20=20=20=20RETURN=20QUERY=20EXECUTE=20format('=0A+=20=20=20= =20=20=20=20=20SELECT=20indexrelid::regclass,=20indisvalid,=20= indisready,=20indislive,=20indisenabled=0A+=20=20=20=20=20=20=20=20FROM=20= pg_index=0A+=20=20=20=20=20=20=20=20WHERE=20indexrelid=20=3D=20= %L::regclass',=20index_name);=0A+END;=0A+$$=20LANGUAGE=20plpgsql;=0A+--=20= Create=20and=20test=20each=20index=20type=0A+--=201.=20Basic=20= single-column=20index=0A+CREATE=20INDEX=20idx_single=20ON=20= index_test(data);=0A+SELECT=20show_index_status('idx_single');=0A+=20=20= show_index_status=20=20=20=0A+----------------------=0A+=20= (idx_single,t,t,t,t)=0A+(1=20row)=0A+=0A+EXPLAIN=20(COSTS=20OFF)=20= SELECT=20*=20FROM=20index_test=20WHERE=20data=20=3D=20'data=20500';=0A+=20= =20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20QUERY=20PLAN=20=20=20=20= =20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=0A= +-----------------------------------------------=0A+=20Bitmap=20Heap=20= Scan=20on=20index_test=0A+=20=20=20Recheck=20Cond:=20(data=20=3D=20'data=20= 500'::text)=0A+=20=20=20->=20=20Bitmap=20Index=20Scan=20on=20idx_single=0A= +=20=20=20=20=20=20=20=20=20Index=20Cond:=20(data=20=3D=20'data=20= 500'::text)=0A+(4=20rows)=0A+=0A+ALTER=20INDEX=20idx_single=20DISABLE;=0A= +SELECT=20show_index_status('idx_single');=0A+=20=20show_index_status=20=20= =20=0A+----------------------=0A+=20(idx_single,t,t,t,f)=0A+(1=20row)=0A= +=0A+EXPLAIN=20(COSTS=20OFF)=20SELECT=20*=20FROM=20index_test=20WHERE=20= data=20=3D=20'data=20500';=0A+=20=20=20=20=20=20=20=20=20=20=20=20=20= QUERY=20PLAN=20=20=20=20=20=20=20=20=20=20=20=20=20=20=0A= +-------------------------------------=0A+=20Seq=20Scan=20on=20= index_test=0A+=20=20=20Filter:=20(data=20=3D=20'data=20500'::text)=0A+(2=20= rows)=0A+=0A+ALTER=20INDEX=20idx_single=20ENABLE;=0A+SELECT=20= show_index_status('idx_single');=0A+=20=20show_index_status=20=20=20=0A= +----------------------=0A+=20(idx_single,t,t,t,t)=0A+(1=20row)=0A+=0A= +--=202.=20Multi-column=20index=0A+CREATE=20INDEX=20idx_multi=20ON=20= index_test(num,=20data);=0A+SELECT=20show_index_status('idx_multi');=0A+=20= =20show_index_status=20=20=0A+---------------------=0A+=20= (idx_multi,t,t,t,t)=0A+(1=20row)=0A+=0A+EXPLAIN=20(COSTS=20OFF)=20SELECT=20= *=20FROM=20index_test=20WHERE=20num=20=3D=2050=20AND=20data=20>=20'data=20= 500';=0A+=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20= =20=20=20=20=20QUERY=20PLAN=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20= =20=20=20=20=20=20=20=20=20=20=20=0A= +----------------------------------------------------------------=0A+=20= Bitmap=20Heap=20Scan=20on=20index_test=0A+=20=20=20Recheck=20Cond:=20= ((num=20=3D=2050)=20AND=20(data=20>=20'data=20500'::text))=0A+=20=20=20= ->=20=20Bitmap=20Index=20Scan=20on=20idx_multi=0A+=20=20=20=20=20=20=20=20= =20Index=20Cond:=20((num=20=3D=2050)=20AND=20(data=20>=20'data=20= 500'::text))=0A+(4=20rows)=0A+=0A+ALTER=20INDEX=20idx_multi=20DISABLE;=0A= +SELECT=20show_index_status('idx_multi');=0A+=20=20show_index_status=20=20= =0A+---------------------=0A+=20(idx_multi,t,t,t,f)=0A+(1=20row)=0A+=0A= +EXPLAIN=20(COSTS=20OFF)=20SELECT=20*=20FROM=20index_test=20WHERE=20num=20= =3D=2050=20AND=20data=20>=20'data=20500';=0A+=20=20=20=20=20=20=20=20=20=20= =20=20=20=20=20=20=20=20=20=20=20=20QUERY=20PLAN=20=20=20=20=20=20=20=20=20= =20=20=20=20=20=20=20=20=20=20=20=20=20=0A= +------------------------------------------------------=0A+=20Seq=20Scan=20= on=20index_test=0A+=20=20=20Filter:=20((data=20>=20'data=20500'::text)=20= AND=20(num=20=3D=2050))=0A+(2=20rows)=0A+=0A+ALTER=20INDEX=20idx_multi=20= ENABLE;=0A+SELECT=20show_index_status('idx_multi');=0A+=20=20= show_index_status=20=20=0A+---------------------=0A+=20= (idx_multi,t,t,t,t)=0A+(1=20row)=0A+=0A+--=203.=20Partial=20index=0A= +CREATE=20INDEX=20idx_partial=20ON=20index_test(num)=20WHERE=20num=20<=20= 50;=0A+SELECT=20show_index_status('idx_partial');=0A+=20=20=20= show_index_status=20=20=20=0A+-----------------------=0A+=20= (idx_partial,t,t,t,t)=0A+(1=20row)=0A+=0A+EXPLAIN=20(COSTS=20OFF)=20= SELECT=20*=20FROM=20index_test=20WHERE=20num=20=3D=2025;=0A+=20=20=20=20=20= =20=20=20=20=20=20=20=20=20=20QUERY=20PLAN=20=20=20=20=20=20=20=20=20=20=20= =20=20=20=20=0A+----------------------------------------=0A+=20Bitmap=20= Heap=20Scan=20on=20index_test=0A+=20=20=20Recheck=20Cond:=20(num=20=3D=20= 25)=0A+=20=20=20->=20=20Bitmap=20Index=20Scan=20on=20idx_partial=0A+=20=20= =20=20=20=20=20=20=20Index=20Cond:=20(num=20=3D=2025)=0A+(4=20rows)=0A+=0A= +ALTER=20INDEX=20idx_partial=20DISABLE;=0A+SELECT=20= show_index_status('idx_partial');=0A+=20=20=20show_index_status=20=20=20=0A= +-----------------------=0A+=20(idx_partial,t,t,t,f)=0A+(1=20row)=0A+=0A= +EXPLAIN=20(COSTS=20OFF)=20SELECT=20*=20FROM=20index_test=20WHERE=20num=20= =3D=2025;=0A+=20=20=20=20=20=20=20=20=20=20=20=20=20=20QUERY=20PLAN=20=20= =20=20=20=20=20=20=20=20=20=20=20=20=0A= +--------------------------------------=0A+=20Bitmap=20Heap=20Scan=20on=20= index_test=0A+=20=20=20Recheck=20Cond:=20(num=20=3D=2025)=0A+=20=20=20->=20= =20Bitmap=20Index=20Scan=20on=20idx_multi=0A+=20=20=20=20=20=20=20=20=20= Index=20Cond:=20(num=20=3D=2025)=0A+(4=20rows)=0A+=0A+ALTER=20INDEX=20= idx_partial=20ENABLE;=0A+SELECT=20show_index_status('idx_partial');=0A+=20= =20=20show_index_status=20=20=20=0A+-----------------------=0A+=20= (idx_partial,t,t,t,t)=0A+(1=20row)=0A+=0A+--=204.=20Expression=20index=0A= +CREATE=20INDEX=20idx_expression=20ON=20index_test((lower(data)));=0A= +SELECT=20show_index_status('idx_expression');=0A+=20=20=20=20= show_index_status=20=20=20=20=20=0A+--------------------------=0A+=20= (idx_expression,t,t,t,t)=0A+(1=20row)=0A+=0A+EXPLAIN=20(COSTS=20OFF)=20= SELECT=20*=20FROM=20index_test=20WHERE=20lower(data)=20=3D=20'data=20= 500';=0A+=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20= QUERY=20PLAN=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20= =20=0A+------------------------------------------------------=0A+=20= Bitmap=20Heap=20Scan=20on=20index_test=0A+=20=20=20Recheck=20Cond:=20= (lower(data)=20=3D=20'data=20500'::text)=0A+=20=20=20->=20=20Bitmap=20= Index=20Scan=20on=20idx_expression=0A+=20=20=20=20=20=20=20=20=20Index=20= Cond:=20(lower(data)=20=3D=20'data=20500'::text)=0A+(4=20rows)=0A+=0A= +ALTER=20INDEX=20idx_expression=20DISABLE;=0A+SELECT=20= show_index_status('idx_expression');=0A+=20=20=20=20show_index_status=20=20= =20=20=20=0A+--------------------------=0A+=20(idx_expression,t,t,t,f)=0A= +(1=20row)=0A+=0A+EXPLAIN=20(COSTS=20OFF)=20SELECT=20*=20FROM=20= index_test=20WHERE=20lower(data)=20=3D=20'data=20500';=0A+=20=20=20=20=20= =20=20=20=20=20=20=20=20=20=20=20=20QUERY=20PLAN=20=20=20=20=20=20=20=20=20= =20=20=20=20=20=20=20=20=0A+--------------------------------------------=0A= +=20Seq=20Scan=20on=20index_test=0A+=20=20=20Filter:=20(lower(data)=20=3D=20= 'data=20500'::text)=0A+(2=20rows)=0A+=0A+ALTER=20INDEX=20idx_expression=20= ENABLE;=0A+SELECT=20show_index_status('idx_expression');=0A+=20=20=20=20= show_index_status=20=20=20=20=20=0A+--------------------------=0A+=20= (idx_expression,t,t,t,t)=0A+(1=20row)=0A+=0A+--=205.=20GIN=20index=0A= +CREATE=20INDEX=20idx_gin=20ON=20index_test=20USING=20gin(vector);=0A= +SELECT=20show_index_status('idx_gin');=0A+=20show_index_status=20=0A= +-------------------=0A+=20(idx_gin,t,t,t,t)=0A+(1=20row)=0A+=0A+EXPLAIN=20= (COSTS=20OFF)=20SELECT=20*=20FROM=20index_test=20WHERE=20vector=20@>=20= ARRAY[500];=0A+=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20= =20QUERY=20PLAN=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20= =20=0A+----------------------------------------------------=0A+=20Bitmap=20= Heap=20Scan=20on=20index_test=0A+=20=20=20Recheck=20Cond:=20(vector=20@>=20= '{500}'::integer[])=0A+=20=20=20->=20=20Bitmap=20Index=20Scan=20on=20= idx_gin=0A+=20=20=20=20=20=20=20=20=20Index=20Cond:=20(vector=20@>=20= '{500}'::integer[])=0A+(4=20rows)=0A+=0A+ALTER=20INDEX=20idx_gin=20= DISABLE;=0A+SELECT=20show_index_status('idx_gin');=0A+=20= show_index_status=20=0A+-------------------=0A+=20(idx_gin,t,t,t,f)=0A= +(1=20row)=0A+=0A+EXPLAIN=20(COSTS=20OFF)=20SELECT=20*=20FROM=20= index_test=20WHERE=20vector=20@>=20ARRAY[500];=0A+=20=20=20=20=20=20=20=20= =20=20=20=20=20=20=20=20QUERY=20PLAN=20=20=20=20=20=20=20=20=20=20=20=20=20= =20=20=20=0A+------------------------------------------=0A+=20Seq=20Scan=20= on=20index_test=0A+=20=20=20Filter:=20(vector=20@>=20'{500}'::integer[])=0A= +(2=20rows)=0A+=0A+ALTER=20INDEX=20idx_gin=20ENABLE;=0A+SELECT=20= show_index_status('idx_gin');=0A+=20show_index_status=20=0A= +-------------------=0A+=20(idx_gin,t,t,t,t)=0A+(1=20row)=0A+=0A+--=206.=20= GiST=20index=0A+CREATE=20INDEX=20idx_gist=20ON=20index_test=20USING=20= gist(range);=0A+SELECT=20show_index_status('idx_gist');=0A+=20= show_index_status=20=20=0A+--------------------=0A+=20(idx_gist,t,t,t,t)=0A= +(1=20row)=0A+=0A+EXPLAIN=20(COSTS=20OFF)=20SELECT=20*=20FROM=20= index_test=20WHERE=20range=20&&=20int4range(100,=20110);=0A+=20=20=20=20=20= =20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20QUERY=20PLAN=20=20=20=20= =20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=0A= +-------------------------------------------------------=0A+=20Bitmap=20= Heap=20Scan=20on=20index_test=0A+=20=20=20Recheck=20Cond:=20(range=20&&=20= '[100,110)'::int4range)=0A+=20=20=20->=20=20Bitmap=20Index=20Scan=20on=20= idx_gist=0A+=20=20=20=20=20=20=20=20=20Index=20Cond:=20(range=20&&=20= '[100,110)'::int4range)=0A+(4=20rows)=0A+=0A+ALTER=20INDEX=20idx_gist=20= DISABLE;=0A+SELECT=20show_index_status('idx_gist');=0A+=20= show_index_status=20=20=0A+--------------------=0A+=20(idx_gist,t,t,t,f)=0A= +(1=20row)=0A+=0A+EXPLAIN=20(COSTS=20OFF)=20SELECT=20*=20FROM=20= index_test=20WHERE=20range=20&&=20int4range(100,=20110);=0A+=20=20=20=20=20= =20=20=20=20=20=20=20=20=20=20=20=20QUERY=20PLAN=20=20=20=20=20=20=20=20=20= =20=20=20=20=20=20=20=20=20=0A= +---------------------------------------------=0A+=20Seq=20Scan=20on=20= index_test=0A+=20=20=20Filter:=20(range=20&&=20'[100,110)'::int4range)=0A= +(2=20rows)=0A+=0A+ALTER=20INDEX=20idx_gist=20ENABLE;=0A+SELECT=20= show_index_status('idx_gist');=0A+=20show_index_status=20=20=0A= +--------------------=0A+=20(idx_gist,t,t,t,t)=0A+(1=20row)=0A+=0A+--=20= 7.=20Covering=20index=0A+CREATE=20INDEX=20idx_covering=20ON=20= index_test(num)=20INCLUDE=20(data);=0A+SELECT=20= show_index_status('idx_covering');=0A+=20=20=20show_index_status=20=20=20= =20=0A+------------------------=0A+=20(idx_covering,t,t,t,t)=0A+(1=20= row)=0A+=0A+EXPLAIN=20(COSTS=20OFF)=20SELECT=20num,=20data=20FROM=20= index_test=20WHERE=20num=20=3D=2050;=0A+=20=20=20=20=20=20=20=20=20=20=20= =20=20=20=20QUERY=20PLAN=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=0A= +-----------------------------------------=0A+=20Bitmap=20Heap=20Scan=20= on=20index_test=0A+=20=20=20Recheck=20Cond:=20(num=20=3D=2050)=0A+=20=20=20= ->=20=20Bitmap=20Index=20Scan=20on=20idx_covering=0A+=20=20=20=20=20=20=20= =20=20Index=20Cond:=20(num=20=3D=2050)=0A+(4=20rows)=0A+=0A+ALTER=20= INDEX=20idx_covering=20DISABLE;=0A+SELECT=20= show_index_status('idx_covering');=0A+=20=20=20show_index_status=20=20=20= =20=0A+------------------------=0A+=20(idx_covering,t,t,t,f)=0A+(1=20= row)=0A+=0A+EXPLAIN=20(COSTS=20OFF)=20SELECT=20num,=20data=20FROM=20= index_test=20WHERE=20num=20=3D=2050;=0A+=20=20=20=20=20=20=20=20=20=20=20= =20=20=20QUERY=20PLAN=20=20=20=20=20=20=20=20=20=20=20=20=20=20=0A= +--------------------------------------=0A+=20Bitmap=20Heap=20Scan=20on=20= index_test=0A+=20=20=20Recheck=20Cond:=20(num=20=3D=2050)=0A+=20=20=20->=20= =20Bitmap=20Index=20Scan=20on=20idx_multi=0A+=20=20=20=20=20=20=20=20=20= Index=20Cond:=20(num=20=3D=2050)=0A+(4=20rows)=0A+=0A+ALTER=20INDEX=20= idx_covering=20ENABLE;=0A+SELECT=20show_index_status('idx_covering');=0A= +=20=20=20show_index_status=20=20=20=20=0A+------------------------=0A+=20= (idx_covering,t,t,t,t)=0A+(1=20row)=0A+=0A+--=208.=20Unique=20index=0A= +CREATE=20UNIQUE=20INDEX=20idx_unique=20ON=20index_test(id,=20data);=0A= +SELECT=20show_index_status('idx_unique');=0A+=20=20show_index_status=20=20= =20=0A+----------------------=0A+=20(idx_unique,t,t,t,t)=0A+(1=20row)=0A= +=0A+EXPLAIN=20(COSTS=20OFF)=20SELECT=20*=20FROM=20index_test=20WHERE=20= id=20=3D=20500=20AND=20data=20=3D=20'data=20500';=0A+=20=20=20=20=20=20=20= =20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20QUERY=20PLAN=20=20=20=20= =20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=0A= +----------------------------------------------------------=0A+=20Index=20= Scan=20using=20idx_unique=20on=20index_test=0A+=20=20=20Index=20Cond:=20= ((id=20=3D=20500)=20AND=20(data=20=3D=20'data=20500'::text))=0A+(2=20= rows)=0A+=0A+ALTER=20INDEX=20idx_unique=20DISABLE;=0A+SELECT=20= show_index_status('idx_unique');=0A+=20=20show_index_status=20=20=20=0A= +----------------------=0A+=20(idx_unique,t,t,t,f)=0A+(1=20row)=0A+=0A= +EXPLAIN=20(COSTS=20OFF)=20SELECT=20*=20FROM=20index_test=20WHERE=20id=20= =3D=20500=20AND=20data=20=3D=20'data=20500';=0A+=20=20=20=20=20=20=20=20=20= =20=20=20=20=20=20=20=20=20=20QUERY=20PLAN=20=20=20=20=20=20=20=20=20=20=20= =20=20=20=20=20=20=20=20=0A= +------------------------------------------------=0A+=20Index=20Scan=20= using=20index_test_pkey=20on=20index_test=0A+=20=20=20Index=20Cond:=20= (id=20=3D=20500)=0A+=20=20=20Filter:=20(data=20=3D=20'data=20500'::text)=0A= +(3=20rows)=0A+=0A+ALTER=20INDEX=20idx_unique=20ENABLE;=0A+SELECT=20= show_index_status('idx_unique');=0A+=20=20show_index_status=20=20=20=0A= +----------------------=0A+=20(idx_unique,t,t,t,t)=0A+(1=20row)=0A+=0A= +--=209.=20Function-based=20index=0A+CREATE=20INDEX=20idx_func=20ON=20= index_test(get_data_length(data));=0A+SELECT=20= show_index_status('idx_func');=0A+=20show_index_status=20=20=0A= +--------------------=0A+=20(idx_func,t,t,t,t)=0A+(1=20row)=0A+=0A= +EXPLAIN=20(COSTS=20OFF)=20SELECT=20*=20FROM=20index_test=20WHERE=20= get_data_length(data)=20=3D=2010;=0A+=20=20=20=20=20=20=20=20=20=20=20=20= =20=20=20QUERY=20PLAN=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=0A= +-----------------------------------------=0A+=20Bitmap=20Heap=20Scan=20= on=20index_test=0A+=20=20=20Recheck=20Cond:=20(length(data)=20=3D=2010)=0A= +=20=20=20->=20=20Bitmap=20Index=20Scan=20on=20idx_func=0A+=20=20=20=20=20= =20=20=20=20Index=20Cond:=20(length(data)=20=3D=2010)=0A+(4=20rows)=0A+=0A= +ALTER=20INDEX=20idx_func=20DISABLE;=0A+SELECT=20= show_index_status('idx_func');=0A+=20show_index_status=20=20=0A= +--------------------=0A+=20(idx_func,t,t,t,f)=0A+(1=20row)=0A+=0A= +EXPLAIN=20(COSTS=20OFF)=20SELECT=20*=20FROM=20index_test=20WHERE=20= get_data_length(data)=20=3D=2010;=0A+=20=20=20=20=20=20=20=20=20=20QUERY=20= PLAN=20=20=20=20=20=20=20=20=20=20=20=0A+-------------------------------=0A= +=20Seq=20Scan=20on=20index_test=0A+=20=20=20Filter:=20(length(data)=20=3D= =2010)=0A+(2=20rows)=0A+=0A+ALTER=20INDEX=20idx_func=20ENABLE;=0A+SELECT=20= show_index_status('idx_func');=0A+=20show_index_status=20=20=0A= +--------------------=0A+=20(idx_func,t,t,t,t)=0A+(1=20row)=0A+=0A+--=20= 10.=20Join=20index=0A+CREATE=20TABLE=20join_test=20(id=20INT=20PRIMARY=20= KEY,=20ref_id=20INT);=0A+INSERT=20INTO=20join_test=20SELECT=20g,=20g=20%=20= 100=20FROM=20generate_series(1,=201000)=20g;=0A+CREATE=20INDEX=20= idx_join=20ON=20join_test(ref_id);=0A+SELECT=20= show_index_status('idx_join');=0A+=20show_index_status=20=20=0A= +--------------------=0A+=20(idx_join,t,t,t,t)=0A+(1=20row)=0A+=0A= +EXPLAIN=20(COSTS=20OFF)=0A+SELECT=20jt.id,=20it.data=0A+FROM=20= join_test=20jt=0A+JOIN=20index_test=20it=20ON=20jt.ref_id=20=3D=20it.num=0A= +WHERE=20jt.id=20BETWEEN=20100=20AND=20200;=0A+=20=20=20=20=20=20=20=20=20= =20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20QUERY=20PLAN=20=20=20=20= =20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=0A= +---------------------------------------------------------------=0A+=20= Hash=20Join=0A+=20=20=20Hash=20Cond:=20(it.num=20=3D=20jt.ref_id)=0A+=20=20= =20->=20=20Seq=20Scan=20on=20index_test=20it=0A+=20=20=20->=20=20Hash=0A= +=20=20=20=20=20=20=20=20=20->=20=20Bitmap=20Heap=20Scan=20on=20= join_test=20jt=0A+=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20Recheck=20= Cond:=20((id=20>=3D=20100)=20AND=20(id=20<=3D=20200))=0A+=20=20=20=20=20=20= =20=20=20=20=20=20=20=20=20->=20=20Bitmap=20Index=20Scan=20on=20= join_test_pkey=0A+=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20= =20=20Index=20Cond:=20((id=20>=3D=20100)=20AND=20(id=20<=3D=20200))=0A= +(8=20rows)=0A+=0A+--=20Disable=20all=20indexes=20to=20force=20seq=20= scan=0A+ALTER=20INDEX=20idx_join=20DISABLE;=0A+ALTER=20INDEX=20= join_test_pkey=20DISABLE;=0A+SELECT=20show_index_status('idx_join');=0A+=20= show_index_status=20=20=0A+--------------------=0A+=20(idx_join,t,t,t,f)=0A= +(1=20row)=0A+=0A+SELECT=20show_index_status('join_test_pkey');=0A+=20=20= =20=20show_index_status=20=20=20=20=20=0A+--------------------------=0A+=20= (join_test_pkey,t,t,t,f)=0A+(1=20row)=0A+=0A+EXPLAIN=20(COSTS=20OFF)=0A= +SELECT=20jt.id,=20it.data=0A+FROM=20join_test=20jt=0A+JOIN=20index_test=20= it=20ON=20jt.ref_id=20=3D=20it.num=0A+WHERE=20jt.id=20BETWEEN=20100=20= AND=20200;=0A+=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20= =20QUERY=20PLAN=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20= =20=20=0A+-----------------------------------------------------=0A+=20= Hash=20Join=0A+=20=20=20Hash=20Cond:=20(it.num=20=3D=20jt.ref_id)=0A+=20=20= =20->=20=20Seq=20Scan=20on=20index_test=20it=0A+=20=20=20->=20=20Hash=0A= +=20=20=20=20=20=20=20=20=20->=20=20Seq=20Scan=20on=20join_test=20jt=0A+=20= =20=20=20=20=20=20=20=20=20=20=20=20=20=20Filter:=20((id=20>=3D=20100)=20= AND=20(id=20<=3D=20200))=0A+(6=20rows)=0A+=0A+ALTER=20INDEX=20idx_join=20= ENABLE;=0A+ALTER=20INDEX=20join_test_pkey=20ENABLE;=0A+SELECT=20= show_index_status('idx_join');=0A+=20show_index_status=20=20=0A= +--------------------=0A+=20(idx_join,t,t,t,t)=0A+(1=20row)=0A+=0A= +SELECT=20show_index_status('join_test_pkey');=0A+=20=20=20=20= show_index_status=20=20=20=20=20=0A+--------------------------=0A+=20= (join_test_pkey,t,t,t,t)=0A+(1=20row)=0A+=0A+--=20Test=20REINDEX=20= CONCURRENTLY=0A+REINDEX=20INDEX=20CONCURRENTLY=20idx_join;=0A+SELECT=20= show_index_status('idx_join');=0A+=20show_index_status=20=20=0A= +--------------------=0A+=20(idx_join,t,t,t,t)=0A+(1=20row)=0A+=0A+ALTER=20= INDEX=20idx_join=20DISABLE;=0A+SELECT=20show_index_status('idx_join');=0A= +=20show_index_status=20=20=0A+--------------------=0A+=20= (idx_join,t,t,t,f)=0A+(1=20row)=0A+=0A+REINDEX=20INDEX=20CONCURRENTLY=20= idx_join;=0A+SELECT=20show_index_status('idx_join');=0A+=20= show_index_status=20=20=0A+--------------------=0A+=20(idx_join,t,t,t,f)=0A= +(1=20row)=0A+=0A+SELECT=20pg_get_indexdef('idx_join'::regclass);=0A+=20=20= =20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20= =20=20=20=20=20=20=20pg_get_indexdef=20=20=20=20=20=20=20=20=20=20=20=20=20= =20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=0A= +-------------------------------------------------------------------------= ----------=0A+=20CREATE=20INDEX=20idx_join=20ON=20= schema_to_reindex.join_test=20USING=20btree=20(ref_id)=20DISABLE=0A+(1=20= row)=0A+=0A+ALTER=20INDEX=20idx_join=20ENABLE;=0A+SELECT=20= pg_get_indexdef('idx_join'::regclass);=0A+=20=20=20=20=20=20=20=20=20=20=20= =20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20pg_get_indexdef=20= =20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20= =20=20=20=20=0A= +-------------------------------------------------------------------------= --=0A+=20CREATE=20INDEX=20idx_join=20ON=20schema_to_reindex.join_test=20= USING=20btree=20(ref_id)=0A+(1=20row)=0A+=0A+--=20Clean=20up=0A+DROP=20= TABLE=20index_test;=0A+DROP=20TABLE=20join_test;=0A+DROP=20FUNCTION=20= get_data_length;=0A+DROP=20FUNCTION=20show_index_status;=0A=20--=20= Failure=20for=20unauthorized=20user=0A=20CREATE=20ROLE=20= regress_reindexuser=20NOLOGIN;=0A=20SET=20SESSION=20ROLE=20= regress_reindexuser;=0Adiff=20--git=20= a/src/test/regress/sql/create_index.sql=20= b/src/test/regress/sql/create_index.sql=0Aindex=20fe162cc7c3..db547898aa=20= 100644=0A---=20a/src/test/regress/sql/create_index.sql=0A+++=20= b/src/test/regress/sql/create_index.sql=0A@@=20-1297,6=20+1297,187=20@@=20= END;=0A=20--=20concurrently=0A=20REINDEX=20SCHEMA=20CONCURRENTLY=20= schema_to_reindex;=0A=20=0A+--=20Test=20enable/disable=20functionality=20= for=20indexes=0A+=0A+--=20Setup=0A+CREATE=20TABLE=20index_test(=0A+=20=20= =20=20id=20INT=20PRIMARY=20KEY,=0A+=20=20=20=20data=20TEXT,=0A+=20=20=20=20= num=20INT,=0A+=20=20=20=20vector=20INT[],=0A+=20=20=20=20range=20= INT4RANGE=0A+);=0A+=0A+INSERT=20INTO=20index_test=0A+SELECT=0A+=20=20=20=20= g,=0A+=20=20=20=20'data=20'=20||=20g,=0A+=20=20=20=20g=20%=20100,=0A+=20=20= =20=20ARRAY[g,=20g+1,=20g+2],=0A+=20=20=20=20int4range(g,=20g+10)=0A= +FROM=20generate_series(1,=201000)=20g;=0A+=0A+--=20Function=20for=20= testing=0A+CREATE=20FUNCTION=20get_data_length(text)=20RETURNS=20INT=20= AS=20$$=0A+=20=20=20=20SELECT=20length($1);=0A+$$=20LANGUAGE=20SQL=20= IMMUTABLE;=0A+=0A+--=20Helper=20function=20to=20show=20index=20status=0A= +CREATE=20OR=20REPLACE=20FUNCTION=20show_index_status(index_name=20text)=0A= +RETURNS=20TABLE=20(=0A+=20=20=20=20indexrelid=20regclass,=0A+=20=20=20=20= indisvalid=20boolean,=0A+=20=20=20=20indisready=20boolean,=0A+=20=20=20=20= indislive=20boolean,=0A+=20=20=20=20indisenabled=20boolean=0A+)=20AS=20= $$=0A+BEGIN=0A+=20=20=20=20RETURN=20QUERY=20EXECUTE=20format('=0A+=20=20=20= =20=20=20=20=20SELECT=20indexrelid::regclass,=20indisvalid,=20= indisready,=20indislive,=20indisenabled=0A+=20=20=20=20=20=20=20=20FROM=20= pg_index=0A+=20=20=20=20=20=20=20=20WHERE=20indexrelid=20=3D=20= %L::regclass',=20index_name);=0A+END;=0A+$$=20LANGUAGE=20plpgsql;=0A+=0A= +--=20Create=20and=20test=20each=20index=20type=0A+--=201.=20Basic=20= single-column=20index=0A+CREATE=20INDEX=20idx_single=20ON=20= index_test(data);=0A+SELECT=20show_index_status('idx_single');=0A= +EXPLAIN=20(COSTS=20OFF)=20SELECT=20*=20FROM=20index_test=20WHERE=20data=20= =3D=20'data=20500';=0A+ALTER=20INDEX=20idx_single=20DISABLE;=0A+SELECT=20= show_index_status('idx_single');=0A+EXPLAIN=20(COSTS=20OFF)=20SELECT=20*=20= FROM=20index_test=20WHERE=20data=20=3D=20'data=20500';=0A+ALTER=20INDEX=20= idx_single=20ENABLE;=0A+SELECT=20show_index_status('idx_single');=0A+=0A= +--=202.=20Multi-column=20index=0A+CREATE=20INDEX=20idx_multi=20ON=20= index_test(num,=20data);=0A+SELECT=20show_index_status('idx_multi');=0A= +EXPLAIN=20(COSTS=20OFF)=20SELECT=20*=20FROM=20index_test=20WHERE=20num=20= =3D=2050=20AND=20data=20>=20'data=20500';=0A+ALTER=20INDEX=20idx_multi=20= DISABLE;=0A+SELECT=20show_index_status('idx_multi');=0A+EXPLAIN=20(COSTS=20= OFF)=20SELECT=20*=20FROM=20index_test=20WHERE=20num=20=3D=2050=20AND=20= data=20>=20'data=20500';=0A+ALTER=20INDEX=20idx_multi=20ENABLE;=0A= +SELECT=20show_index_status('idx_multi');=0A+=0A+--=203.=20Partial=20= index=0A+CREATE=20INDEX=20idx_partial=20ON=20index_test(num)=20WHERE=20= num=20<=2050;=0A+SELECT=20show_index_status('idx_partial');=0A+EXPLAIN=20= (COSTS=20OFF)=20SELECT=20*=20FROM=20index_test=20WHERE=20num=20=3D=2025;=0A= +ALTER=20INDEX=20idx_partial=20DISABLE;=0A+SELECT=20= show_index_status('idx_partial');=0A+EXPLAIN=20(COSTS=20OFF)=20SELECT=20= *=20FROM=20index_test=20WHERE=20num=20=3D=2025;=0A+ALTER=20INDEX=20= idx_partial=20ENABLE;=0A+SELECT=20show_index_status('idx_partial');=0A+=0A= +--=204.=20Expression=20index=0A+CREATE=20INDEX=20idx_expression=20ON=20= index_test((lower(data)));=0A+SELECT=20= show_index_status('idx_expression');=0A+EXPLAIN=20(COSTS=20OFF)=20SELECT=20= *=20FROM=20index_test=20WHERE=20lower(data)=20=3D=20'data=20500';=0A= +ALTER=20INDEX=20idx_expression=20DISABLE;=0A+SELECT=20= show_index_status('idx_expression');=0A+EXPLAIN=20(COSTS=20OFF)=20SELECT=20= *=20FROM=20index_test=20WHERE=20lower(data)=20=3D=20'data=20500';=0A= +ALTER=20INDEX=20idx_expression=20ENABLE;=0A+SELECT=20= show_index_status('idx_expression');=0A+=0A+--=205.=20GIN=20index=0A= +CREATE=20INDEX=20idx_gin=20ON=20index_test=20USING=20gin(vector);=0A= +SELECT=20show_index_status('idx_gin');=0A+EXPLAIN=20(COSTS=20OFF)=20= SELECT=20*=20FROM=20index_test=20WHERE=20vector=20@>=20ARRAY[500];=0A= +ALTER=20INDEX=20idx_gin=20DISABLE;=0A+SELECT=20= show_index_status('idx_gin');=0A+EXPLAIN=20(COSTS=20OFF)=20SELECT=20*=20= FROM=20index_test=20WHERE=20vector=20@>=20ARRAY[500];=0A+ALTER=20INDEX=20= idx_gin=20ENABLE;=0A+SELECT=20show_index_status('idx_gin');=0A+=0A+--=20= 6.=20GiST=20index=0A+CREATE=20INDEX=20idx_gist=20ON=20index_test=20USING=20= gist(range);=0A+SELECT=20show_index_status('idx_gist');=0A+EXPLAIN=20= (COSTS=20OFF)=20SELECT=20*=20FROM=20index_test=20WHERE=20range=20&&=20= int4range(100,=20110);=0A+ALTER=20INDEX=20idx_gist=20DISABLE;=0A+SELECT=20= show_index_status('idx_gist');=0A+EXPLAIN=20(COSTS=20OFF)=20SELECT=20*=20= FROM=20index_test=20WHERE=20range=20&&=20int4range(100,=20110);=0A+ALTER=20= INDEX=20idx_gist=20ENABLE;=0A+SELECT=20show_index_status('idx_gist');=0A= +=0A+--=207.=20Covering=20index=0A+CREATE=20INDEX=20idx_covering=20ON=20= index_test(num)=20INCLUDE=20(data);=0A+SELECT=20= show_index_status('idx_covering');=0A+EXPLAIN=20(COSTS=20OFF)=20SELECT=20= num,=20data=20FROM=20index_test=20WHERE=20num=20=3D=2050;=0A+ALTER=20= INDEX=20idx_covering=20DISABLE;=0A+SELECT=20= show_index_status('idx_covering');=0A+EXPLAIN=20(COSTS=20OFF)=20SELECT=20= num,=20data=20FROM=20index_test=20WHERE=20num=20=3D=2050;=0A+ALTER=20= INDEX=20idx_covering=20ENABLE;=0A+SELECT=20= show_index_status('idx_covering');=0A+=0A+--=208.=20Unique=20index=0A= +CREATE=20UNIQUE=20INDEX=20idx_unique=20ON=20index_test(id,=20data);=0A= +SELECT=20show_index_status('idx_unique');=0A+EXPLAIN=20(COSTS=20OFF)=20= SELECT=20*=20FROM=20index_test=20WHERE=20id=20=3D=20500=20AND=20data=20=3D= =20'data=20500';=0A+ALTER=20INDEX=20idx_unique=20DISABLE;=0A+SELECT=20= show_index_status('idx_unique');=0A+EXPLAIN=20(COSTS=20OFF)=20SELECT=20*=20= FROM=20index_test=20WHERE=20id=20=3D=20500=20AND=20data=20=3D=20'data=20= 500';=0A+ALTER=20INDEX=20idx_unique=20ENABLE;=0A+SELECT=20= show_index_status('idx_unique');=0A+=0A+--=209.=20Function-based=20index=0A= +CREATE=20INDEX=20idx_func=20ON=20index_test(get_data_length(data));=0A= +SELECT=20show_index_status('idx_func');=0A+EXPLAIN=20(COSTS=20OFF)=20= SELECT=20*=20FROM=20index_test=20WHERE=20get_data_length(data)=20=3D=20= 10;=0A+ALTER=20INDEX=20idx_func=20DISABLE;=0A+SELECT=20= show_index_status('idx_func');=0A+EXPLAIN=20(COSTS=20OFF)=20SELECT=20*=20= FROM=20index_test=20WHERE=20get_data_length(data)=20=3D=2010;=0A+ALTER=20= INDEX=20idx_func=20ENABLE;=0A+SELECT=20show_index_status('idx_func');=0A= +=0A+--=2010.=20Join=20index=0A+CREATE=20TABLE=20join_test=20(id=20INT=20= PRIMARY=20KEY,=20ref_id=20INT);=0A+INSERT=20INTO=20join_test=20SELECT=20= g,=20g=20%=20100=20FROM=20generate_series(1,=201000)=20g;=0A+CREATE=20= INDEX=20idx_join=20ON=20join_test(ref_id);=0A+SELECT=20= show_index_status('idx_join');=0A+=0A+EXPLAIN=20(COSTS=20OFF)=0A+SELECT=20= jt.id,=20it.data=0A+FROM=20join_test=20jt=0A+JOIN=20index_test=20it=20ON=20= jt.ref_id=20=3D=20it.num=0A+WHERE=20jt.id=20BETWEEN=20100=20AND=20200;=0A= +=0A+--=20Disable=20all=20indexes=20to=20force=20seq=20scan=0A+ALTER=20= INDEX=20idx_join=20DISABLE;=0A+ALTER=20INDEX=20join_test_pkey=20DISABLE;=0A= +SELECT=20show_index_status('idx_join');=0A+SELECT=20= show_index_status('join_test_pkey');=0A+=0A+EXPLAIN=20(COSTS=20OFF)=0A= +SELECT=20jt.id,=20it.data=0A+FROM=20join_test=20jt=0A+JOIN=20index_test=20= it=20ON=20jt.ref_id=20=3D=20it.num=0A+WHERE=20jt.id=20BETWEEN=20100=20= AND=20200;=0A+=0A+ALTER=20INDEX=20idx_join=20ENABLE;=0A+ALTER=20INDEX=20= join_test_pkey=20ENABLE;=0A+SELECT=20show_index_status('idx_join');=0A= +SELECT=20show_index_status('join_test_pkey');=0A+=0A+--=20Test=20= REINDEX=20CONCURRENTLY=0A+REINDEX=20INDEX=20CONCURRENTLY=20idx_join;=0A= +SELECT=20show_index_status('idx_join');=0A+=0A+ALTER=20INDEX=20idx_join=20= DISABLE;=0A+SELECT=20show_index_status('idx_join');=0A+REINDEX=20INDEX=20= CONCURRENTLY=20idx_join;=0A+SELECT=20show_index_status('idx_join');=0A+=0A= +SELECT=20pg_get_indexdef('idx_join'::regclass);=0A+ALTER=20INDEX=20= idx_join=20ENABLE;=0A+SELECT=20pg_get_indexdef('idx_join'::regclass);=0A= +=0A+--=20Clean=20up=0A+DROP=20TABLE=20index_test;=0A+DROP=20TABLE=20= join_test;=0A+DROP=20FUNCTION=20get_data_length;=0A+DROP=20FUNCTION=20= show_index_status;=0A+=0A=20--=20Failure=20for=20unauthorized=20user=0A=20= CREATE=20ROLE=20regress_reindexuser=20NOLOGIN;=0A=20SET=20SESSION=20ROLE=20= regress_reindexuser;=0A--=20=0A2.37.1=20(Apple=20Git-137.1)=0A=0A= --Apple-Mail=_A720B644-54CD-484B-895F-BABE27930CC7 Content-Transfer-Encoding: 7bit Content-Type: text/html; charset=us-ascii
--Apple-Mail=_A720B644-54CD-484B-895F-BABE27930CC7-- --Apple-Mail=_4CA67527-9F2C-457A-ABBA-F92623F5DB75--