I've run across a strange problem with PG 8.3.3 not using indexes on a
particular table after building the table during a transaction.
You can see a transcript of the issue here:
http://gist.github.com/21154
Interestingly, if I create another temp table 'CREATE TEMP TABLE AS
SELECT * FROM act' as seen on line 107, then add the same indexes to
that table, PG will use the indexes. While it's not in the gist
transcript, even an extremely simple query like:
SELECT * FROM act WHERE act_usr_id = 1;
will not use the index on the original act table, but the jefftest and
jefftest2 tables both work fine. As you can probably see in the
transcript, the tables have been ANALYZEd. I even tried 'enable
seqscan=0;' and that made the cost really high for the seq scan, but the
planner still chose the seq scan.
The issue does not affect 8.2.3 nor does it affect 8.3.4. I didn't see
any mention of a fix for this sort of thing in 8.3.4's release notes. I
was wondering if this is a known bug in 8.3.3 (and maybe other 8.3.x
versions) and just didn't make it into the release notes of 8.3.4?
--
Jeff Frost, Owner <jeff@frostconsultingllc.com>
Frost Consulting, LLC http://www.frostconsultingllc.com/
Phone: 916-647-6411 FAX: 916-405-4032