Re: execute same query only one time?

Am 09.02.2016 um 00:56 schrieb Vitaly Burovoy:
> On 2/8/16, Johannes <jotpe@posteo.de> wrote:
>> Am 08.02.2016 um 21:50 schrieb Vitaly Burovoy:
>>> On 2/8/16, Johannes <jotpe@posteo.de> 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?

Yes, we do.

> 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.

Right.

>>>> 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).

I know all these facts. I run it a "only" a few times, single threaded.
With high resolution. The rounded result of 3ms was reliable and thats
exact enough for my rule of thumb.
Your temp table variant looks more elegant, and is sightly faster. I'm
fine with that.

> 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, ...).

Anyway, it is faster ;)

> 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.

I know. I did not tell that I run my queries in with repeatable read
isolation.
And I read it is wise to bundle multiple queries in an transaction,
because the overhead of multiple transaction can be avoid to one.

> 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

Thats more complicated, I think I learned enough about it. But thanks.

Ciao Johannes