我建议您熟悉Vladimir Sitnikov在2016年初的报告中所写的文字:“ PostgreSQL和JDBC挤占了所有果汁”
![](https://habrastorage.org/webt/o9/mp/ux/o9mpuxbzsx9o1mci3cjhavkz9eg.png)
![](https://habrastorage.org/webt/go/vu/zb/govuzbz3d10w7ujmgtitupkkj5q.png)
! . 10 NetCracker. . , Java, , SQL – , .
, , PostgreSQL . Java. , , Java. , .
![](https://habrastorage.org/webt/c7/xp/x1/c7xpx1nzcmckuko3bai42cjppmm.png)
:
![](https://habrastorage.org/webt/ge/7s/vj/ge7svjbgkduzr1bsn8ep_hda47m.png)
. .
![](https://habrastorage.org/webt/kr/kb/51/krkb51wxmokk-gubovhvcvc2ujm.png)
. 20 .
![](https://habrastorage.org/webt/5p/_l/ij/5p_lijcziissassxqqx_qgswciy.png)
20 – . 100 , , , . . .
, . .
![](https://habrastorage.org/webt/up/eb/oe/upeboeudeaasslci-lgdaqt6jl0.png)
– . ? , , .
![](https://habrastorage.org/webt/po/_9/iw/po_9iwqthv6itpgmuxbk6xfenf0.png)
https://github.com/pgjdbc/pgjdbc/pull/478
, , . , , . .
Super extended query – , . , , - , - , . . , , . , .
![](https://habrastorage.org/webt/zm/co/yq/zmcoyqjga0rkf59rbo_p4nae5xy.png)
, , – simple query extended query.
?
. . , , . . - .
![](https://habrastorage.org/webt/la/ea/g3/laeag3npvvtd8jya3cwt1yywpg4.png)
Extended query – . , . - . . . .
![](https://habrastorage.org/webt/7p/1b/4x/7p1b4xaekfrd2sk9zruoelaohlc.png)
. . Java . .
statement. . close. ? ? . . . , .
![](https://habrastorage.org/webt/pl/v2/mv/plv2mvpwssqee4chelojn7oliwy.png)
, . ? «close». , – . «PARSE EXECUTE DEALLOCATE».
statements? . PreparedStatement , , . , .
![](https://habrastorage.org/webt/pz/6i/lc/pz6ilczzlrcd638cecbih0ju_w8.png)
, , . statement, . – . REST statement id. .
![](https://habrastorage.org/webt/iz/w0/8v/izw08vq8e5jcqvaszxqnsxxrznm.png)
?
![](https://habrastorage.org/webt/wz/3s/kb/wz3skbi9b8yl_kp1tioe5q4ppyy.png)
– statements. : «prepare» «execute».
![](https://habrastorage.org/webt/ej/za/5m/ejza5mytlgugtrf5kmi9mpcqznc.png)
![](https://habrastorage.org/webt/z3/nx/zd/z3nxzdmrsddw0jj5p7kryydcdn4.png)
, , - - . , . , . statement. - , , .
, . . , . , .
![](https://habrastorage.org/webt/xy/o0/0w/xyo00wduqnjiv1pjsavsb7--wnq.png)
? ?
statements. , , .
PostgreSQL . , .
.
![](https://habrastorage.org/webt/8e/wp/id/8ewpidji1ovqyihle5mnt-zhodi.png)
.
– , PgSQL. . . . . 100500 . . – . -.
– . , . -. , .
![](https://habrastorage.org/webt/eg/g1/-n/egg1-nh6fsgdhqk8lucgccfvxz4.png)
https://github.com/pgjdbc/pgjdbc/pull/319
2015 . . . , . PgSQL, . . , .
Server-prepared statements 5- , .
![](https://habrastorage.org/webt/am/la/jv/amlajvzqsxbt1_zlpetfedy8f10.png)
– ? ? , .
, OLTP- - 20 . 0,5 , 20 . – 10 , 170 . OLTP . 1, 5, 10 , .
20 . 0 . .
? Java, .
- , – ? , , PL 8 LibPQ, , , . ? .
![](https://habrastorage.org/webt/tk/ud/sf/tkudsfjgajo1lbzu0k1z7ogpmhk.png)
, , - . . , , , .
![](https://habrastorage.org/webt/ao/2n/xu/ao2nxuttpkvfdbxhxuzedl3q1ig.png)
. . - , SQL-.
? , .
hashCode. CPU – hash’ . – . - . .
![](https://habrastorage.org/webt/vd/-4/jt/vd-4jtaoxeo03wkrb-7wzhbcoa4.png)
. . ORM, , NULL, -. Int, setInt. NULL, VARCHAR . NULL? . .
. , , , VARCHAR, Server-prepared statements. statement.
![](https://habrastorage.org/webt/6o/sc/pi/6oscpikbal7cq5c3cefeivhkwfi.png)
, , . NULL. , , PreparedStatements
![](https://habrastorage.org/webt/bt/6l/nw/bt6lnwynt-gy60hpfgopwdze_oa.png)
, . , , . . .
? ? , – . . . . , , . , , - , . .
![](https://habrastorage.org/webt/5f/wh/pk/5fwhpkomywdunsjgey9a_ga98hk.png)
https://gist.github.com/vlsi/df08cbef370b2e86a5c1
, , . NULL . 20 . , .
, . . «?» «$1» , ?
![](https://habrastorage.org/webt/nw/uj/_o/nwuj_okbygswxbsa_37zeaufn44.png)
https://gist.github.com/vlsi/df08cbef370b2e86a5c1
– . – . - . --. – - . , . , , , ?
![](https://habrastorage.org/webt/tx/1x/mj/tx1xmjmnqnczgyjztk63wawbqia.png)
? ? . generic . , , - generic , , , , . , . - , .
? , , - . , . +0, OFFSET 0. , . «+0» . .
– . , 6 «explain analyze». 5, .
– pgsql-hackers . , , – .
![](https://habrastorage.org/webt/wj/vt/g5/wjvtg58_zxyg3tmzckvsl1jvate.png)
https://gist.github.com/vlsi/df08cbef370b2e86a5c1
– , . «+0». . , . . . «+0» , , .
![](https://habrastorage.org/webt/6z/6i/qy/6z6iqycdsc6vwmj0lgetcujkbxs.png)
6- explain’. 6 , . , . . .
, ? , . .
![](https://habrastorage.org/webt/aw/q4/kv/awq4kvjpotl1mu1jezibsp4bqpg.png)
. , . . – . ? . . – , .
![](https://habrastorage.org/webt/d4/m-/ek/d4m-ekiasldedl1l8jpnhogkh0q.png)
: «?». , , , , «search_path», , . , .
? , server-prepared statements , search_path - . . - .
![](https://habrastorage.org/webt/xy/x2/qm/xyx2qmf7nzyek5ci5usjq76dxoq.png)
, , . , . 9.1 . , .
![](https://habrastorage.org/webt/ko/g-/dn/kog-dnbw5ngnzfdh7ue4aftpljq.png)
Set search_path + server-prepared statements =
cached plan must not change result type
? – . search_path . , .
, . . , , . , , , - , : «, . , statements , ?». , - statements .
– , Java. PL/ pgSQL . .
![](https://habrastorage.org/webt/a8/il/dw/a8ildwhpmbfhtsl9ith8ryyezfm.png)
. -. . . . Java- 128 .
, , . . . resultSet . ? ? - ? , Postgres . . OutOFMemory? OutOfMemory? ? - .
, . OFFSET/LIMIT. ? , autoCommit’ ?
, , . autoCommit, . ? .
![](https://habrastorage.org/webt/33/tt/x3/33ttx3gakes4wf3bigph2jgroak.png)
, Postgres, . PgJDBC , .
FetchSize, . . statement , , , 10, 50. , autoCommit. autoCommit – .
setFetchSize – . , .
![](https://habrastorage.org/webt/ua/js/2d/uajs2dod6tfzh2scrls83ljsfc0.png)
. . ? , , , 10 , . .
![](https://habrastorage.org/webt/w_/lr/s3/w_lrs37cz1dkeejkbgerkdg_-6s.png)
, , , : defaultRowFetchSize .
![](https://habrastorage.org/webt/ev/yf/1i/evyf1igsnpxncbc29vwzsuwlpmk.png)
. – , . , INSERT, VALUES. . «INSERT SELECT». . .
, Batch statement, , . Postgres – COPY , . . .
![](https://habrastorage.org/webt/-c/vk/tl/-cvktld0ukglx4bdcqab9b45ovc.png)
, . , ? .
![](https://habrastorage.org/webt/ej/xu/ey/ejxuey8mpvsyei9eyqp6vvmppik.png)
TCP . , , . , , , .
![](https://habrastorage.org/webt/rz/xm/3n/rzxm3n4mlugk6syvve7rhg4ksc8.png)
. , .
, . , 200 , . .
![](https://habrastorage.org/webt/9t/jq/pn/9tjqpnlv1fk4dzvhr3mfcgaruvy.png)
https://github.com/pgjdbc/pgjdbc/pull/380
, 10 . . ? , - . «128» – batching.
![](https://habrastorage.org/webt/cc/r8/ez/ccr8ez25tb0xnesnhpzql2v8uoe.png)
Java microbenchmark harness
, . , . , , .
![](https://habrastorage.org/webt/wk/xd/20/wkxd20mxi4k0ngck1yioanlfkxe.png)
. InsertBatch . InsertBatch , . . , values . . , , , COPY.
![](https://habrastorage.org/webt/yw/xc/6e/ywxc6egbmmswpw7ixqeqaqy67wa.png)
COPY.
![](https://habrastorage.org/webt/qb/ds/v2/qbdsv2bz91_dw1fmrgv0qi9l3eo.png)
. User default type, INSERT .
: pgjdbc/ubenchmsrk/InsertBatch.java, GitHub. , . .
![](https://habrastorage.org/webt/tw/j6/sq/twj6sqlwlpjwttkxdgxdpfo1npw.png)
. , , batch – . batching , . . .
![](https://habrastorage.org/webt/gl/jh/_6/gljh_6tc9n-bt3zmwidkzjvl_yk.png)
. . . ? , . COPY, , .
![](https://habrastorage.org/webt/cs/ei/cd/cseicdwylrp_smyxv5btm4_ypzy.png)
. , VALUES, VALUES, VALUES 10 . . 1, 2, 4, 128. , Batch Insert, , . . . , , , VALUES . EXECUTE.
COPY – . . , . . COPY.
COPY , . . , , . Batch , COPY – .
接下来我们要做什么?他们测量了它。我们知道,我们必须使用结构或巧妙的方法,结合多种价值。
![](https://habrastorage.org/webt/-w/-r/7d/-w-r7dfb-preqj4td8cyhl4irfm.png)
需要从今天的报告中学到什么?
- PreparedStatement是我们的一切。它为性能提供了很多。它产生了一大桶焦油。
- 并且您需要做EXPLAIN ANALYZE 6次。
- 而且,您需要稀释OFFSET 0和+0之类的技巧,以编辑保留在那里的有问题查询的百分比。