Re: [PERFORM] Bytea poor performance
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 ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [PERFORM] Bytea poor performance
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 ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[PERFORM] 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. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [PERFORM] Bytea poor performance
On 10/15/05 9:20 AM, NSO [EMAIL PROTECTED] 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
Re: [PERFORM] Bytea poor performance
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 [EMAIL PROTECTED] 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. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [PERFORM] Bytea poor performance
On 10/15/05 10:00 AM, NSO [EMAIL PROTECTED] 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 [EMAIL PROTECTED] 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. ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [PERFORM] Bytea poor performance
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 [EMAIL PROTECTED] 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 [EMAIL PROTECTED] 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. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [PERFORM] Bytea poor performance
NSO [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [PERFORM] Bytea poor performance
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 [EMAIL PROTECTED] 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. ---(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
Re: [PERFORM] Bytea poor performance
NSO [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
