To: pgsql-sql@postgresql.org
From: Thomas Kellerer <spam_eater@gmx.net>
Subject: SELECT very slow
Date: Wed, 08 Jun 2005 23:46:41 +0200
Lines: 39
Message-ID: <d87ood$8vq$1@sea.gmane.org>
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii; format=flowed
Content-Transfer-Encoding: 7bit
User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.0; de-AT;
	rv:1.7.6) Gecko/20050319 Mnenhy/0.6.0.104

Hello,

I have a table with roughly 100,000 rows (four varchar(100) columns). This 
is basically test data I generated for something else. I'm using JDBC to 
access PG (but the behaviour is the same with psql).

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.

It seems that PG is actually building up the full result set in the 
background before delivering the first row. But I cannot see any of the 
processes (neither my Java process nor the PG processes) using a lot of 
memory - which I would expect if a result set of that size is created.

I need to test a program which should process large result sets (without 
loading it into memory) and waiting nearly three minutes before it actually 
starts working is a bit annoying :)
A SELECT count(*) FROM foobar is quite fast (about 2 seconds)

I hate to say this, but the same SELECT returns the first row more or less 
instantly with Firebird, SQL Server (MSDE) and HSQLDB.

Is there anything I can do, to convince PG to return the first row more 
quickly?

I tried a VACUUM FULL, no change. I increased the shared_buffers to 1000, 
no improvement either.

The execution plan is not really surprising:

Seq Scan on foobar  (cost=0.00..2510.04 rows=117504 width=63)


I'm using PG 8.0.3 on Win2K.

Thanks in advance
Thomas