Can you show explain with analyze and buffers options for your query? Regards, Roman Konoval rkono...@gmail.com
> On Aug 24, 2017, at 15:15, Mariel Cherkassky <mariel.cherkas...@gmail.com> > wrote: > > I'm trying to understand what postgresql doing in an issue that I'm having. > Our app team wrote a function that runs with a cursor over the results of a > query and via the utl_file func they write some columns to a file. I dont > understand why, but postgresql write the data into the file in the fs in > parts. I mean that it runs the query and it takes time to get back results > and when I see that the results back postgresql write to file the data and > then suddenly stops for X minutes. After those x minutes it starts again to > write the data and it continues that way until its done. The query returns > total 100 rows. I want to understand why it stops suddenly. There arent any > locks in the database during this operation. > > my function looks like that : > > func(a,b,c...) > > cursor cr for > > select ab,c,d,e..... > > begin > > raise notice - 'starting loop time - %',timeofday(); > > for cr_record in cr > > Raise notice 'print to file - '%',timeofday(); > > utl_file.write(file,cr_record) > > end loop > > end > > I see the log of the running the next output : > > starting loop 16:00 > > print to file : 16:03 > > print to file : 16:03 > > print to file : 16:07 > > print to file : 16:07 > > print to file : 16:07 > > print to file : 16:010 > > ...... > > > > > > Can somebody explain to me this kind of behavior ? Why is it taking some much > time to write and in different minutes after the query already been executed > and finished ? Mybe I'm getting from the cursor only part of the rows ? > >
