Re: execute same query only one time? - Mailing list pgsql-general

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?

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