AW: AW: VERY strange query plan (LONG) - Mailing list pgsql-hackers

From Zeugswetter Andreas SB
Subject AW: AW: VERY strange query plan (LONG)
Date
Msg-id 11C1E6749A55D411A9670001FA687963368047@sdexcsrv1.f000.d0188.sd.spardat.at
Whole thread Raw
List pgsql-hackers
> > 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;

That still does not lead to the same result as your subselect.
Looks like the subselect is really what you want in the first place.
The problem with above is that for the two or'ed clauses there
is no restriction for the respective 3rd table, thus still producing
a cartesian product (the and'ed clauses wont produce that,
thus correct plan for and).

A little better, but still not same result would be:
where
txt.tid=tl1_0.tid and txt.tid=tl11_0.tid and
(( tl1_0.lid in (17700) and  tl1_0.did=0)
OR
( tl11_0.lid in (172751) and tl11_0.did=0))

Andreas


pgsql-hackers by date:

Previous
From: Oleg Bartunov
Date:
Subject: Re: AW: VERY strange query plan (LONG)
Next
From: Zeugswetter Andreas SB
Date:
Subject: AW: VERY strange query plan (LONG)