On 2/8/16, Johannes <jotpe@xxxxxxxxx> wrote: > Am 08.02.2016 um 21:50 schrieb Vitaly Burovoy: >> On 2/8/16, Johannes <jotpe@xxxxxxxxx> wrote: >>> Am 08.02.2016 um 21:17 schrieb Vitaly Burovoy: >>>> Hmm. Could you clarify why you don't want to pass id from the first >>>> query to the second one: >>>> >>>> select col1 from t1 where t0_id = value_id_from_the_first_query >>> >>> Of course I could do that, but in that case I would not ask. >>> >>> I thougt there could be a better solution to execute all statements at >>> once. >> >> What the reason to execute all statements which return different >> columns at once? >> >>> Saving roundtrips, >> >> In most cases they are not so big. Getting a bunch of duplicated data >> is wasting you network bandwidth and don't increase speed. > > In my and your example no duplicated data (result sets) is send over the > network. The server do not need to wait until the client snips out the > id and sends it id in the next query again. So the server can compute > the result set without external dependencies as fast as possible. We are talking about executing all statements at once to save RTT. Are we? And a parallel thread has advice to join tables (queries). It is a way to run both queries at once, but it is not a solution. >>> increase speed, >> >> Speed will be at least the same. In your case either you have to use >> more DDL (like CREATE TEMP TABLE) or get copied columns that leads >> more time to encode/decode and send it via network. > > The time difference is small, yes. > My old variant with executing the first select, remember the returned id > value and paste it into the second query and execute it takes 32ms. > > Your temp table variant need 29ms. Nice to see. That are 10% speed > improvement. I guess you measure it by your app. It is just a measurement error. +-3ms can be a sum of TCP packet loss, system interrupts, system timer inaccuracy, multiple cache missing, different layers (you are using Java, it has a VM and a lot of intermediate abstraction layers). Remember, my version has 6 statements each of them requires some work at PG's side, plus my version has two joins which usually slower than direct search by a value. Your version has only 4 statements and the only one slow place -- "where" clause in the second select which can be replaced by a value founded in the first select (your version sends more data: value1, value2, ...). You also can avoid "begin" and "commit" since default transaction isolation is "READ COMMITTED"[1]: > Also note that two successive SELECT commands can see different data, > even though they are within a single transaction, if other transactions commit > changes after the first SELECT starts and before the second SELECT starts. If you want to measure time, run both versions 10000 times in 8 connections simultaneously and compare results. ;-) 32ms * 10k requests / 8 threads = 40000ms = 40sec [1]http://www.postgresql.org/docs/devel/static/transaction-iso.html#XACT-READ-COMMITTED -- Best regards, Vitaly Burovoy -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
