Re: Strange query plan

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