I am looking for some specific information regarding optimizer behavior. We recently experienced a situation where a query that was previously using a btree lookup (efficient) SWITCHED to using seqscan/hash lookup.
My questions would be: - Under what circumstances is the optimizer likely to CHANGE behavior from using a btree index lookup to using a seq scan/hash lookup? - What are the critical decision factors that would feed into the optimizer making such a change? - Is it possible to measure any metrics in a way that would enable a prediction of such a change? Platform - 8.3.10 (64bit) on RHEL5. - Linux xxxxx 2.6.18-164.10.1.el5xen #1 SMP Thu Jan 7 20:28:30 EST 2010 x86_64 x86_64 x86_64 GNU/Linux Application The table in question is: - 30m rows, variable length (contains varchar fields), rowlength avg about 120B - Approx. 3m unique values in the index column Activity on the table would be, per row: "Insert, multiple updates, delete after 90 days" We vacuum analyze this table once/weekly. No partitions are used. Our experience which prompts this question was as follows: - If the table is not "vacuum analyze'd" at least once/week, the query plans become unpredictable as to whether they will use btree or seqscan/hash lookup - Until last week, "vacuum analyze" was sufficient - Friday evening of last week, the query plan for selected queries against this index changed again, but "vacuum analyze" was insufficient - Rebuilding index on primary key and on the column index was insufficient - It was necessary to take a site outage and perform a "vacuum full analyze" on the table - Following this, the query plan reverted to the more efficient btree lookup Clearly, the garbage buildup resulting from transaction activity on the table is the villain here. - Is it possible to calculate expected space usage given row count and average row size - At what point might the ratio of "expected"/"actual" space usage be able to indicate the need to perform "full vacuum", or similar maintenance Any observations/comments that anyone would care to make are welcome. Thanks in advance for your time Mr
