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