Query Costs

A query cost is measured in 'funny money'. It only has meaning when comparing the various query plans for a given query.

The best way to explain the process is to consider a very simple example. Suppose we do a join between two tables where there is a 1 to 1 relationship between each of the tables. Now, suppose that there is a where clause on some column on each of the two tables. If the where for table1 is more selective than the where clause for table 2, then by using the filter on table 1 and the join index to table 2 would be much more selective than going in the other direction. If the filter on table1 would select 0.02 of the rows and the filter on table2 was 0.10, then we could compare the query plan of selecting from table1 and then joining each row to table2 versus selecting from table2 and then joining each row to table1 by simply multiplying the selectivity by the number of rows in the table. Thus if there were 1000 rows in both of the tables, the first query plan would be 1000 * 0.02 -> 20 while the cost of the second plan would be 1000 * 0.10 or 100. The first plan is cheaper and selected. This example also indicates why update statistics are so important, if the optimiser doesn't have accurate data distributions it will cost queries incorrectly.

However, you can do some things to check to see if you have questionable queries by examining our sqexplain output, or by examining syssqexplain from the sysmaster database. The syssqexplan view in sysmaster is a display of any of the current queries within the database. Check for things like sqx_seqscan, sqx_srtscan, sqx_autoindex, sqx_tempfile. These are the things that you might want to improve and try to eliminate.

The exact weights the optimiser places on individual access methods is detailed in the Performance Manual

Note: this is based on an article posted to the CDI newsgroup by Madison Pruet