From: Thomas Kellerer <spam_eater@gmx.net>
To: pgsql-sql@postgresql.org
Subject: SELECT very slow
Date: Wed, 08 Jun 2005 23:46:41 +0200
Message-ID: <d87ood$8vq$1@sea.gmane.org> (raw)
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: spam_eater@gmx.net
Subject: Re: SELECT very slow
In-Reply-To: <d87ood$8vq$1@sea.gmane.org>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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