Re: Where do a novice do to make it run faster?

A B wrote:
> So, it is time to improve performance, it is running to slow.
> AFAIK (as a novice) there are a few general areas:
>
> 1) hardware
> 2) rewriting my queries and table structures
> 3) using more predefined queries
> 4) tweek parameters in the db conf files
>
> Of these points:
> 1) is nothing I can do about right now, but in the future perhaps.
> 2) will be quite hard right now since there is more code than time.
> 3) almost like 2 but perhaps more do-able with the current constraints.
> 4) This seems to be the easiest one to start with...
>
> So what should I do/read concerning point 4?
> If you have other good suggestions I'd be very interested in that.
>
Go back to step zero - gather information that would be helpful in
giving advice. For starters:
- What hardware do you currently have?
- What OS and version of PG?
- How big is the database?
- What is the nature of the workload (small queries or data-mining, how
many simultaneous clients, transaction rate, etc.)?
- Is PG sharing the machine with other workloads?

Then edit your postgresql.conf file to gather data (see
http://www.postgresql.org/docs/8.3/interactive/monitoring-stats.html)
With stat collection enabled, you can often find some low-hanging fruit
like indexes that aren't used (look in pg_stat_user_indexes) - sometime
because the query didn't case something in the where-clause correctly.

Also look at
http://www.postgresql.org/docs/8.3/interactive/runtime-config-logging.html
- especially the log_min_duration_statement setting to find long-running
queries. You will probably need to try different settings and watch the
log. Logging impacts performance so don't just set to log everything and
forget. You need to play with it.

Don't discount step 2 - you may find you can rewrite one inefficient but
frequent query. Or add a useful index on the server.

Cheers,
Steve