Index usage problem on 8.3.3 - Mailing list pgsql-performance

From Jeff Frost
Subject Index usage problem on 8.3.3
Date
Msg-id 490A4EBE.9030804@frostconsultingllc.com
Whole thread Raw
Responses Re: Index usage problem on 8.3.3  (Tom Lane <tgl@sss.pgh.pa.us>)
List pgsql-performance
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


pgsql-performance by date:

Previous
From: "Oliver Johnson"
Date:
Subject: Re: CPU utilization vs. IO wait, shared buffers?
Next
From: Tom Lane
Date:
Subject: Re: Index usage problem on 8.3.3