Re: [PERFORM] Very slow bytea data extraction
[EMAIL PROTECTED] wrote: If you look at the actual time it's completing very quickly indeed. So - it must be something to do with either: 1. Fetching/formatting the data 2. Transferring the data to the client. I do agree. What happens if you only select half the rows? Does the time to run the select halve? Yes, it does. Using pgAdmin, the time to get all 36 rows is about 67500ms while it's 24235ms to get only 18 rows. Hmm - I've seen reports about the traffic-shaping module not being install/activated making large data transfers slow. That was on Windows 2000 though. Might be worth searching the mail archives - I'm afraid I run PG on Linux mostly, so can't say for sure. One other thing I'd test. Make a small table with text columns of the same size and see how fast it is to select from that. If it's just as slow then it's your network setup. If it's much faster then it's something to do with the bytea type. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [PERFORM] Very slow bytea data extraction
[EMAIL PROTECTED] wrote: Hi all, I'm using Postgresql 8.2.3 on a Windows XP system. I need to write and retrieve bytea data from a table. The problem is that, while data insertion is quite fast, bytea extraction is very slow. I'm trying to store a 250KB image into the bytea field. A simple select query on a 36-row table takes more than one minute to execute. Where is the problem? Is it in executing the query (what does EXPLAIN ANALYSE show)? Is it in fetching/formatting the data (what does the equivalent COUNT(*) show)? How are you accessing the database: odbc,jdbc,other? Does it do this with psql too? -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [PERFORM] Very slow bytea data extraction
If you look at the actual time it's completing very quickly indeed. So - it must be something to do with either: 1. Fetching/formatting the data 2. Transferring the data to the client. I do agree. What happens if you only select half the rows? Does the time to run the select halve? Yes, it does. Using pgAdmin, the time to get all 36 rows is about 67500ms while it's 24235ms to get only 18 rows. Massimo ---(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] Very slow bytea data extraction
are you getting the data from the local box or from a remote site? Everything is on the local box. also explain analyze is showing nothing slow but you did not post the enitre output. also, try the \timing switch in psql. Actually a line was missing: Total runtime: 0.337 ms. Massimo ---(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