From: Jeff Frost <jeff@frostconsultingllc.com>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: pgsql-performance@postgresql.org
Subject: Re: Index usage problem on 8.3.3
Date: Thu, 30 Oct 2008 17:52:17 -0700
Message-ID: <490A56C1.8020804@frostconsultingllc.com> (raw)
In-Reply-To: <13272.1225413116@sss.pgh.pa.us>
References: <490A4EBE.9030804@frostconsultingllc.com>
<13272.1225413116@sss.pgh.pa.us>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: jeff@frostconsultingllc.com, tgl@sss.pgh.pa.us
Subject: Re: Index usage problem on 8.3.3
In-Reply-To: <490A56C1.8020804@frostconsultingllc.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox