Re: execute same query only one time?

On 2/9/16, Johannes <jotpe@posteo.de> wrote:
> 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:
>>>>> 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 ;)

I can't believe it. I insist it is a measurement error.

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

It is not true for transactions with SELECTs only. It is wise to join
multiple queries in one transaction when you do multiple _changes_ in
a DB, because after each change DB must save it into WAL file and
increase shared "Transaction ID sequence".

Also as in your example, transactions with isolation SERIALIZABLE and
REPEATABLE READ are used to do (even RO) queries to the DB in a
consistent state. But RO queries don't become faster.

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

Real world is complex. 40 seconds per test is not long.
If you have to save only one param for the other query creating a temp
table is heavy enough.

I'm waiting for a result of 10k requests test.
--
Best regards,
Vitaly Burovoy