X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org
Received: from localhost (av.hub.org [200.46.204.144])
	by postgresql.org (Postfix) with ESMTP id 919EA9DD768
	for <pgsql-performance-postgresql.org@localhost.postgresql.org>;
	Fri,  2 Dec 2005 19:26:33 -0400 (AST)
Received: from postgresql.org ([200.46.204.71])
	by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024)
	with ESMTP id 85284-02
	for <pgsql-performance-postgresql.org@localhost.postgresql.org>;
	Fri,  2 Dec 2005 19:26:29 -0400 (AST)
X-Greylist: delayed 00:27:13.561398 by SQLgrey-
Received: from zoraida.natserv.net (p65-147.acedsl.com [66.114.65.147])
	by postgresql.org (Postfix) with ESMTP id 599839DCF92
	for <pgsql-performance@postgresql.org>;
	Fri,  2 Dec 2005 19:26:31 -0400 (AST)
Received: from 35st-server.simplicato.com
	(static-71-249-233-130.nycmny.east.verizon.net [71.249.233.130])
	by zoraida.natserv.net (Postfix) with ESMTP id 255507D9C
	for <pgsql-performance@postgresql.org>;
	Fri,  2 Dec 2005 18:26:34 -0500 (EST)
Message-ID: <cone.1133566089.411630.45838.5001@35st-server.simplicato.com>
X-Mailer: http://www.courier-mta.org/cone/
From: Francisco Reyes <lists@stringsutils.com>
To: PostgreSQL performance <pgsql-performance@postgresql.org>
Subject: Small table or partial index?
Date: Fri, 02 Dec 2005 18:28:09 -0500
Mime-Version: 1.0
Content-Type: text/plain; format=flowed; charset="US-ASCII"
Content-Disposition: inline
Content-Transfer-Encoding: 7bit
X-Virus-Scanned: by amavisd-new at hub.org
X-Spam-Status: No, score=0 required=5 tests=[none]
X-Spam-Score: 0
X-Spam-Level: 
X-Archive-Number: 200512/88
X-Sequence-Number: 15909

I am in the process of designing a new system.
There will be a long list of words such as

-word table
word_id integer
word varchar
special boolean

Some "special" words are used to determine if some work is to be done and 
will be what we care the most for one type of operation. 

Will it be more effective to have a partial index 'where is special' or to 
copy those special emails to their own table?

The projected number of non special words is in the millions while the 
special ones will be in the thousands at most (under 10K for sure).

My personal view is that performance should be pretty much equal, but one of 
 my co-worker's believes that the smaller table would likely get cached by 
the OS since it would be used so frequently and would perform better.

In both instances we would be hitting an index of exactly the same size.

The searches will be 'where word = <variable> and is special'