SQL FAQ: Query Performance
Why does my query take so long to run?
There could be a number of reasons. Among them might be:
- Are there any indices on your table?
- Have you vacuumed lately?
- Is Illustra using any available indices?
How does running vacuum make queries run faster?
The query optimizer uses statistics gathered on a table to determine
the fastest way to execute a query.
You can look at some of this information with the
ml_cstat()
and ml_tstat()
functions.
If statistics get out of date, for example if the distribution of
values in a column changes dramatically, the optimizer might choose
a slower way to execute the query.
The vacuum command invoked with the statistics modifier
updates statistics.
Creating an index automatically updates the statistics for the
column(s) being indexed.
How can I tell what indices a table has?
The ml_tindex() function lists all indices
on a table; for example, the following query list all indices on the
tables system catalog:
* select * from ml_tindex('tables');
---------------------------------------------------------
|table_name |index_name |archived |keys |
---------------------------------------------------------
|tables |tablenameind |f | Column 19 |
|tables |tableidind |f | Column -2 |
|tables |tabletypeind |f | Column 17 |
---------------------------------------------------------
3 rows selected
How can I tell which index Illustra is using?
Call the trace function with the 'Planner.1' argument.
For more information, see Appendix E of the Illustra User's Guide.
Can I tell Illustra which index to use?
You can provide hints to the Illustra optimizer,
including which index to use, with the using clause.
For more information, see Appendix E of the Illustra User's Guide.
Back to top level FAQ.
Last modified 30-June-94 (Illustra Rev 2.0.12)
Jean Anderson (jta@postgres.berkeley.edu)