Re: Index usage problem on 8.3.3

[Jeff Frost <jeff@xxxxxxxxxxxxxxxxxxxxxx>]

Tom Lane wrote:

Jeff Frost <jeff@xxxxxxxxxxxxxxxxxxxxxx> writes:
  

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.
    

This may be a HOT side-effect ... is pg_index.indcheckxmin set for
the index?
  

Yep, sure enough, the 'act' table's indexes have it set and jefftest and jefftest2's indexes do not.

select c.relname,i.indcheckxmin  from pg_class c, pg_index i WHERE i.indexrelid = c.oid AND c.relname IN ('act_act_usr_id', 'act_arrived', 'act_closing', 'act_place');
    relname     | indcheckxmin
----------------+--------------
 act_closing    | t
 act_act_usr_id | t
 act_place      | t
 act_arrived    | t
(4 rows)


consdb=# select c.relname,i.indcheckxmin  from pg_class c, pg_index i WHERE i.indexrelid = c.oid AND c.relname IN ('jefftest2_jefftest_usr_id', 'jefftest2_arrived', 'jefftest2_closing', 'jefftest2_place');
          relname          | indcheckxmin
---------------------------+--------------
 jefftest2_jefftest_usr_id | f
 jefftest2_place           | f
 jefftest2_arrived         | f
 jefftest2_closing         | f
(4 rows)

-- 
Jeff Frost, Owner 	<jeff@xxxxxxxxxxxxxxxxxxxxxx>
Frost Consulting, LLC 	http://www.frostconsultingllc.com/
Phone: 916-647-6411	FAX: 916-405-4032