Документ взят из кэша поисковой машины. Адрес
оригинального документа
: http://www.sai.msu.su/~megera/oddmuse/index.cgi/GinFastUpdate
Дата изменения: Unknown Дата индексирования: Mon Apr 11 07:59:22 2016 Кодировка: Поисковые слова: reflection nebula |
The idea of GIN fast update is to accumulate new index rows in separate location and build index at vacuum time. That should greatly improve insert time, but increase search time, since search should lookup both that location and regular inverted index.
New option for CREATE INDEX - fastupdate={[true|1|on]|false|0|off}
Testing environment - GIN index on int[]:
postgres=# \d tt Table "public.tt" Column | Type | Modifiers --------+-----------+----------- id | integer | a | integer[] | Indexes: "gin_idx" gin (a) postgres=# \d tt2 Table "public.tt2" Column | Type | Modifiers --------+-----------+----------- id | integer | a | integer[] | Indexes: "gin_idx_nfu" gin (a) WITH (fastupdate=false)
One can consider this as emulation of blog with a rich dictionary. We emulate real-time environment using concurrent clients, which read and creates new documents. To improve performance 'vacuum analyze' runs after every 50 modifications (inserts/updates). Parent process forks clients, which run different queries (depending on given weights, for example, read/insert = 100/1), and collects stats from childs. Also, it periodically runs 'vacuum analyze' and prints global stats every 1000 operations.
Script runs forever, typical output looks like (times in sec):
select/insert/update = 1:0.01:0 Total = 700139:counts: 693220 6919 0 139 TotalDay= 75180250 Elapse= 804.626334:Sum= 8096.54966000014:7736.28839500003 176.893917 0 183.367348
TotalDay is the estimation of total number of queries per day. counts: 634872 6254 0 126 shows counters for select/insert/update/vacuum operations. Elapse is the time (s) for running script, Sum is the sum of all individual timings for selects,inserts,updates and vaccuum.
Environment: My desktop machine with 4GB RAM, Core2Duo 2.66GHz, 2.6.24.3-smp, busy firefox (30+ tabs), icewm, mplayer running, PostgreSQL CVS head + GIN fastupdate v. 0.13.
Summary:
select/insert/update = 1:0.01:0 Total = 700139:counts: 693220 6919 0 139 TotalDay= 75180250 Elapse= 804.626334:Sum= 8096.54966000014:7736.28839500003 176.893917 0 183.367348 Total = 700133:counts: 693500 6633 0 133 TotalDay= 80674016 Elapse= 749.826202:Sum= 7592.16917599985:7280.20674199988 168.201272 0 143.761162 ------------------------------------------------------------------------ select/insert/update = 1:0.1:0 Total = 701276:counts: 637520 63756 0 1276 TotalDay= 32229391 Elapse= 1879.968669:Sum= 15753.7955909996:12785.8333579997 1459.52234 0 1508.439893 Total = 701273:counts: 637660 63613 0 1273 TotalDay= 32900288 Elapse= 1841.624821:Sum= 14951.7257170003:11652.6948879999 1838.66890699999 0 1460.361922 ------------------------------------------------------------------------ select/insert/update = 1:1:0 Total = 707177:counts: 348360 358817 0 7177 TotalDay= 9119596 Elapse= 6699.868084:Sum= 13777.8184119997:5163.04862900011 2082.48855400007 0 6532.281229 Total = 707171:counts: 348620 358551 0 7171 TotalDay= 9095799 Elapse= 6717.339423:Sum= 14320.8554989999:4774.93323400009 3002.11292499999 0 6543.80934000001 ------------------------------------------------------------------------- select/insert/update = 1:10:0 Total = 712989:counts: 63540 649449 0 12989 TotalDay= 4087770 Elapse= 15069.889573:Sum= 18328.8505179987:2181.982464 1139.24994300097 0 15007.618111 Total = 712970:counts: 64460 648510 0 12970 TotalDay= 4095267 Elapse= 15041.900094:Sum= 19889.9708929997:2507.75610700001 2404.95518500038 0 14977.2596010001