| Lists: | pgsql-performance |
|---|
| From: | "Kevin Grittner" <Kevin(dot)Grittner(at)wicourts(dot)gov> |
|---|---|
| To: | <saurabh(dot)b85(at)gmail(dot)com>,<pgsql-performance(at)postgresql(dot)org> |
| Subject: | Re: How to improve insert speed with index on text column |
| Date: | 2012-02-03 19:03:18 |
| Message-ID: | 4F2BDB160200002500044D5A@gw.wicourts.gov |
| Views: | Raw Message | Whole Thread | Download mbox | Resend email |
| Lists: | pgsql-performance |
Saurabh wrote:
> wal_buffers = 5MB
As as already been suggested, use 16MB (or if the version you're
using supports it, the default of -1);
> autovacuum = off
If the only activity while this is off is a bulk load, that might be
OK, but be sure *not* to leave this off. You will almost certainly
regret very much later. Your tables will tend to bloat and slowly
get very slow and very big. At that point it will be much more
painful to do aggressive maintenance to clean things up. If you
think you have some particular reason to turn it off, please discuss
it here -- you might have better options.
-Kevin
| From: | Saurabh <saurabh(dot)b85(at)gmail(dot)com> |
|---|---|
| To: | pgsql-performance(at)postgresql(dot)org |
| Subject: | Re: How to improve insert speed with index on text column |
| Date: | 2012-02-05 17:29:22 |
| Message-ID: | 51f4fd78-e683-4c24-a7fa-5cdb0ef21693@lr19g2000pbb.googlegroups.com |
| Views: | Raw Message | Whole Thread | Download mbox | Resend email |
| Lists: | pgsql-performance |
Kelvin,
My intention to keep autovacuum as off is bulk loading only. I was
thinking after bullk load I will change it.
I changed wal_buffer from 5MB to 16MB but I got same performance that
I got with 5MB (even less).
Thanks,
Saurabh
| From: | Robert Haas <robertmhaas(at)gmail(dot)com> |
|---|---|
| To: | Saurabh <saurabh(dot)b85(at)gmail(dot)com> |
| Cc: | pgsql-performance(at)postgresql(dot)org |
| Subject: | Re: How to improve insert speed with index on text column |
| Date: | 2012-02-29 18:35:37 |
| Message-ID: | CA+TgmoYtWVjPvD-koPcNC8NN_t9S1qoYbkt_-+KAYrucCryTgA@mail.gmail.com |
| Views: | Raw Message | Whole Thread | Download mbox | Resend email |
| Lists: | pgsql-performance |
On Sun, Feb 5, 2012 at 12:29 PM, Saurabh <saurabh(dot)b85(at)gmail(dot)com> wrote:
> My intention to keep autovacuum as off is bulk loading only. I was
> thinking after bullk load I will change it.
>
> I changed wal_buffer from 5MB to 16MB but I got same performance that
> I got with 5MB (even less).
Does it help if you create the index using COLLATE "C"? Assuming
you're on 9.1.x...
--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company