pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  

help / color / mirror / Atom feed
From: Vitalii Tymchyshyn <tivv00@gmail.com>
To: Robert Ayrapetyan <robert.ayrapetyan@comodo.com>
Cc: Kevin Grittner <Kevin.Grittner@wicourts.gov>
Cc: pgsql-performance@postgresql.org <pgsql-performance@postgresql.org>
Subject: Re: Performance die when COPYing to table with bigint PK
Date: Fri, 05 Aug 2011 13:36:38 +0300
Message-ID: <4E3BC7B6.8050700@gmail.com> (raw)
In-Reply-To: <CAAboi9tWD0d2X3vyjzmZP-wnvZWuBos7LFN6yK+OLVZmJtH_Wg@mail.gmail.com>
References: <CAAboi9sk0EnbS23-tr86X9i7LdHTfdbTP4aa7r2q7NpWKaHWPA@mail.gmail.com>
	<4E366C9C.4040904@gmail.com>
	<CAAboi9uU+hMg8deuJ6Q2LxncE2HO2jykPW89fPVxHLsU8_tFrw@mail.gmail.com>
	<CAAboi9s9MCsmqYPR+KqtXstPUodiE5MzvEpk1-e+C0n6FwkkyQ@mail.gmail.com>
	<4E37F07D020000250003F9A7@gw.wicourts.gov>
	<CAAboi9t8i_+W=c-bWCeYsc4mwYyT7+mGQ5QymtO6MLcH96YdqA@mail.gmail.com>
	<4E3A7BAC020000250003FA6C@gw.wicourts.gov>
	<4E3AC4CE.2000507@gmail.com>
	<CAAboi9syy0rRkhc0wy081-2K6mMbMJT9CQZTi9RRLBp540hrNw@mail.gmail.com>
	<4E3A8F07020000250003FA72@gw.wicourts.gov>
	<CAAboi9v_6OvPpHu-Ew2CYCH9Y8Q-XVm2nZU54zn8uw20ZfRJgQ@mail.gmail.com>
	<CABWW-d1rdRspTaZXW_MLDZtRbreUxTcKJavOqpd6M0Lm34s5Sw@mail.gmail.com>
	<CAAboi9tWD0d2X3vyjzmZP-wnvZWuBos7LFN6yK+OLVZmJtH_Wg@mail.gmail.com>

05.08.11 11:44, Robert Ayrapetyan написав(ла):
> Yes, you are right. Performance become even more awful.
> Can some techniques from pg_bulkload be implemented in postgres core?
> Current performance is not suitable for any enterprise-wide production system.
BTW: I was thinking this morning about indexes.
How about next feature:
Implement new index type, that will have two "zones" - old & new. New 
zone is of fixed configurable size, say 100 pages (800 K).
Any search goes into both zones. So, as soon as index is larger then 
800K, the search must be done twice.
As soon as new zone hit's it's size limit, part (may be only one?) of 
it's pages are merged with old zone. The merge is "rolling" - if last 
merge've stopped at "X" entry, next merge will start at entry right after X.

As for me, this should greatly resolve large index insert problem:
1) Insert into new zone must be quick because it's small and hot in cache.
2) During merge writes will be grouped because items with near keys (for 
B-tree) or hashes (for hash index) will go to small subset of "old" zone 
pages. In future, merge can be also done by autovacuum in background.
Yes, we get dual index search, but new zone will be hot, so this won't 
make it twice as costly.

Best regards, Vitalii Tymchyshyn




view thread (16+ messages)

Message-ID: <4E3BC7B6.8050700@gmail.com>
Permalink:  ../4E3BC7B6.8050700@gmail.com/
Also on:    postgresql.org/message-id/4E3BC7B6.8050700@gmail.com

 · 

reply
Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-performance@postgresql.org
  Cc: tivv00@gmail.com, robert.ayrapetyan@comodo.com, Kevin.Grittner@wicourts.gov
  Subject: Re: Performance die when COPYing to table with bigint PK
  In-Reply-To: <4E3BC7B6.8050700@gmail.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

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