Re: Performance die when COPYing to table with bigint PK

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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.Grittner@xxxxxxxxxxxx> wrote:
> Robert Ayrapetyan <robert.ayrapetyan@xxxxxxxxxx> 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

-- 
Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance


[Postgresql General]     [Postgresql PHP]     [PHP Users]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Yosemite]

  Powered by Linux