pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedVery slow bytea data extraction
5+ messages / 2 participants
[nested] [flat]
* Very slow bytea data extraction
@ 2007-02-23 10:10 msmbarabino@virgilio.it <msmbarabino@virgilio.it>
2007-02-23 10:46 ` Re: Very slow bytea data extraction Richard Huxton <dev@archonet.com>
0 siblings, 1 reply; 5+ messages in thread
From: msmbarabino@virgilio.it @ 2007-02-23 10:10 UTC (permalink / raw)
To: pgsql-performance
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Very slow bytea data extraction
2007-02-23 10:10 Very slow bytea data extraction msmbarabino@virgilio.it <msmbarabino@virgilio.it>
@ 2007-02-23 10:46 ` Richard Huxton <dev@archonet.com>
0 siblings, 0 replies; 5+ messages in thread
From: Richard Huxton @ 2007-02-23 10:46 UTC (permalink / raw)
To: msmbarabino@virgilio.it <msmbarabino@virgilio.it>; +Cc: pgsql-performance
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Very slow bytea data extraction
@ 2007-02-23 17:44 msmbarabino@virgilio.it <msmbarabino@virgilio.it>
2007-02-26 08:56 ` Re: Very slow bytea data extraction Richard Huxton <dev@archonet.com>
0 siblings, 1 reply; 5+ messages in thread
From: msmbarabino@virgilio.it @ 2007-02-23 17:44 UTC (permalink / raw)
To: pgsql-performance
>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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Very slow bytea data extraction
2007-02-23 17:44 Re: Very slow bytea data extraction msmbarabino@virgilio.it <msmbarabino@virgilio.it>
@ 2007-02-26 08:56 ` Richard Huxton <dev@archonet.com>
0 siblings, 0 replies; 5+ messages in thread
From: Richard Huxton @ 2007-02-26 08:56 UTC (permalink / raw)
To: msmbarabino@virgilio.it <msmbarabino@virgilio.it>; +Cc: pgsql-performance
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Very slow bytea data extraction
@ 2007-02-23 17:55 msmbarabino@virgilio.it <msmbarabino@virgilio.it>
0 siblings, 0 replies; 5+ messages in thread
From: msmbarabino@virgilio.it @ 2007-02-23 17:55 UTC (permalink / raw)
To: pgsql-performance
>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
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2007-02-26 08:56 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2007-02-23 10:10 Very slow bytea data extraction msmbarabino@virgilio.it <msmbarabino@virgilio.it>
2007-02-23 10:46 ` Richard Huxton <dev@archonet.com>
2007-02-23 17:44 Re: Very slow bytea data extraction msmbarabino@virgilio.it <msmbarabino@virgilio.it>
2007-02-26 08:56 ` Richard Huxton <dev@archonet.com>
2007-02-23 17:55 Re: Very slow bytea data extraction msmbarabino@virgilio.it <msmbarabino@virgilio.it>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox