Je suggÚre de prendre connaissance de la transcription du rapport du début de 2016 par Vladimir Sitnikov "PostgreSQL et JDBC pressent tous les jus"
![](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 â .
Que faisons-nous ensuite? Ils l'ont mesuré. Nous comprenons que nous devons utiliser soit des structures, soit la bactérie ingénieuse, combinant plusieurs valeurs.
![](https://habrastorage.org/webt/-w/-r/7d/-w-r7dfb-preqj4td8cyhl4irfm.png)
Que retenir du rapport d'aujourd'hui?
- PreparedStatement est notre tout. Cela donne beaucoup pour la performance. Cela donne un gros baril de goudron.
- Et vous devez faire EXPLAIN ANALYZE 6 fois.
- Et vous devez diluer OFFSET 0, et des astuces comme +0 afin de modifier le pourcentage de nos requĂȘtes problĂ©matiques qui y sont restĂ©es.