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.
