Thread: Explain Analyze help

I've just completed my application and my customer is starting to
populate the database.  I've put a table in where queries get logged so
that I can start pinpointing my optimizations to the most frequently
used queries.  I've read the manual on the explain analyze statement
and I have one (which will probably turn into many) question.

Obivously, I have a lot of sequential scans going on, at about what
cost does it start to make sense to make an index?  Or, should I just
put in a bunch of indexes and let the optimizer decide whether or not
to use them?

OK, so it was 2 questions.

Anyone?  Bueller?

Mark

Mark Lubratt wrote:
> Obivously, I have a lot of sequential scans going on, at about what
> cost does it start to make sense to make an index?  Or, should I just
> put in a bunch of indexes and let the optimizer decide whether or not
> to use them?

Read the chapters on Indexes and Performance Tips in the documentation.
They contain various example scenarios where indexes are useful.  Keep
in mind that indexes are useful only if your table is large (say, 10000
rows or more) and the fraction you select is small (say, 1%).  The
costs that EXPLAIN prints out are not meaningful absolute values; they
are only useful to compare two plans of the same query.  So in absence
of a better approach, your job is to

1. EXPLAIN ANALYZE without index
2. create index
3. EXPLAIN ANALYZE with index

(And don't forget to run ANALYZE sometime.)

If there is a significant improvement, then keep the index.  Remember,
too many indexes slow down updates.