8.1.4 planner index selection

sfpug-

When I add a simple predicate based on an indexed column to a query (Q1,
below), the query becomes much slower (Q2). This is despite a
multi-column index that in principle ought to have greatly improved
performance. When I remove the multi-column index, the query plan for
Q2 reverts to using the same index as that for Q1.

This index was beneficial in PG 7.x and I believe that this query ran
well as late as 8.1.2. It is now much slower in 8.1.4. I cannot roll
back to 8.1.2 to verify this.

I understand that the query optimizer makes no guarantees about
optimality and that it's based on statistics and cost assumptions.
However, I doubt that anyone expects performance losses during upgrades
or the counterintuitive observation that /removing/ a trivial predicate
or an index causes the runtime performance to improve.

Does this merit a posting to the pgsql-bugs (or other) list?

Thanks,
Reece

I'm running 8.1.4 on SuSE 10.0 on a 4P Opteron 850 w/32GB.

csb=> \d+ pahmm
Table "unison.pahmm"
Column | Type | // | Description
-------------+------------------+----------//-+----------------------------------------------------------
pfeature_id | integer | not null // | unique feature id
pseq_id | integer | not null // | unique protein sequence identifier -- see pseq(pseq_id)
pftype_id | integer | not null // | protein feature type identifier -- see pftype(pftype_id)
start | integer | not null // | start of prediction in protein sequence
stop | integer | not null // | stop of prediction in protein sequence
params_id | integer | not null // | parameter set identifier -- see params(params_id)
pmodel_id | integer | not null // | unique protein model identifier
mstart | smallint | not null // | start of match /in model/
mstop | smallint | not null // | stop of match /in model/
ident | smallint | // |
sim | smallint | // |
gaps | smallint | not null // |
qgaps | smallint | not null // | number of gaps in query sequence
tgaps | smallint | not null // | number of gaps in target sequence
score | smallint | not null // | algorithm-specific score
eval | double precision | not null // | expectation value
len | smallint | // |
Indexes:
"pahmm_redundant_feature" UNIQUE, btree (pseq_id, "start", stop, pmodel_id, params_id, mstart, mstop) CLUSTER
"pahmm_search1" btree (pmodel_id, eval, params_id)
"pahmm_search2" btree (params_id, eval, pmodel_id)
"pahmm_search3" btree (params_id, score, pmodel_id)
Foreign-key constraints:
"pahmm_params_id_exists" FOREIGN KEY (params_id) REFERENCES params(params_id) ON UPDATE CASCADE ON DELETE CASCADE
"pahmm_pmodel_id_exists" FOREIGN KEY (pmodel_id) REFERENCES pmhmm(pmodel_id) ON UPDATE CASCADE ON DELETE CASCADE
"pfeature_pftype_id_exists" FOREIGN KEY (pftype_id) REFERENCES pftype(pftype_id) ON UPDATE CASCADE ON DELETE CASCADE
"pfeature_pseq_id_exists" FOREIGN KEY (pseq_id) REFERENCES pseq(pseq_id) ON UPDATE CASCADE ON DELETE CASCADE
Inherits: palignment
Has OIDs: no

unison(at)csb=> analyze pahmm;
ANALYZE

rkh(at)csb=> explain analyze select * from pahmm where eval<1e-10 AND pseq_id = 76; -- Q1
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------
Bitmap Heap Scan on pahmm (cost=12.65..4998.18 rows=1 width=54) (actual time=0.127..0.144 rows=2 loops=1)
Recheck Cond: (pseq_id = 76)
Filter: (eval < 1e-10::double precision)
-> Bitmap Index Scan on pahmm_redundant_feature (cost=0.00..12.65 rows=1330 width=0) (actual time=0.094..0.094 rows=10 loops=1)
Index Cond: (pseq_id = 76)
Total runtime: 0.199 ms
(6 rows)

rkh(at)csb=> explain analyze select * from pahmm where eval<1e-10 AND pseq_id = 76 AND params_id=34; -- Q2
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------
Index Scan using pahmm_search2 on pahmm (cost=0.00..1791.59 rows=1 width=54) (actual time=2743.505..7934.694 rows=1 loops=1)
Index Cond: ((params_id = 34) AND (eval < 1e-10::double precision))
Filter: (pseq_id = 76)
Total runtime: 7934.771 ms
(4 rows)

unison(at)csb=> drop index pahmm_search2 ;
DROP INDEX

unison(at)csb=> explain analyze select * from pahmm where eval<1e-10 AND pseq_id = 76 AND params_id=34; -- Q2
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------
Bitmap Heap Scan on pahmm (cost=15.98..2335.66 rows=1 width=54) (actual time=0.057..0.059 rows=1 loops=1)
Recheck Cond: ((pseq_id = 76) AND (params_id = 34))
Filter: (eval < 1e-10::double precision)
-> Bitmap Index Scan on pahmm_redundant_feature (cost=0.00..15.98 rows=604 width=0) (actual time=0.039..0.039 rows=2 loops=1)
Index Cond: ((pseq_id = 76) AND (params_id = 34))
Total runtime: 0.116 ms
(6 rows)

--
Reece Hart, http://harts.net/reece/, GPG:0x25EC91A0