Re: feeding big script to psql - Mailing list pgsql-general

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.otto@freemail.hu>
>> To: <pgsql-general@postgresql.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.otto@freemail.hu>
>>> To: "Tom Lane" <tgl@sss.pgh.pa.us>
>>> Cc: <pgsql-general@postgresql.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@sss.pgh.pa.us>
>>>> To: "Havasvölgyi Ottó" <h.otto@freemail.hu>
>>>> Cc: <pgsql-general@postgresql.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.otto@freemail.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@postgresql.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@sidhe.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