Explain Analyze help
Explain Analyze help
От:
Mark Lubratt <mark.lubratt@indeq.com>
Дата:
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
Re: Explain Analyze help
От:
Peter Eisentraut <peter_e@gmx.net>
Дата:
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.