Re: SELECT very slow - Mailing list pgsql-sql

> 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
Scanon 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...