pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedSmall table or partial index?
3+ messages / 2 participants
[nested] [flat]
* Small table or partial index?
@ 2005-12-02 23:28 Francisco Reyes <lists@stringsutils.com>
2005-12-12 22:24 ` Re: Small table or partial index? Jim C. Nasby <jnasby@pervasive.com>
0 siblings, 1 reply; 3+ messages in thread
From: Francisco Reyes @ 2005-12-02 23:28 UTC (permalink / raw)
To: pgsql-performance
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'
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Small table or partial index?
2005-12-02 23:28 Small table or partial index? Francisco Reyes <lists@stringsutils.com>
@ 2005-12-12 22:24 ` Jim C. Nasby <jnasby@pervasive.com>
2005-12-13 16:08 ` Re: Small table or partial index? Francisco Reyes <lists@stringsutils.com>
0 siblings, 1 reply; 3+ messages in thread
From: Jim C. Nasby @ 2005-12-12 22:24 UTC (permalink / raw)
To: Francisco Reyes <lists@stringsutils.com>; +Cc: pgsql-performance
On Fri, Dec 02, 2005 at 06:28:09PM -0500, Francisco Reyes wrote:
> 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.
Tough call. The key here is the amount of time required to do a join. It
also depends on if you need all the special words or not. Your best bet
is to try and benchmark both ways.
--
Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com
Pervasive Software http://pervasive.com work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Small table or partial index?
2005-12-02 23:28 Small table or partial index? Francisco Reyes <lists@stringsutils.com>
2005-12-12 22:24 ` Re: Small table or partial index? Jim C. Nasby <jnasby@pervasive.com>
@ 2005-12-13 16:08 ` Francisco Reyes <lists@stringsutils.com>
0 siblings, 0 replies; 3+ messages in thread
From: Francisco Reyes @ 2005-12-13 16:08 UTC (permalink / raw)
To: Jim C. Nasby <jnasby@pervasive.com>; +Cc: pgsql-performance
Jim C. Nasby writes:
> On Fri, Dec 02, 2005 at 06:28:09PM -0500, Francisco Reyes wrote:
>> 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.
>
> Tough call. The key here is the amount of time required to do a join. It
> also depends on if you need all the special words or not. Your best bet
> is to try and benchmark both ways.
In your opinion do you think performance will be comparable?
I am hoping I will have time to test, but not sure if will have time and the
tables will be pretty large. :-(
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2005-12-13 16:08 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-12-02 23:28 Small table or partial index? Francisco Reyes <lists@stringsutils.com>
2005-12-12 22:24 ` Jim C. Nasby <jnasby@pervasive.com>
2005-12-13 16:08 ` Francisco Reyes <lists@stringsutils.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox