Home > mailing lists

What kind of index to use for many rows with few unique values? - Mailing list pgsql-admin

From	David F. Skoll
Subject	What kind of index to use for many rows with few unique values?
Date	December 2, 2002 20:10:03
Msg-id	Pine.LNX.4.50.0212021707290.6178-100000@shishi.roaringpenguin.com Whole thread Raw
Responses	Re: What kind of index to use for many rows with few unique values? Re: What kind of index to use for many rows with few unique values?
List	pgsql-admin

Tree view

Hi,

I have a table with a column called "state".  Each row can be in one
of four states, let's call them 'new', 'pending', 'ok', and 'bad'.
On average, about 95% of the rows will be 'bad', with the remaining
5% being in one of the other three states.  If the table has 50K rows
and I just want to pull out the 'ok' rows, I don't want to do a sequential
scan.  To pull out the 'bad' rows, obviously, sequential scan is fine.

I've heard that a btree index performs badly in this situation.  Is
a hash index appropriate?  I've heard bad things about hash indexes in
PostgreSQL.

Regards,

David.

Roaring Penguin Software Inc. | http://www.roaringpenguin.com
GPG fingerprint: C523 771C 3710 0F54 B2D2 4B0D C6EF 6991 34AB 95BA
GPG public key:  http://www.roaringpenguin.com/dskoll-key-2002.txt ID: 34AB95BA

pgsql-admin by date:

From: Stephan Szabo
Date: 02 December 2002, 19:27:40
Subject: Re: [SQL] CURRENT_TIMSTAMP

From: Joel Burton
Date: 02 December 2002, 21:04:38
Subject: Re: What kind of index to use for many rows with few unique values?

What kind of index to use for many rows with few unique values? - Mailing list pgsql-admin

Previous

Next