Re: [PATCH 4/4] Add tests to dblink covering use of COPY TO FUNCTION

2009/11/25 Jeff Davis <pgsql(at)j-davis(dot)com>:
> On Wed, 2009-11-25 at 11:32 +0100, Pavel Stehule wrote:
>> 1.
>> postgres=# select count(*) from generate_series(1,1000000);
>>   count
>> ─────────
>>  1000000
>> (1 row)
>>
>> Time: 930,720 ms
>>
>> 2.
>> postgres=# select count(*) from (select generate_series(1,1000000)) x;
>>   count
>> ─────────
>>  1000000
>> (1 row)
>>
>> Time: 276,511 ms
>>
>> 2. is significantly faster then 1 (there are not SRF materialisation)
>
> I think case #1 can be fixed.
>
>> generate_function is fast and simple - but still COPY is about 30% faster
>
> My quick tests are not consistent enough, so I will have to try with
> more data. The times look similar to me so far.
>
> If there is a difference, I wonder what it is?
>
>> I thing, so materialisation is every time, when you use any SQL
>> statement without cursor.
>
> I don't think that is true. Here's an expanded version of my previous
> example:
>
> create table zero(i int);
> create table tmp(j int);
> insert into zero select 0 from generate_series(1,1000000); -- all 0
> insert into tmp select 1/i from zero; -- error immediately, doesn't wait
>
> The error would take longer if it materialized the table "zero". But
> instead, it passes the first tuple to the function for "/" before the
> other tuples are read, and gets an error immediately. So no
> materialization.

this show nothing.

It working like:

1. EXECUTE SELECT 0 FROM generate_series(1,...);
2. STORE RESULT TO TABLE zero;
3. EXECUTE SELECT 1/i FROM zero;
4. STORE RESULT TO TABLE tmp;

Problem is in seq execution. Result is stored to destination after
execution - so any materialisation is necessary,

>
> I worry that we're getting further away from the original problem. Let's
> allow functions to get the bytes of data from a COPY, like the original
> proposal. I am not sure COPY is the best mechanism to move records
> around when INSERT ... SELECT already does that.
>

In one single case hack I prefer using any hook and feature stored
contrib. I don't see a general using for this feature.

Regards
Pavel Stehule

> Regards,
>        Jeff Davis
>
>