Re: Performance die when COPYing to table with bigint PK

All you are saying disproves following:

in experiment I replaces bigint index:

CREATE INDEX ix_t_big ON test.t USING btree (id_big) TABLESPACE tblsp_ix;

with 4 (!) other indexes:

CREATE INDEX ix_t2 ON test.t USING btree (ip) TABLESPACE tblsp_ix;
CREATE INDEX ix_t3 ON test.t USING btree (id_small) TABLESPACE tblsp_ix;
CREATE INDEX ix_t4 ON test.t USING btree (id_smalll) TABLESPACE tblsp_ix;
CREATE INDEX ix_t5 ON test.t USING btree (ts) TABLESPACE tblsp_ix;

which are definitely larger then one bigint index.

0.000u 0.005s 0:13.23 0.0% 0+0k 0+0io 0pf+0w
COPY 100000
0.000u 0.035s 0:05.08 0.5% 421+1114k 0+0io 0pf+0w
COPY 100000
0.000u 0.036s 0:19.28 0.1% 526+1393k 0+0io 0pf+0w
COPY 100000
0.000u 0.005s 0:05.56 0.0% 0+0k 0+0io 0pf+0w
COPY 100000
0.006u 0.012s 0:05.57 0.1% 984+1820k 0+0io 0pf+0w
COPY 100000
0.007u 0.029s 0:05.20 0.3% 808+1746k 0+0io 0pf+0w
COPY 100000
0.005u 0.000s 0:05.35 0.0% 0+0k 0+0io 0pf+0w
COPY 100000
0.000u 0.011s 0:05.92 0.1% 316+836k 0+0io 0pf+0w
COPY 100000
0.000u 0.005s 0:12.08 0.0% 0+0k 0+0io 0pf+0w
COPY 100000
0.000u 0.029s 0:05.46 0.3% 808+2074k 0+0io 0pf+0w
COPY 100000
0.002u 0.002s 0:05.35 0.0% 0+0k 0+0io 0pf+0w
COPY 100000
0.000u 0.005s 0:06.52 0.0% 0+0k 0+0io 0pf+0w

Insertions became slower 4-5 times, which is ok.

Nothing is closer to even half of minute, while one bigint index constantly
gives more then minute and even 2 for 100k records.

On Thu, Aug 4, 2011 at 8:22 PM, Kevin Grittner
<Kevin(dot)Grittner(at)wicourts(dot)gov> wrote:
> Robert Ayrapetyan <robert(dot)ayrapetyan(at)comodo(dot)com> wrote:
>
>> If you look at the rest of my mail - you would notice 50 times
>> difference in performance.
>> What you would say?
>
> That accessing a page from RAM is more than 50 times as fast as a
> random access of that page from disk.
>
> -Kevin
>

--
Ayrapetyan Robert,
Comodo Anti-Malware Data Processing Analysis and Management System (CAMDPAMS)
http://repo-qa.camdpams.odessa.office.comodo.net/mediawiki/index.php