Thread: Very slow bytea data extraction
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. Any help would be very appreciated Thanks in advance Massimo
msmbarabino@virgilio.it 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
>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
>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
msmbarabino@virgilio.it 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