

> 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.

Andreas

Thu, 10 Aug 2000 10:14:42 +0200
Zeugswetter Andreas SB <ZeugswetterA@wien.spardat.at>
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.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@sai.msu.su, http://www.sai.msu.su/~megera/
phone: +007(095)939-16-83, +007(095)939-23-83


Thu, 10 Aug 2000 11:41:13 +0300 (GMT)
Oleg Bartunov <oleg@sai.msu.su>
Re: AW: VERY strange query plan (LONG)