From: | jao(at)geophile(dot)com |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | Insert rate drops as table grows |
Date: | 2006-01-30 22:47:42 |
Message-ID: | 20060130174742.svh9mhkqowssoswk@geophile.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
I have this table and index:
create table t(id int, hash int);
create index idx_t on t(hash);
The value of the hash column, which is indexed, is a pseudo-random
number. I load the table and measure the time per insert.
What I've observed is that inserts slow down as the table grows to
1,000,000 records. Observing the pg_stat* tables, I see that the data
page reads per unit time stay steady, but that index page reads grow
quickly, (shared_buffers was set to 2000).
I'm guessing that this is because inserts will append to data pages,
but there is a random probe to the btree to maintain the index.
This is a test program, but I'm wondering about my application that
will need row counts much beyond 1,000,000.
Questions:
- Am I reasoning about the data and index costs correctly?
- In order to keep insert times from dropping too much, do I simply
need to increase shared_buffers in order to accomodate more of the
index?
- Assuming I'm willing to buy enough RAM and set shmmax high enough,
are there practical limits on how big shared_buffers can be that will
limit how far I can pursue such a strategy?
Jack Orenstein
From | Date | Subject | |
---|---|---|---|
Next Message | Rick Gigger | 2006-01-30 23:29:15 | Re: incremental backups |
Previous Message | Héjja Attila | 2006-01-30 22:41:37 | WinXP install question |