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

Re: [HACKERS] Performance testing of COPY (SELECT) TO

From: Böszörményi Zoltán <zboszor(at)dunaweb(dot)hu>
To: "Tom Lane" <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: pgsql-hackers(at)postgresql(dot)org, pgsql-patches(at)postgresql(dot)org
Subject: Re: [HACKERS] Performance testing of COPY (SELECT) TO
Date: 2006-08-25 13:40:38
Message-ID: 3839.213.163.11.81.1156513238.squirrel@www.dunaweb.hu (view raw or flat)
Thread:
Lists: pgsql-hackerspgsql-patches
> Böszörményi Zoltán <zboszor(at)dunaweb(dot)hu> writes:
>> With PostgreSQL 8.1.4, I used this:
>
>> begin;
>> select ... into temp myquery1;
>> copy myquery1 to stdout csv delimiter '|';
>> rollback;
>
> The performance of this would doubtless vary a lot with the temp_buffers
> setting.  Did you try different values?

Yes, I did, but now checked back with 8.2CVS.
The previously quoted result was achieved with
temp_buffers = 1000 on both 8.1.4 and 8.2CVS.
On 8.2CVS with temp_buffers = 4096, the 10 client case kills
the machine with swapping, but the 3 client runtime with
COPY(SELECT) went down to 2:41. The SELECT INTO TEMP
case went down to 3:36.

> It'd also be interesting to time the same way (with a temp table) in
> devel.  I don't remember whether we did any performance work on the
> COPY CSV data path in this cycle, or whether that was all present in
> 8.1.  In any case it'd be worth proving that the COPY SELECT patch isn't
> degrading performance of the copy-a-relation case.

I will report back with that, say on Monday.

In the meantime, I documented the COPY (SELECT) case
and modified parser/analyze.c and tcop/utility.c so neither of them
calls anything from under another directory. I think it's cleaner now.
Also, I tried to implement more closely what printtup() does.
Please, review.

Best regards,
Zoltán Böszörményi

Attachment: pgsql-copyselect-7.patch.gz
Description: application/x-gzip (11.1 KB)

In response to

Responses

pgsql-hackers by date

Next:From: Tom LaneDate: 2006-08-25 14:01:00
Subject: Re: Tricky bugs in concurrent index build
Previous:From: Peter EisentrautDate: 2006-08-25 13:35:38
Subject: Re: Safer auto-initdb for RPM init script

pgsql-patches by date

Next:From: Böszörményi ZoltánDate: 2006-08-25 14:18:42
Subject: Re: Performance testing of COPY (SELECT) TO
Previous:From: Tom LaneDate: 2006-08-25 12:53:07
Subject: Re: Performance testing of COPY (SELECT) TO

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