pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Explain Analyze help
2+ messages / 2 participants
[nested] [flat]

* Explain Analyze help
@ 2004-03-09 22:48  Mark Lubratt <mark.lubratt@indeq.com>
  0 siblings, 1 reply; 2+ messages in thread

From: Mark Lubratt @ 2004-03-09 22:48 UTC (permalink / raw)
  To: pgsql-admin

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




^ permalink  raw  reply  [nested|flat] 2+ messages in thread

* Re: Explain Analyze help
@ 2004-03-11 09:02  Peter Eisentraut <peter_e@gmx.net>
  parent: Mark Lubratt <mark.lubratt@indeq.com>
  0 siblings, 0 replies; 2+ messages in thread

From: Peter Eisentraut @ 2004-03-11 09:02 UTC (permalink / raw)
  To: Mark Lubratt <mark.lubratt@indeq.com>; pgsql-admin

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.




^ permalink  raw  reply  [nested|flat] 2+ messages in thread


end of thread, other threads:[~2004-03-11 09:02 UTC | newest]

Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2004-03-09 22:48 Explain Analyze help Mark Lubratt <mark.lubratt@indeq.com>
2004-03-11 09:02 ` Peter Eisentraut <peter_e@gmx.net>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox