A few questions, hopefully fairly simple for someone here.
1) If I have a large (1e7+ rows) table indexed on a wide varchar field
a complete copy of that varchar field will be included in the index.
That makes the index file ridiculously large and, presumably, slow.
I'm mostly searching for exact matches and occasionally prefix
matches (like 'mumblefoo%') on the wide text field. I could use
a functional index to record just the first 10 characters
of the text field in the index, and use some odd select statements
to use the index
("create index tab_idx on tab (mysubstr10(word))"
"select * from tab where mysubstr10(word)='abcdefghij' and word
like 'abcdefghijkl%'")
Is there any smarter way to setup an index to handle this
efficiently?
2) Are there any array operators or functions in the postgresql kernel
other than array_dims(), equality and the slice operator?
(I'm trying to do an UPDATE where I append an array onto the
already existing array)
Cheers,
Steve