Forcing the planner's hand with set enable_seqscan off WTF   

We checked our dev server which has the same PostgreSQL 8.4.2 install as production (both running on Windows though production is a Windows 2008 and dev Windows 2003), and data that was probably about two weeks older on dev. Dev was working perfectly and production was not.

Then we vacuum analyzed some critical tables on Dev and Dev started to behave in the same slow stupid way with the same stupid plan. Vacuum analyzing is supposed to help the situation? After analyzing the plans, we realized what had changed before vacuum analyze and after was that, it stopped using this critical index on the child nodes.

Our query reduced down to what we think is the problem area. In a very watered down form it looked something like this:

    SELECT item_name, geom
        FROM items 
        WHERE items.feature_type 
            IN(SELECT f.feature_type 
                FROM lufeature_types As f 
                WHERE f.grouping IN('G','K') );

That used to use the btree index we had on feature_type for all the tables but then suddenly stopped. Changing it to an explicit IN list where the IN list is what the subquery would return made it use the btree index again, but that was a very undesirable thing to do because we like our code as short as possible, and such a stupid solution would require writing a query -- parsing it into an array and then feeding it to a second query. And no even making the subquery a CTE did not help.

SELECT item_name, geom
    FROM items 
    WHERE items.feature_type IN('A', 'B','C');

We finally ended up changing the code to do this which allowed us to keep the SQL statement as we liked it and still use the index. But the idea of giving the planner these kinds of hints especially when it never needed it before, I've always found a bit unpleasant.

set enable_seqscan = off;
SELECT item_name, geom
    FROM items 
WHERE items.feature_type 
    IN(SELECT f.feature_type
        FROM lufeature_types As f 
        WHERE f.grouping IN('G','K')
        );

For those unfamiliar with the enable_seqscan setting, it doesn't disable sequential scanning completely to set it to off since well in some cases there is no alternative. It does make the planner use sequential scanning as a last resort when there is no index it can possibly use. Sequential scans are not always a bad thing even if you do have indexes since an index scan is not a completely free strategy.