On Tue, 5 Jun 2001, Tom Lane wrote:
> Oleg Bartunov <oleg@sai.msu.su> writes:
> > select msg_prt.tid as mid from msg_prt
> > where exists (select idx.tid from idx where msg_prt.tid=idx.tid
> > and idx.did=1 and idx.lid in (1207,59587) )
> > NOTICE: QUERY PLAN:
>
> > Seq Scan on msg_prt (cost=0.00..119090807.13 rows=69505 width=4)
> > SubPlan
> > -> Index Scan using idxidx, idxidx on idx (cost=0.00..1713.40 rows=1 width=4)
>
> Actually, this example does reveal an unnecessary inefficiency: the
> planner is only using the "idx.lid in (1207,59587)" clause for the
> indexscan, ignoring the fact that the did and tid clauses match the
> additional columns of your three-column index. The attached patch
> should improve matters.
>
> regards, tom lane
Cool. Looks better
select msg_prt.tid as mid from msg_prt where exists (select idx.tid from idx where msg_prt.tid=idx.tid and idx.did=1
andidx.lid in ( 1207, 59587) )
NOTICE: QUERY PLAN:
Seq Scan on msg_prt (cost=0.00..333700.88 rows=69505 width=4) SubPlan -> Index Scan using idxidx, idxidx on idx
(cost=0.00..4.79rows=1 width=4)
total: 3.15 sec; number: 1; for one: 3.153 sec;
interesting that droping index 'idxidx' and creating simple
create index tididx on idx (tid);
behaves better, while plan looks worse. Notice, index on tididx
estimates cost better (16).
select msg_prt.tid as mid from msg_prt where exists (select idx.tid from idx where msg_prt.tid=idx.tid and idx.did=1
andidx.lid in ( 1207, 59587) )
NOTICE: QUERY PLAN:
Seq Scan on msg_prt (cost=0.00..1134474.94 rows=69505 width=4) SubPlan -> Index Scan using tididx on idx
(cost=0.00..16.31rows=1 width=4)
total: 1.70 sec; number: 1; for one: 1.703 sec;
Interesting that earlier if I have 2 indexes - idxidx and tididx
optimizer choose tididx, while now (after patching) optimizer
always choose idxidx.
Regards, Oleg
_____________________________________________________________
Oleg Bartunov, sci.researcher, hostmaster of AstroNet,
Sternberg Astronomical Institute, Moscow University (Russia)
Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/
phone: +007(095)939-16-83, +007(095)939-23-83
