Re: 8.1.4 planner index selection

Reece, I can verify on 8.1.2 or just about any other version if you can share
the data (or just give me some fake data) if you like. I certainly would not
expect the single index scan you see in the second query below to take longer
than the bitmap heap scan in the first and third queries. It seems as though
the planner actually made the wrong choice because the estimates are much
lower than the actuals (note the ratio of estimates to actuals in all 3
queries).

On Mon, 19 Jun 2006, Reece Hart wrote:

> 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)
>
>
>

--
Jeff Frost, Owner <jeff(at)frostconsultingllc(dot)com>
Frost Consulting, LLC http://www.frostconsultingllc.com/
Phone: 650-780-7908 FAX: 650-649-1954