SEO

#1 (**permalink**) 04-10-2008, 10:41 AM

Hi,
before writing to this list I've tried to solve the problem
discussing it in the pgsql-performance mailing list but I wasn't able
to solve it.

The problem is as follows.

My test db only contains a
single table with some varying-text field and a bytea field.
When
inserting data into this table everyting works fine.
The problem is
that it takes too much time for the db to answer a simple select query
on the bytea field.
The test table has 36 records and each bytea field
contains a 250KB object. A (SELECT byteafield FROM table) takes more
than 1 minute to execute.

The table is called FILE

Here is the output
of explain analyze SELECT * FROM "FILE"

"Seq Scan on "FILE" (cost=0.
00..1.36 rows=36 width=235)
(actual time=0.023..0.107 rows=36 loops=1)
Total runtime:0.337 ms".

I'm accessing the db with jdbc but the
problem persists even with psql or pgAdmin.

Postgresql version: 8.2.3

OS: WIN XP (32-bit)

Thanks in advance for your help

Massimo

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org

#2 (**permalink**) 04-10-2008, 10:41 AM

"msmbarabino@virgilio.it" <msmbarabino@virgilio.it> writes:
> The test table has 36 records and each bytea field
> contains a 250KB object. A (SELECT byteafield FROM table) takes more
> than 1 minute to execute.

Hm, it takes about 4 seconds here, on a fairly old and slow machine:

$ time psql -A -c "select * from file" regression | wc
38 33699 26653873

real 0m4.00s
user 0m1.37s
sys 0m0.62s

In psql the trick is to use unaligned display mode, else psql itself
eats a whole lot of time trying to nicely format those 250KB rows:

$ time psql -c "select * from file" regression | wc
40 33700 28134681

real 0m57.10s
user 0m53.55s
sys 0m0.67s

I surmise that your problem is likewise on the client side, but you
weren't very specific about what client code you were using. Look
for bottlenecks associated with processing of very wide rows...

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

SEO

Very slow bytea extraction