Skip site navigation (1) Skip section navigation (2)

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

From: Jeff Davis <pgsql(at)j-davis(dot)com>
To: Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>
Cc: Daniel Farina <drfarina(at)gmail(dot)com>, Hannu Krosing <hannu(at)krosing(dot)net>, Greg Smith <greg(at)2ndquadrant(dot)com>, Robert Haas <robertmhaas(at)gmail(dot)com>, Daniel Farina <dfarina(at)truviso(dot)com>, pgsql-hackers(at)postgresql(dot)org
Subject: Re: [PATCH 4/4] Add tests to dblink covering use of COPY TO FUNCTION
Date: 2009-11-25 20:56:09
Message-ID: 1259182569.19289.280.camel@jdavis (view raw or flat)
Thread:
Lists: pgsql-hackers
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


In response to

Responses

pgsql-hackers by date

Next:From: Peter EisentrautDate: 2009-11-25 21:07:59
Subject: Re: cvs chapters in our docs
Previous:From: David ChristensenDate: 2009-11-25 20:53:34
Subject: Spontaneous PITR standby activiation

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group