2009/11/25 Jeff Davis <pg...@j-davis.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 > > -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers