Re: Strange query plan

On Tue, 5 Jun 2001, Tom Lane wrote:

> Oleg Bartunov <oleg(at)sai(dot)msu(dot)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 and idx.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.79 rows=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 and idx.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.31 rows=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(at)sai(dot)msu(dot)su, http://www.sai.msu.su/~megera/
phone: +007(095)939-16-83, +007(095)939-23-83