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@wien.spardat.at>
> To: 'Oleg Bartunov' <oleg@sai.msu.su>
> Cc: "'pgsql-hackers@postgresql.org'" <pgsql-hackers@postgresql.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.92rows=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.69rows=3 width=24)             ->  Index Scan using txt_lexem11_key on txt_lexem11 tl11_0
(cost=0.00..35.23rows=13 width=4)             ->  Index Scan using txt_lexem1_key on txt_lexem1 tl1_0  (cost=0.00..3.95
rows=1width=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@sai.msu.su, http://www.sai.msu.su/~megera/
phone: +007(095)939-16-83, +007(095)939-23-83