Re: Very slow queries on 8.1

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?

> 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';

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. If the result set is large then you can experience
performance problems due to a shortage of real memory.

How volatile is the data and how common are queries based on signum?
You might benefit from clustering on the signum index.

> (If necessary, I can write an entire script that creates and populates a
> table and then give my performance on that sample for someone else to
> check against.)

If it's a short script that populates the table with canned data
then go ahead and post it.

--
Michael Fuhr