Michael Fuhr wrote:
On Thu, Nov 17, 2005 at 10:38:50AM -0500, David Rysdam wrote:
I'm porting an application from Sybase and I've noticed that similar
application functions take 2 to 3 times longer on postgres than they
used to on the same machine running under Sybase. I've tried changing
various "performance tuning" parameters, such as shared_buffers,
effective_cache_size, etc but there's little or no effect.
What's your OS and hardware environment and what are your non-default
PostgreSQL settings?
Fedora Core 2, dual 2.8 GHz, 2 GB ram.
shared_buffers = 10000
effective_cache_size = 100000
Right now, I'm working on a test case that involves a table with ~360k
rows called "nb.sigs". My sample query is:
select * from nb.sigs where signum > 250000
With no index, explain says this query costs 11341. After CREATE INDEX
on the signum field, along with an ANALYZE for nb.sigs, the query costs
3456 and takes around 4 seconds to return the first row.
Please post the EXPLAIN ANALYZE output for the query -- that'll
show us the query plan, the accuracy of the planner's row count
estimate, and how long the query takes to execute on the server.
It might also be useful to see the table definition and the output
of the following query:
SELECT null_frac, n_distinct, correlation
FROM pg_stats
WHERE schemaname = 'nb' AND tablename = 'sigs' AND attname = 'signum';
The first things would be problematic to supply, since they are actually
on a computer that doesn't have access to the Internet or to the machine
I'm writing this on. As for the query:
Row null_frac n_distinct correlation
1 0 -1 1
What client interface are you using? If the query returns a lot
of rows then you might benefit from using a cursor to fetch the
result set in chunks; otherwise the client library is probably
waiting for the entire result set to arrive before returning any
rows to you.
That does seem to be the problem. I've never worked with cursors, so
I'll have to see if I can fit our DB module into that mold.
---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend
