Skip site navigation (1) Skip section navigation (2)

Re: why hash on the primary key?

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: "Robert Haas" <robertmhaas(at)gmail(dot)com>
Cc: pgsql-general(at)postgresql(dot)org
Subject: Re: why hash on the primary key?
Date: 2008-11-29 15:43:05
Message-ID: 1219.1227973385@sss.pgh.pa.us (view raw or flat)
Thread:
Lists: pgsql-general
"Robert Haas" <robertmhaas(at)gmail(dot)com> writes:
>> What's strange about it?  A probe into an in-memory hashtable is a lot
>> cheaper than a probe into an index, so this type of plan makes plenty
>> of sense if the hashtable will fit in RAM and there are going to be a
>> lot of probes.  (Where "a lot" means "enough to amortize the cost of
>> building the hashtable", of course.)

> Hmm...  it didn't occur to me that the index probe itself might be
> more expensive than a hash probe.  Is that due to concurrency control,
> or are you talking about the need to possibly read index pages in from
> disk?

Both, plus the loss of sequentiality of access to the table itself.

>>> Experimentation shows this is actually about 25% faster.
>> 
>> Well, that just says your cost parameters need a bit of adjustment
>> if you'd like the planner to get the crossover point exactly right.

> Any sense of which ones might be worth fiddling with?

random_page_cost, effective_cache_size, maybe the cpu_xxx parameters.

			regards, tom lane

In response to

Responses

pgsql-general by date

Next:From: Robert HaasDate: 2008-11-29 16:42:33
Subject: Re: why hash on the primary key?
Previous:From: Robert HaasDate: 2008-11-29 13:31:48
Subject: Re: why hash on the primary key?

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group