Thread: Bytea poor performance
Hello, I am trying to select form table with bytea field. And queries runs very slow. My table: CREATE TABLE files (file bytea, nr serial NOT NULL) WITH OIDS; Query: select * from files where nr > 1450 (I have total 1500 records in it, every holds picture of 23kB size) Query runs very long: Total query runtime: 23625 ms. Data retrieval runtime: 266 ms. 50 rows retrieved. explain: Index Scan using pk on files (cost=0.00..3.67 rows=50 width=36) Index Cond: (nr > 1450) Is it possible to do something with it? or it is normal? Our server is fast, and all other tables work fine.. -- This message has been scanned for viruses and dangerous content, and is believed to be clean.
On 10/15/05 9:20 AM, "NSO" <nso@fmf.vtu.lt> wrote: > Hello, > > I am trying to select form table with bytea field. And queries runs very > slow. > My table: > CREATE TABLE files (file bytea, nr serial NOT NULL) WITH OIDS; > > Query: > select * from files where nr > 1450 > > (I have total 1500 records in it, every holds picture of 23kB size) > Query runs very long: > Total query runtime: 23625 ms. > Data retrieval runtime: 266 ms. > 50 rows retrieved. > > explain: > Index Scan using pk on files (cost=0.00..3.67 rows=50 width=36) > Index Cond: (nr > 1450) > > Is it possible to do something with it? or it is normal? Our server is > fast, and all other tables work fine.. How about some explain analyze output? Have you done a full vacuum lately? How about reindexing? Sean
Hello, How about some explain analyze output? Explain analyse select * from files where nr > 1450 "Index Scan using pk on files (cost=0.00..3.67 rows=50 width=36) (actual time=0.000..0.000 rows=50 loops=1)" Have you done a full vacuum lately? How about reindexing? Yes, I did reindexing and vacuum full just before query.. > On 10/15/05 9:20 AM, "NSO" <nso@fmf.vtu.lt> wrote: > >> Hello, >> >> I am trying to select form table with bytea field. And queries runs very >> slow. >> My table: >> CREATE TABLE files (file bytea, nr serial NOT NULL) WITH OIDS; >> >> Query: >> select * from files where nr > 1450 >> >> (I have total 1500 records in it, every holds picture of 23kB size) >> Query runs very long: >> Total query runtime: 23625 ms. >> Data retrieval runtime: 266 ms. >> 50 rows retrieved. >> >> explain: >> Index Scan using pk on files (cost=0.00..3.67 rows=50 width=36) >> Index Cond: (nr > 1450) >> >> Is it possible to do something with it? or it is normal? Our server is >> fast, and all other tables work fine.. > > How about some explain analyze output? Have you done a full vacuum > lately? > How about reindexing? > > Sean > > > ---------------------------(end of broadcast)--------------------------- > TIP 6: explain analyze is your friend > > -- > This message has been scanned for viruses and > dangerous content, and is believed to be clean. > > -- This message has been scanned for viruses and dangerous content, and is believed to be clean.
On 10/15/05 10:00 AM, "NSO" <nso@fmf.vtu.lt> wrote: > Hello, > > How about some explain analyze output? > Explain analyse select * from files where nr > 1450 > > "Index Scan using pk on files (cost=0.00..3.67 rows=50 width=36) > (actual time=0.000..0.000 rows=50 loops=1)" I may not be understanding the output, but your actual time reports 0 for the query. And the total runtime is 23 seconds? Sean >> On 10/15/05 9:20 AM, "NSO" <nso@fmf.vtu.lt> wrote: >> >>> Hello, >>> >>> I am trying to select form table with bytea field. And queries runs very >>> slow. >>> My table: >>> CREATE TABLE files (file bytea, nr serial NOT NULL) WITH OIDS; >>> >>> Query: >>> select * from files where nr > 1450 >>> >>> (I have total 1500 records in it, every holds picture of 23kB size) >>> Query runs very long: >>> Total query runtime: 23625 ms. >>> Data retrieval runtime: 266 ms. >>> 50 rows retrieved. >>> >>> explain: >>> Index Scan using pk on files (cost=0.00..3.67 rows=50 width=36) >>> Index Cond: (nr > 1450) >>> >>> Is it possible to do something with it? or it is normal? Our server is >>> fast, and all other tables work fine.. >> >> How about some explain analyze output? Have you done a full vacuum >> lately? >> How about reindexing? >> >> Sean >> >> >> ---------------------------(end of broadcast)--------------------------- >> TIP 6: explain analyze is your friend >> >> -- >> This message has been scanned for viruses and >> dangerous content, and is believed to be clean. >> >> > >
Yes, it takes even up to 35 seconds.. I did the same query on the server (not PC with was connected directly to server with 100mbit net), and /I got better result it is 3.5 - 4 seconds, but it still not good.. Why it is slow? and why the difference is so big? I mean from 4 to 35 seconds? thx > On 10/15/05 10:00 AM, "NSO" <nso@fmf.vtu.lt> wrote: > >> Hello, >> >> How about some explain analyze output? >> Explain analyse select * from files where nr > 1450 >> >> "Index Scan using pk on files (cost=0.00..3.67 rows=50 width=36) >> (actual time=0.000..0.000 rows=50 loops=1)" > > I may not be understanding the output, but your actual time reports 0 for > the query. And the total runtime is 23 seconds? > > Sean > > >>> On 10/15/05 9:20 AM, "NSO" <nso@fmf.vtu.lt> wrote: >>> >>>> Hello, >>>> >>>> I am trying to select form table with bytea field. And queries runs >>>> very >>>> slow. >>>> My table: >>>> CREATE TABLE files (file bytea, nr serial NOT NULL) WITH OIDS; >>>> >>>> Query: >>>> select * from files where nr > 1450 >>>> >>>> (I have total 1500 records in it, every holds picture of 23kB size) >>>> Query runs very long: >>>> Total query runtime: 23625 ms. >>>> Data retrieval runtime: 266 ms. >>>> 50 rows retrieved. >>>> >>>> explain: >>>> Index Scan using pk on files (cost=0.00..3.67 rows=50 width=36) >>>> Index Cond: (nr > 1450) >>>> >>>> Is it possible to do something with it? or it is normal? Our server is >>>> fast, and all other tables work fine.. >>> >>> How about some explain analyze output? Have you done a full vacuum >>> lately? >>> How about reindexing? >>> >>> Sean >>> >>> >>> ---------------------------(end of >>> broadcast)--------------------------- >>> TIP 6: explain analyze is your friend >>> >>> -- >>> This message has been scanned for viruses and >>> dangerous content, and is believed to be clean. >>> >>> >> >> > > > -- > This message has been scanned for viruses and > dangerous content, and is believed to be clean. > > -- This message has been scanned for viruses and dangerous content, and is believed to be clean.
"NSO" <nso@fmf.vtu.lt> writes:
> Query runs very long:
> Total query runtime: 23625 ms.
> Data retrieval runtime: 266 ms.
> 50 rows retrieved.
Notice that the query itself took 266ms. The rest of the time was
wasted by your client app trying to format a 23Kb by 50 row table
for display. You need to replace your client-side code with something
less inefficient about handling wide values.
regards, tom lane
Hello, Yes, I can understand that, but then why the same app on the server machine is done in 4 seconds? (big difference from 20-30 seconds). I tryed to monitor network traffic and it is used only for 1-2% of total 100mbit. > "NSO" <nso@fmf.vtu.lt> writes: >> Query runs very long: >> Total query runtime: 23625 ms. >> Data retrieval runtime: 266 ms. >> 50 rows retrieved. > > Notice that the query itself took 266ms. The rest of the time was > wasted by your client app trying to format a 23Kb by 50 row table > for display. You need to replace your client-side code with something > less inefficient about handling wide values. > > regards, tom lane > > -- > This message has been scanned for viruses and > dangerous content, and is believed to be clean. > > -- This message has been scanned for viruses and dangerous content, and is believed to be clean.
"NSO" <nso@fmf.vtu.lt> writes:
> Yes, I can understand that, but then why the same app on the server
> machine is done in 4 seconds? (big difference from 20-30 seconds).
That would suggest a networking problem, which is a bit outside my
expertise. If the client machine is running Windows, we have seen
problems of that sort before from (IIRC) various third-party add-ons
that fool around with networking behavior. Try searching the archives.
regards, tom lane
NSO wrote: > Hello, > > Yes, I can understand that, but then why the same app on the server > machine is done in 4 seconds? (big difference from 20-30 seconds). I > tryed to monitor network traffic and it is used only for 1-2% of total > 100mbit. > Is this a web app? If so, then check you are using the same browser settings on the server and client (or the same browser for that matter). Note that some browsers really suck for large (wide or long) table display! cheers Mark
Hello, No it is not web app, I tested on simple delphi app and with PGAdmin III.. same results.. Query from PGAdmin takes up to 30seconds... > NSO wrote: >> Hello, >> >> Yes, I can understand that, but then why the same app on the server >> machine is done in 4 seconds? (big difference from 20-30 seconds). I >> tryed to monitor network traffic and it is used only for 1-2% of total >> 100mbit. >> > > Is this a web app? If so, then check you are using the same browser > settings on the server and client (or the same browser for that matter). > > Note that some browsers really suck for large (wide or long) table > display! > > cheers > > Mark > > > ---------------------------(end of broadcast)--------------------------- > TIP 3: Have you checked our extensive FAQ? > > http://www.postgresql.org/docs/faq > > -- > This message has been scanned for viruses and > dangerous content, and is believed to be clean. > > -- This message has been scanned for viruses and dangerous content, and is believed to be clean.
NSO wrote: >Hello, > > No it is not web app, I tested on simple delphi app and with PGAdmin >III.. same results.. Query from PGAdmin takes up to 30seconds... > > Displaying the data can take a long time on several platforms for pgAdmin; complex controls tend to be dead slow on larger data sets. We're waiting for a better wxWidgets solution, I doubt delphi is better... Regards, Andreas
Well, no. Delphi isn't better, same time just for downloading data... But as I told before, if for ex. pgAdminIII is running on server machine it is a lot faster, I do not know why, I was monitoring network connection between client and server and it is using only up to 2% of full speed.. is server can't send faster? or client is not accepting data faster? > NSO wrote: > >>Hello, >> >> No it is not web app, I tested on simple delphi app and with PGAdmin >>III.. same results.. Query from PGAdmin takes up to 30seconds... >> >> > Displaying the data can take a long time on several platforms for > pgAdmin; complex controls tend to be dead slow on larger data sets. > We're waiting for a better wxWidgets solution, I doubt delphi is better... > > Regards, > Andreas > > > -- > This message has been scanned for viruses and > dangerous content, and is believed to be clean. > > -- This message has been scanned for viruses and dangerous content, and is believed to be clean.
NSO wrote: >Well, no. Delphi isn't better, same time just for downloading data... But >as I told before, if for ex. pgAdminIII is running on server machine it is >a lot faster, I do not know why, I was monitoring network connection >between client and server and it is using only up to 2% of full speed.. is >server can't send faster? or client is not accepting data faster? > > Only the first number is relevant and subject to network/db/server issues. The second is GUI only. Regards, Andreas
NSO wrote: > > Well, no. Delphi isn't better, same time just for downloading data... But > as I told before, if for ex. pgAdminIII is running on server machine it is > a lot faster, I do not know why, I was monitoring network connection > between client and server and it is using only up to 2% of full speed.. is > server can't send faster? or client is not accepting data faster? > > That difference is suspiciously high - you need to get one of your network boys to check that the NIC in your client box is operating at full speed (and/or does not clash with whatever network device it is plugged into). The other thing to check that that your client box is reasonably spec'ed : e.g. not running out of ram or disk in particular - or suffering from massively fragmented disk (the latter if its win32). With respect to the Delphi, you can probably narrow where it has issues by running test versions of your app that have bits of functionality removed: - retrieves the bytea but does not display it - retrieves the bytea but displays it unformatted, or truncated - does not retrieve the bytea at all The difference between these should tell you where your issue is! By way of comparison, I have a Php page (no Delphi sorry) that essentially shows 50 rows from your files table over a 100Mbit network. Some experiments with that show: - takes 2 seconds to display in Firefox - takes 0.2 seconds to complete a request (i.e. "display") using httperf This indicates that (in my case) most of the 2 seconds is being used by Firefox (not being very good at) formatting the wide output for display. The figure of about 2-5 seconds seems about right, so your 20-30 seconds certainly seems high! cheers Mark