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 1shGOP-00FQEP-7A for pgsql-general@arkaria.postgresql.org; Thu, 22 Aug 2024 22:34:57 +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 1shGOM-006Bre-U7 for pgsql-general@arkaria.postgresql.org; Thu, 22 Aug 2024 22:34:55 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1shGOM-006BrQ-G6 for pgsql-general@lists.postgresql.org; Thu, 22 Aug 2024 22:34:55 +0000 Received: from mail-oa1-x31.google.com ([2001:4860:4864:20::31]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1shGOI-00116k-LO for pgsql-general@postgresql.org; Thu, 22 Aug 2024 22:34:54 +0000 Received: by mail-oa1-x31.google.com with SMTP id 586e51a60fabf-2689f749702so841159fac.3 for ; Thu, 22 Aug 2024 15:34:51 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1724366090; x=1724970890; darn=postgresql.org; h=to:subject:message-id:date:from:mime-version:from:to:cc:subject :date:message-id:reply-to; bh=CBWweRFI5BKO1Dgg6e4nZQoVkjpK4eYJLi2vqQayMWk=; b=WOu8LkdxxxSde3DcqB//KaGRnzTT8lzBRFh9qQrYaPOLfsg7uNMTynXwscm58u5LHQ aps4+Isu3tl/lUYyBCbgaecmYmTZffw+C0Ul6yOWSChsi6iUcMDlqAdKdeHC5/u8AF6q jvBi7bmpZVof/5dZJ6Ftt8HGwwR5eua9mPhuAm9SqCSU4y3RVYquOGoGzQwyc2uoAPuv hWYFTtI8w/aelOJx+rxFxL078PNefHVin6ZXkzXq8LINk3Rg+oiXbB+LffOKxJyk+X94 fEEqjzLPCLWaaQBr1zrKx4Pu/7nhNmHRQdVufDsG+TJfrhsgqz3D68oDuKB3KH3Gpuav zriQ== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1724366090; x=1724970890; h=to:subject:message-id:date:from:mime-version:x-gm-message-state :from:to:cc:subject:date:message-id:reply-to; bh=CBWweRFI5BKO1Dgg6e4nZQoVkjpK4eYJLi2vqQayMWk=; b=SufYtFCDHr8EGVakuoLCxfHNIaeR7G/33VbTmTKkLTRNv2/Ge7Q2rgQ8cH0ytOElwM TLC7lLUDO5mvi7gNcJLjBKgqO67GakCJXCYXIDRuliORDHC7QNAJPvFRyt2IeKk1OYqy IefMlVI5lAXZvl1jdj330X+xkeFUf4IRmyRcDx1PYWSkpJYBfW1f3ASWJVfVTwKCFSql Dq0Jxu0qVZ8SI3dg7OCueryfphojyeKp4pLRo9tWfp4qaOwYGWGqX+Hin+Z55q4g5FVj LfSQ2VwWRG5ZCvabeMREsMLbPKSRcEzQvM31NQyZutKASBue2lXLHzqL2L5HvaLmQHlY PqFQ== X-Gm-Message-State: AOJu0YzLM8o361G6gebKxxU5p3KCsNLhupGehH60LPTFd+uoF4gmq0uM 53inZr5au8AKLBhk/4W1stOw5enmtK1/MKtoMdAoeRk6wGqAco7qUt3mD2rREKrVN4S6e6hNLGj r7a8g2OEOITnBAG6cE/QMzX3X3IAhiDNb X-Google-Smtp-Source: AGHT+IFR+r4Pan0WW8z7pT24qyNyTSdrxsbf5C5f/mGze7DDki9xkGL8QCNBCP18Rh7SC7t6tqs4uYnpvrgZ9K6A2QE= X-Received: by 2002:a05:6871:689:b0:260:f5ab:62ff with SMTP id 586e51a60fabf-273e66c43a9mr233594fac.44.1724366089863; Thu, 22 Aug 2024 15:34:49 -0700 (PDT) MIME-Version: 1.0 From: Morris de Oryx Date: Thu, 22 Aug 2024 17:34:39 -0500 Message-ID: Subject: Is there a way to translate pg_amop.amopstrategy into a description? To: pgsql-general Content-Type: multipart/alternative; boundary="0000000000003fc8cf06204d44de" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --0000000000003fc8cf06204d44de Content-Type: text/plain; charset="UTF-8" I'm digging into GiST indexes again, and ran into a helpful script here: https://medium.com/postgres-professional/indexes-in-postgresql-5-gist-86e19781b5db (This piece has shown up in many places in various versions.) I've adapted the search a little, as I'd like to make it easier to explore available index ops: SELECT amop.amopopr::regoperator AS operator, iif(amop.amoppurpose = 's', 'search','order') AS purpose, amop.amopstrategy AS stratgey_number -- I'd like to translate this into a description FROM pg_opclass opc, pg_opfamily opf, pg_am am, pg_amop amop WHERE opc.opcname = 'gist_trgm_ops' AND am.amname = 'gist' AND opf.oid = opc.opcfamily AND am.oid = opf.opfmethod AND amop.amopfamily = opc.opcfamily AND amop.amoplefttype = opc.opcintype; +------------------+---------+-----------------+ | operator | purpose | stratgey_number | +------------------+---------+-----------------+ | %(text,text) | search | 1 | | <->(text,text) | order | 2 | | ~~(text,text) | search | 3 | | ~~*(text,text) | search | 4 | | ~(text,text) | search | 5 | | ~*(text,text) | search | 6 | | %>(text,text) | search | 7 | | <->>(text,text) | order | 8 | | %>>(text,text) | search | 9 | | <->>>(text,text) | order | 10 | | =(text,text) | search | 11 | +------------------+---------+-----------------+ What I'm hoping for is a function like get_opt_class_strategy_description(optclass, straregy_number) I've looked at the source a bit, and it seems that there is no such function, and that it might well be difficult to implement. The strategy numbers are, as far as I can see, local to the specific opt_class, which has no requirement to label them in any particular way. Does anyone know if I'm missing something? Along the way, I did find that you can often look things up by hand in the source for specific tools, or review a lot of the strategies in one place: https://github.com/postgres/postgres/blob/edcb71258504ed22abba8cc7181d2bab3762e757/src/include/catalog/pg_amop.dat#L82 It's easier to use the docs at that point. No lives hang in the balance here, but I'm hoping to learn something. Thanks for any help or clarification. --0000000000003fc8cf06204d44de Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
I'm digging into GiST indexes again, and ran into a he= lpful script here:

https://medium.co= m/postgres-professional/indexes-in-postgresql-5-gist-86e19781b5db

(This piece has shown up in many places in various = versions.) I've adapted the search a little, as I'd like to make it= easier to explore available index ops:

 SELECT amop.amopopr:=
:regoperator                       AS operator,
        iif(a=
mop.amoppurpose =3D 's', 'search','=
order')   AS purpose,=

        amop.amopstrategy =
                              AS=
 stratgey_number -- I'd like to tran=
slate this into a description
       
   FROM pg_opclass=
 opc, 
        pg_opfamily opf, 
        pg_am am, 
        pg_amo=
p amop
     
  WHERE opc.opcname        =3D 'gist_trgm_ops'
    AND am.amname          =3D 'gist'
    AND opf.oid          =
  =3D opc.opcfamily
    AND am.oid             =3D opf.opfmethod
    AND amop.amopfamily    =3D opc.opcfamily
    AND amop.amoplefttype  =3D opc.opcintype;

+------------------+----=
-----+-----------------+
| operator         | purpose | stratgey_number =
|
+------------------+---------+-----------------+
| %(text,text)    =
 | search  | 1               |
| <->(text,text)   | order   | 2   =
            |
| ~~(text,text)    | search  | 3               |
| ~~*(=
text,text)   | search  | 4               |
| ~(text,text)     | search  =
| 5               |
| ~*(text,text)    | search  | 6               |
=
| %>(text,text)    | search  | 7               |
| <->>(text=
,text)  | order   | 8               |
| %>>(text,text)   | search =
 | 9               |
| <->>>(text,text) | order   | 10      =
        |
| =3D(text,text)     | search  | 11              |
+-------=
-----------+---------+-----------------+
What I'm hoping fo=
r is a function like get_opt_class_strategy_description(optclass, straregy_number)=C2=A0 I've looked at the source a bit, and it seem=
s that there is no such function, and that it might well be difficult to im=
plement. The strategy numbers are, as far as I can see, local to the specif=
ic opt_class, which has no requirement to label them in any particular way.=

<=
/font>
Does anyone know if I'm missing something?<=
/font>
Along the way, I did find that you can often look thin=
gs up by hand in the source for specific tools, or review a lot of the stra=
tegies in one place:
https://github.com/postgres/postgres/blob/edcb712=
58504ed22abba8cc7181d2bab3762e757/src/include/catalog/pg_amop.dat#L82
It's easier to use the docs at =
that point.
No lives hang in the balance here, =
but I'm hoping to learn something.
Thanks=
 for any help or clarification.
--0000000000003fc8cf06204d44de--