From: Jeff Frost <jeff@frostconsultingllc.com>
To: pgsql-performance@postgresql.org
Subject: Index usage problem on 8.3.3
Date: Thu, 30 Oct 2008 17:18:06 -0700
Message-ID: <490A4EBE.9030804@frostconsultingllc.com> (raw)
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
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
Subject: Re: Index usage problem on 8.3.3
In-Reply-To: <490A4EBE.9030804@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