I'm looking to create an index on a large table (~50 million rows) on a field with lots of non-unique values.
Table schema looks like:
Column | Type | Modifiers | Storage | Stats target | Description --------+-----------------------+-----------+----------+--------------+------------- gid | character varying(20) | | extended | | word | character varying(30) | | extended | | stat | double precision | | plain | | Has OIDs: no
I want to create an index on the 'word' column. There is a fairly regular pattern where each word appears about 1000 times. I need to do fast
SELECT * FROM mytable WHERE word='something';queries. Creating a regular B-Tree index on these tables takes a ton of time but does substantially improve performance.
I'm uncomfortable with my solution right now for several reasons
(1) The selection of a B-Tree index isn't particularly motivated. Are there alternative indexing schemes that perform better on fields with highly duplicated values?
(2) I'm in a production environment where these tables pop into and out of existence fairly regularly. Because not all tables will always be heavily queried I've opted to only build indexes on a table when certain (outside the DB) applications are triggered, such that I know 10k+ queries will be performed on the table+field. Waiting 20 minutes while an index is being created isn't ideal however. The situation is delicate; optimization gained by creating the index competes with the initial time-sink required to create the index. Are there 'cheaper' indexes to create? perhaps ones that will perform overall slightly worse than B-Tree but have less initial creation cost?