Re: feeding big script to psql

Dan Sugalski wrote:
> At 1:57 PM +0200 8/2/05, Havasvölgyi Ottó wrote:
>> Hi,
>>
>> Now I am at 7 MB, and the reading speed is 3-4KB/sec.
>
> Have you checked to see if you're swapping as this goes on, either in
> the client or on the server?
>
>> ----- Original Message ----- From: "Havasvölgyi Ottó"
>> <h(dot)otto(at)freemail(dot)hu>
>> To: <pgsql-general(at)postgresql(dot)org>
>> Sent: Tuesday, August 02, 2005 1:31 PM
>> Subject: Re: [GENERAL] feeding big script to psql
>>
>>> Hi,
>>>
>>> The effect is the same even if I redirect the output to file with the
>>> -o switch.
>>> At the beginning 200 KB/sec, at 1.5 MB the speed is less than 20 KB/sec.
>>>
>>> Best Regards,
>>> Otto
>>>
>>>
>>>
>>> ----- Original Message ----- From: "Havasvölgyi Ottó"
>>> <h(dot)otto(at)freemail(dot)hu>
>>> To: "Tom Lane" <tgl(at)sss(dot)pgh(dot)pa(dot)us>
>>> Cc: <pgsql-general(at)postgresql(dot)org>
>>> Sent: Tuesday, August 02, 2005 11:24 AM
>>> Subject: Re: [GENERAL] feeding big script to psql
>>>
>>>> Tom,
>>>>
>>>> Thanks for the suggestion. I have just applied both switch , -f (I
>>>> have applied this in the previous case too) and -n, but it becomes
>>>> slow again. At the beginning it reads about 300 KB a second, and
>>>> when it has read 1.5 MB, it reads only about 10 KB a second, it
>>>> slows down gradually. Maybe others should also try this scenario.
>>>> Can I help anything?
>>>>
>>>> Best Regards,
>>>> Otto
>>>>
>>>>
>>>> ----- Original Message ----- From: "Tom Lane" <tgl(at)sss(dot)pgh(dot)pa(dot)us>
>>>> To: "Havasvölgyi Ottó" <h(dot)otto(at)freemail(dot)hu>
>>>> Cc: <pgsql-general(at)postgresql(dot)org>
>>>> Sent: Tuesday, August 02, 2005 3:54 AM
>>>> Subject: Re: [GENERAL] feeding big script to psql
>>>>
>>>>> =?ISO-8859-1?Q?Havasv=F6lgyi_Ott=F3?= <h(dot)otto(at)freemail(dot)hu> writes:
>>>>>> I know it would be faster with COPY, but this is extremly slow,
>>>>>> and the
>>>>>> bottleneck is psql.
>>>>>> What is the problem?
>>>>>
>>>>> Hmm, does the Windows port have readline support, and if so does
>>>>> adding
>>>>> the "-n" switch to the psql invocation fix the problem? Or you could
>>>>> try feeding the script with -f switch or \i rather than "psql
>>>>> <script".
>>>>> Readline adds a fair amount of overhead, which is completely invisible
>>>>> at human typing speeds but can be annoying when reading scripts.
>>>>>
>>>>> regards, tom lane
>>>>>
>>>>
>>>>
>>>>
>>>> ---------------------------(end of
>>>> broadcast)---------------------------
>>>> TIP 1: if posting/reading through Usenet, please send an appropriate
>>>> subscribe-nomail command to majordomo(at)postgresql(dot)org so that your
>>>> message can get through to the mailing list cleanly
>>>>
>>>
>>>
>>>
>>> ---------------------------(end of broadcast)---------------------------
>>> TIP 4: Have you searched our list archives?
>>>
>>> http://archives.postgresql.org
>>>
>>
>>
>>
>> ---------------------------(end of broadcast)---------------------------
>> TIP 9: In versions below 8.0, the planner will ignore your desire to
>> choose an index scan if your joining column's datatypes do not
>> match
>
>
> --
> Dan
>
> --------------------------------------it's like this-------------------
> Dan Sugalski even samurai
> dan(at)sidhe(dot)org have teddy bears and even
> teddy bears get drunk
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: Don't 'kill -9' the postmaster
>

Have you tried inserting VACUUM commands into the script every now and then?

I found a while ago that after inserting a lot of rows into a clean
Postgres table it would take several minutes just to analyse a command,
not even starting the execution. That was on version 8.0. On version
7.4.x the query never returned at all.

Pete
--
Peter Wilson - YellowHawk Ltd : http://www.yellowhawk.co.uk