Skip site navigation (1) Skip section navigation (2)

Index usage problem on 8.3.3

From: Jeff Frost <jeff(at)frostconsultingllc(dot)com>
To: pgsql-performance(at)postgresql(dot)org
Subject: Index usage problem on 8.3.3
Date: 2008-10-31 00:18:06
Message-ID: 490A4EBE.9030804@frostconsultingllc.com (view raw or flat)
Thread:
Lists: 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(at)frostconsultingllc(dot)com>
Frost Consulting, LLC 	http://www.frostconsultingllc.com/
Phone: 916-647-6411	FAX: 916-405-4032


Responses

pgsql-performance by date

Next:From: Tom LaneDate: 2008-10-31 00:31:56
Subject: Re: Index usage problem on 8.3.3
Previous:From: Oliver JohnsonDate: 2008-10-30 23:38:14
Subject: Re: CPU utilization vs. IO wait, shared buffers?

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group