Thread: Index usage problem on 8.3.3
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
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?
regards, tom lane
Tom Lane wrote:
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)
Yep, sure enough, the 'act' table's indexes have it set and jefftest and jefftest2's indexes do not.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?
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
Jeff Frost <jeff@frostconsultingllc.com> writes:
> Tom Lane wrote:
>> 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.
Okay. What that means is that the indexes were created on data that had
already been inserted and updated to some extent, resulting in
HOT-update chains that turned out to be illegal for the new indexes.
The way we deal with this is to mark the indexes as not usable by any
query that can still see the dead HOT-updated tuples.
Your best bet for dodging the problem is probably to break the operation
into two transactions, if that's possible. INSERT and UPDATE in the
first xact, create the indexes at the start of the second. (Hmm ...
I'm not sure if that's sufficient if there are other concurrent
transactions; but it's certainly necessary.) Another possibility is
to create the indexes just after data load, before you start updating
the columns they're on.
regards, tom lane
Tom Lane wrote: > Okay. What that means is that the indexes were created on data that had > already been inserted and updated to some extent, resulting in > HOT-update chains that turned out to be illegal for the new indexes. > The way we deal with this is to mark the indexes as not usable by any > query that can still see the dead HOT-updated tuples. > > Your best bet for dodging the problem is probably to break the operation > into two transactions, if that's possible. INSERT and UPDATE in the > first xact, create the indexes at the start of the second. (Hmm ... > I'm not sure if that's sufficient if there are other concurrent > transactions; but it's certainly necessary.) Another possibility is > to create the indexes just after data load, before you start updating > the columns they're on. > > Thanks Tom! Any idea why I don't see it on 8.3.4? -- Jeff Frost, Owner <jeff@frostconsultingllc.com> Frost Consulting, LLC http://www.frostconsultingllc.com/ Phone: 916-647-6411 FAX: 916-405-4032
Jeff Frost <jeff@frostconsultingllc.com> writes:
> Tom Lane wrote:
>> Okay. What that means is that the indexes were created on data that had
>> already been inserted and updated to some extent, resulting in
>> HOT-update chains that turned out to be illegal for the new indexes.
>> The way we deal with this is to mark the indexes as not usable by any
>> query that can still see the dead HOT-updated tuples.
> Any idea why I don't see it on 8.3.4?
I think it's more likely some small difference in your test conditions
than any real version-to-version difference. In particular I think the
"still see" test might be influenced by the ages of transactions running
concurrently.
regards, tom lane
On Thu, 30 Oct 2008, Tom Lane wrote: >> Any idea why I don't see it on 8.3.4? > > I think it's more likely some small difference in your test conditions > than any real version-to-version difference. In particular I think the > "still see" test might be influenced by the ages of transactions running > concurrently. Interesting. This is on a test server which has no other concurrent transactions and it acts the same way after I stopped 8.3.4 and started up 8.3.3 again as it did before stopping 8.3.3 to bring up 8.3.4. Hrmm..I'm not sure that makes sense. So, I did the test with the sql script on 8.3.3, then shut down 8.3.3, started up 8.3.4 on the same data dir, ran the test successfully. Next I shut down 8.3.4 and started 8.3.3 and verified that the behavior was still the same on 8.3.3. I wonder what else I might be doing differently. The good news is that making the indexes before the updates seems to make the planner happy! -- Jeff Frost, Owner <jeff@frostconsultingllc.com> Frost Consulting, LLC http://www.frostconsultingllc.com/ Phone: 916-647-6411 FAX: 916-405-4032
Jeff Frost <jeff@frostconsultingllc.com> writes:
> On Thu, 30 Oct 2008, Tom Lane wrote:
>>> Any idea why I don't see it on 8.3.4?
>>
>> I think it's more likely some small difference in your test conditions
>> than any real version-to-version difference. In particular I think the
>> "still see" test might be influenced by the ages of transactions running
>> concurrently.
> Interesting. This is on a test server which has no other concurrent
> transactions and it acts the same way after I stopped 8.3.4 and started up
> 8.3.3 again as it did before stopping 8.3.3 to bring up 8.3.4. Hrmm..I'm not
> sure that makes sense. So, I did the test with the sql script on 8.3.3, then
> shut down 8.3.3, started up 8.3.4 on the same data dir, ran the test
> successfully. Next I shut down 8.3.4 and started 8.3.3 and verified that the
> behavior was still the same on 8.3.3. I wonder what else I might be doing
> differently.
Huh. That does sound like it's a version-to-version difference.
There's nothing in the CVS log that seems related though. Are you
willing to post your test case?
regards, tom lane
Tom Lane wrote:
It's a customer DB, so I'll contact them and see if we can boil it down to a test case with no sensitive data.Jeff Frost <jeff@frostconsultingllc.com> writes:On Thu, 30 Oct 2008, Tom Lane wrote:Any idea why I don't see it on 8.3.4?I think it's more likely some small difference in your test conditions than any real version-to-version difference. In particular I think the "still see" test might be influenced by the ages of transactions running concurrently.Interesting. This is on a test server which has no other concurrent transactions and it acts the same way after I stopped 8.3.4 and started up 8.3.3 again as it did before stopping 8.3.3 to bring up 8.3.4. Hrmm..I'm not sure that makes sense. So, I did the test with the sql script on 8.3.3, then shut down 8.3.3, started up 8.3.4 on the same data dir, ran the test successfully. Next I shut down 8.3.4 and started 8.3.3 and verified that the behavior was still the same on 8.3.3. I wonder what else I might be doing differently.Huh. That does sound like it's a version-to-version difference. There's nothing in the CVS log that seems related though. Are you willing to post your test case?
-- Jeff Frost, Owner <jeff@frostconsultingllc.com> Frost Consulting, LLC http://www.frostconsultingllc.com/ Phone: 916-647-6411 FAX: 916-405-4032
Jeff Frost <jeff@frostconsultingllc.com> writes:
> Tom Lane wrote:
>> Huh. That does sound like it's a version-to-version difference.
>> There's nothing in the CVS log that seems related though. Are you
>> willing to post your test case?
>>
> It's a customer DB, so I'll contact them and see if we can boil it down
> to a test case with no sensitive data.
Well, if there was a change it seems to have been in the right direction
;-) so this is mostly just idle curiosity. Don't jump through hoops to
get a test case.
regards, tom lane
Tom Lane <tgl@sss.pgh.pa.us> writes: > Jeff Frost <jeff@frostconsultingllc.com> writes: >> Tom Lane wrote: >>> Huh. That does sound like it's a version-to-version difference. >>> There's nothing in the CVS log that seems related though. Are you >>> willing to post your test case? >>> >> It's a customer DB, so I'll contact them and see if we can boil it down >> to a test case with no sensitive data. > > Well, if there was a change it seems to have been in the right direction > ;-) so this is mostly just idle curiosity. Don't jump through hoops to > get a test case. Assuming it's not a bug... -- Gregory Stark EnterpriseDB http://www.enterprisedb.com Ask me about EnterpriseDB's RemoteDBA services!
On Fri, 31 Oct 2008, Gregory Stark wrote: > Tom Lane <tgl@sss.pgh.pa.us> writes: > >> Jeff Frost <jeff@frostconsultingllc.com> writes: >>> Tom Lane wrote: >>>> Huh. That does sound like it's a version-to-version difference. >>>> There's nothing in the CVS log that seems related though. Are you >>>> willing to post your test case? >>>> >>> It's a customer DB, so I'll contact them and see if we can boil it down >>> to a test case with no sensitive data. >> >> Well, if there was a change it seems to have been in the right direction >> ;-) so this is mostly just idle curiosity. Don't jump through hoops to >> get a test case. > > Assuming it's not a bug... Well, after boiling down my test case to the bare essentials, I was unable to reproduce the different behavior between 8.3.3 and 8.3.4. Now, I've gone back to the original script and can't reproduce the behavior I previously saw on 8.3.4 and my screen session doesn't have enough scrollback to look at what happened previously. I was thinking perhaps I had inadvertently committed the transaction, but then the act would have been dropped as it's a temp table created with ON COMMIT DROP. But, I've tested 3 times in a row and every time 8.3.4 uses the seq scan just like 8.3.3 now, so I must've done something differently to get that result as Tom had originally suggested. I just can't think what it might have been. Perhaps it's time to buy some glasses. :-/ -- Jeff Frost, Owner <jeff@frostconsultingllc.com> Frost Consulting, LLC http://www.frostconsultingllc.com/ Phone: 916-647-6411 FAX: 916-405-4032