Re: AW: VERY strange query plan (LONG)

On Thu, 10 Aug 2000, Zeugswetter Andreas SB wrote:

> Date: Thu, 10 Aug 2000 10:14:42 +0200
> From: Zeugswetter Andreas SB <ZeugswetterA(at)wien(dot)spardat(dot)at>
> To: 'Oleg Bartunov' <oleg(at)sai(dot)msu(dot)su>
> Cc: "'pgsql-hackers(at)postgresql(dot)org'" <pgsql-hackers(at)postgresql(dot)org>
> Subject: AW: [HACKERS] VERY strange query plan (LONG)
>
>
>
> > very strange numbers and no indices used) (I did run vacuume analyze)
> >
> > explain
> > select
> > txt.tid
> > from
> > txt, txt_lexem1 tl1_0, txt_lexem11 tl11_0
> > where
> > tl1_0.lid =17700
> > OR
> > tl11_0.lid =172751
> > ;
> > NOTICE: QUERY PLAN:
>
> Did you forget to join the tids together, and the did=0 restrictions ?
>
> Your statement looks very strange (cartesian product), and has nothing in
> common with the subselect statements you quoted.

You're right, I simplified original query just to show plans.
Here is original query:
explain
select
txt.tid,
tl1_0.count, tl1_0.pos[1] as pos
from
txt, txt_lexem1 tl1_0, txt_lexem11 tl11_0
where
(
( tl1_0.lid in (17700) and tl1_0.did=0 and txt.tid=tl1_0.tid )
OR
( tl11_0.lid in (172751) and tl11_0.did=0 and txt.tid=tl11_0.tid ))

order by count desc, pos asc;

and plan:

NOTICE: QUERY PLAN:

Sort (cost=1278139131.36..1278139131.36 rows=1 width=44)
-> Nested Loop (cost=0.00..1278139131.35 rows=1 width=44)
-> Nested Loop (cost=0.00..1277916858.52 rows=4041 width=40)
-> Seq Scan on txt_lexem11 tl11_0 (cost=0.00..2596.92 rows=132292 width=12)
-> Seq Scan on txt_lexem1 tl1_0 (cost=0.00..3815.95 rows=194795 width=28)
-> Seq Scan on txt (cost=0.00..20.00 rows=1000 width=4)

EXPLAIN

Interesthing that plan for AND looks realistic (and uses indices):
explain
select
txt.tid,
tl1_0.count, tl1_0.pos[1] as pos
from
txt, txt_lexem1 tl1_0, txt_lexem11 tl11_0
where
(
( tl1_0.lid in (17700) and tl1_0.did=0 and txt.tid=tl1_0.tid )
AND
( tl11_0.lid in (172751) and tl11_0.did=0 and txt.tid=tl11_0.tid ))

order by count desc, pos asc;
NOTICE: QUERY PLAN:

Sort (cost=109.05..109.05 rows=1 width=28)
-> Nested Loop (cost=0.00..109.04 rows=1 width=28)
-> Nested Loop (cost=0.00..87.69 rows=3 width=24)
-> Index Scan using txt_lexem11_key on txt_lexem11 tl11_0 (cost=0.00..35.23 rows=13 width=4)
-> Index Scan using txt_lexem1_key on txt_lexem1 tl1_0 (cost=0.00..3.95 rows=1 width=20)
-> Index Scan using txt_pkey on txt (cost=0.00..8.14 rows=10 width=4)

EXPLAIN

We could live with fulltext search using only AND but very strange
plan for OR worry me.

Regards,

Oleg

>
> Andreas
>

_____________________________________________________________
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