Re: How to improve insert speed with index on text column

On Tue, Jan 31, 2012 at 12:46 PM, Josh Berkus <josh(at)agliodbs(dot)com> wrote:
>
>> Shared buffers is the cache maintained by PostgreSQL. All all the data
>> that you read/write need to go through shared buffers.
>
> While this is technically true, I need to point out that you generally
> increase shared_buffers for high concurrency, and for reads, not for
> writes, especially for row-at-a-time inserts.  There's just not that
> much memory required (although more than the out-of-the-box defaults).
>
> I'd suggest increasing wal_buffers to 16MB, which is the maximum useful
> amount, rather than 5MB.

yeah -- postgresql.conf settings are not going to play a big role here unless:
*) you defer index build to the end of the load, and do CREATE INDEX
and crank maintenance_work_mem
*) you are doing lots of transactions and relax your sync policy via
synchronous_commit

what's almost certainly happening here is that the text index is
writing out a lot more data. what's the average length of your key?

If I'm inspecting sizes of tables/indexes which start with 'foo', I can do this:
postgres=# select relname,
pg_size_pretty(pg_relation_size(relname::text)) from pg_class where
relname like 'foo%';
relname | pg_size_pretty
-----------+----------------
foo | 40 kB
foo_i_idx | 40 kB
foo_t_idx | 40 kB

We'd like to see the numbers for your table/indexes in question.

merlin