Skip site navigation (1) Skip section navigation (2)

Re: Strange query plan

From: Oleg Bartunov <oleg(at)sai(dot)msu(dot)su>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: Pgsql Hackers <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: Strange query plan
Date: 2001-06-05 19:02:57
Message-ID: Pine.GSO.4.33.0106052154120.26250-100000@ra.sai.msu.su (view raw or flat)
Thread:
Lists: pgsql-hackers
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


In response to

pgsql-hackers by date

Next:From: Peter EisentrautDate: 2001-06-05 19:50:38
Subject: Can the backend return more than one error message per PQexec?
Previous:From: Vince VielhaberDate: 2001-06-05 17:26:38
Subject: importing from sybase

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group