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

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.

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.

Regards,
Jeff Davis