substring indices / array operators

From: Steve Atkins <steve(at)blighty(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: substring indices / array operators
Date: 2002-04-01 18:13:02
Message-ID: 20020401101302.A58900@blighty.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

Browse pgsql-general by date

  From Date Subject
Next Message Hal Roberts 2002-04-01 18:50:17 sorting goofiness
Previous Message Tom Lane 2002-04-01 17:33:15 Re: PostgreSQL and explain