SELECT very slow
От | Thomas Kellerer |
---|---|
Тема | SELECT very slow |
Дата | |
Msg-id | d87ood$8vq$1@sea.gmane.org обсуждение исходный текст |
Ответы |
Re: SELECT very slow
Re: SELECT very slow |
Список | pgsql-sql |
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
В списке pgsql-sql по дате отправления: