Re: SELECT very slow

[PFC <lists(at)boutiquenumerique(dot)com>]

> The problem is, that a SELECT * FROM foobar; takes ages (roughly 3  
> minutes) to return the first row. I played around with the fetchSize()  
> to disable the result set caching in the Java program first (before I  
> tried psql) but that did not change anything.

	Hello,

	Yours seemed strange so I tried this :
	Created a table with 128K lines, 4 TEXT columns containing about 70 chars  
each...

---------------------------------------------
\d bigtest;
 Colonne |  Type   |                      Modificateurs
---------+---------+---------------------------------------------------------
 id      | integer | not null default  
nextval('public.bigtest_id_seq'::text)
 data1   | text    |
 data2   | text    |
 data3   | text    |
 data4   | text    |
Index :
    «bigtest_pkey» PRIMARY KEY, btree (id)

---------------------------------------------

SELECT count(*) from bigtest;
 count
--------
 131072

---------------------------------------------

explain analyze select * from bigtest;
                                                    QUERY PLAN
------------------------------------------------------------------------------------------------------------------
 Seq Scan on bigtest  (cost=0.00..7001.72 rows=131072 width=308) (actual  
time=0.035..484.249 rows=131072 loops=1)
 Total runtime: 875.095 ms

So grabbing the data takes 0.875 seconds.

---------------------------------------------

SELECT avg(length(data1)),  
avg(length(data2)),avg(length(data3)),avg(length(data3)) from bigtest;
         avg         |         avg         |         avg         |          
avg
---------------------+---------------------+---------------------+---------------------
 72.1629180908203125 | 72.2342376708984375 | 72.3680572509765625 |  
72.3680572509765625

Here you see the average data sizes.

---------------------------------------------

	Now I fire up python, do a SELECT * from the table and retrieve all the  
data as native objects... Hm, it takes about 1.3 seconds... on my  
Pentium-M 1600 laptop...

	I was about to suggest you use a less slow and bloated language than  
Java, but then on my machine psql takes about 5 seconds to display the  
results, so it looks like it ain't Java. psql is slow because it has to  
format the result and compute the column widths.

	Don't you have a problem somewhere ? Are you sure it's not swapping ? did  
you check memory ? Are you transferring all this data over the network ?  
Might an obscure cabling problem have reverted your connection to 10 Mbps ?

	I'm using pg 8.0.something on Linux.

	Ouch. I saw you're on Windows so I tried it on the windows machine there  
which has a postgres installed, over a 100Mbps network, querying from my  
linux laptop. The windows machine is a piece of crap, Pentium-II 300 and  
256 MB Ram, it takes 7 seconds to retrieve the whole table in a python  
native object.

	So...