Thread

What type of index should I use?

Mike Christensen <mike@kitchenpc.com> — 2010-06-21T09:01:32Z

I have a varying(200) text column that I need to be able to do lookups
on very fast (WHERE col = 'foo') - The data in this table will pretty
much never change (when it does I have to restart the entire server,
so updates of any sort are extremely rare).  I estimate the table will
hold around 5,000 rows, never any more.

Am I better off using a btree index or should I use GiN?  From what
I've read, GiN is extremely fast, but very slow for updates.  However,
it was unclear to me if they're only fast when doing fulltext
searches, or if they're just fast period.

Thanks!

Mike

Re: What type of index should I use?

Dimitri Fontaine <dfontaine@hi-media.com> — 2010-06-22T08:49:00Z

Mike Christensen <mike@kitchenpc.com> writes:

> I have a varying(200) text column that I need to be able to do lookups
> on very fast (WHERE col = 'foo')

Btree is what to use here. GIN covers cases where you index arrays.

>   I estimate the table will hold around 5,000 rows, never any more.

It could be that you're better off without any index, depending on the
size of rows you put in there, and the overall memory usage patterns you
have.

Regards,
-- 
dim

Re: What type of index should I use?

Adrian von Bidder <avbidder@fortytwo.ch> — 2010-06-22T14:35:01Z

On Tuesday 22 June 2010 10.49:00 Dimitri Fontaine wrote:
> >   I estimate the table will hold around 5,000 rows, never any more.
> 
> It could be that you're better off without any index, depending on the
> size of rows you put in there, and the overall memory usage patterns you
> have.

I agree: don't overstimate the effect an index can have and don't 
underestimate the cost of index updates.

Have you tested performance as it is?  Spending time to tune that doesn't 
need tuning is an interesting hobby, but probably nothing more :-)

cheers
-- vbi

-- 
Today is Pungenday, the 27th day of Confusion in the YOLD 3176