Received: from localhost (unknown [200.46.204.183]) by postgresql.org (Postfix) with ESMTP id 079E6650146 for ; Thu, 30 Oct 2008 21:53:29 -0300 (ADT) Received: from postgresql.org ([200.46.204.86]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 59320-04 for ; Thu, 30 Oct 2008 21:53:26 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from smtp.01.com (lb-2.01.com [38.98.186.147]) by postgresql.org (Postfix) with ESMTP id 4C17B64FD24 for ; Thu, 30 Oct 2008 21:53:26 -0300 (ADT) Received: from asav-1.01.com (localhost.localdomain [127.0.0.1]) by asav-1.01.com (8.13.1/8.13.1) with SMTP id m9V0rIaO022737; Thu, 30 Oct 2008 19:53:18 -0500 Received: from smtp-2.01.com (smtp-2.01.com [38.98.186.158]) by asav-1.01.com (asav-1.01.com [10.25.1.11]) id k9YJrI0518637544iv ret-id none; Thu, 30 Oct 2008 19:53:18 -0500 Received: from localhost (localhost.localdomain [127.0.0.1]) by smtp-2.01.com (Postfix) with ESMTP id 2D3901AD3BD; Thu, 30 Oct 2008 19:53:18 -0500 (CDT) Received: from smtp-2.01.com ([127.0.0.1]) by localhost (smtp-2.01.com [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id 9jphz5QXZKHd; Thu, 30 Oct 2008 19:53:18 -0500 (CDT) Received: from [10.10.10.18] (8.202-62-69.ftth.swbr.surewest.net [69.62.202.8]) by smtp-2.01.com (Postfix) with ESMTP id C9B891AD3B0; Thu, 30 Oct 2008 19:53:17 -0500 (CDT) Message-ID: <490A56C1.8020804@frostconsultingllc.com> Date: Thu, 30 Oct 2008 17:52:17 -0700 From: Jeff Frost Organization: Frost Consulting, LLC User-Agent: Thunderbird 2.0.0.17 (Windows/20080914) MIME-Version: 1.0 To: Tom Lane CC: pgsql-performance@postgresql.org Subject: Re: Index usage problem on 8.3.3 References: <490A4EBE.9030804@frostconsultingllc.com> <13272.1225413116@sss.pgh.pa.us> In-Reply-To: <13272.1225413116@sss.pgh.pa.us> Content-Type: multipart/alternative; boundary="------------030401090607060005020601" X-FSL-MailScanner-Information: Please contact postmaster@01.com for more information X-FSL-MailScanner: Found to be clean X-FSL-MailScanner-SpamCheck: X-FSL-MailScanner-From: jeff@frostconsultingllc.com X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.001 tagged_above=0 required=5 tests=HTML_MESSAGE=0.001 X-Spam-Level: X-Archive-Number: 200810/138 X-Sequence-Number: 31719 This is a multi-part message in MIME format. --------------030401090607060005020601 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Tom Lane wrote: > Jeff Frost 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 Frost Consulting, LLC http://www.frostconsultingllc.com/ Phone: 916-647-6411 FAX: 916-405-4032 --------------030401090607060005020601 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Tom Lane wrote: 
Jeff Frost <jeff@frostconsultingllc.com> 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@frostconsultingllc.com>
Frost Consulting, LLC 	http://www.frostconsultingllc.com/
Phone: 916-647-6411	FAX: 916-405-4032
--------------030401090607060005020601--