So sorry, I send you only the half of information:
MS-SQL uses the the plan with only nested loops when i have abt less
than 100 tuples in both the tables (="small")
when t1 gets abt > 1000 tuples (="medium") it switches to the
hash-anti-semi-join, when both tables gets more than 1000 tuples
(="big") it too adds parallelism. (on my 2cpu machine).
from the file i attached in the last mail in the big-plan-xml-file I
extraxced this plan by cutting of what i thought is too much detail:
It is the third one Gregory had get from the image (that had a cutoff
on the top because of my resolution)
If you look inside the xml (plan_bigdata.sqlplan) you can find
interesting details i think.
for me it's clear that the query is not nice. I used it to provoke the
optimizer only for studiing the possibilities of what can be optimized
from my POV it's not clear why PostgreSQL runs into the triple-table
nested-loop which will lead to a cardinality of abt 8*10^9 where it
could make the t1,t2-nested loop with cardinality 5*10^6 and then a
merge-anti-semi-join on t1 (#1300) which should be able to do in about
log(5*10^6)*5*10^6. so there is a gap of nearly factor 1000.
for me it looks like it can not rotate the calculation of the
expression (2*(a1+a2)) outside of the "not exists"?
best regards, alexander.
PS: I will now let the query run to an end if it takes less than 10 hours
2007/12/19, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>:
> Gregory Stark <stark(at)enterprisedb(dot)com> writes:
> > "Tom Lane" <tgl(at)sss(dot)pgh(dot)pa(dot)us> writes:
> >> It's possible that MS-SQL is doing something analogous to the
> >> hashed-subplan approach (hopefully with suitable tweaking for the NULL
> >> case) but even then it's hard to see how it could take only 9 sec.
> >> The cartesian product is too big.
> > Fwiw it seems MS-SQL is doing something funny. The three plans posted in the
> > screenshots for the "small", "mediu", and "large" cases are:
> > ...
> > Postgres is doing something equivalent to the first plan.
> Hmm. I think the second plan is probably equivalent to the
> hashed-subplan behavior that you can get in PG by rewriting the query to
> NOT IN as I illustrated. The third plan looks to be the same thing plus
> some parallelization frammishes.
> I'm not clear on what "small/medium/large" means, in particular not on
> which of these corresponds to the OP's report of 9-second execution.
> regards, tom lane
+49 228 2661615
In response to
pgsql-bugs by date
|Next:||From: Dave Page||Date: 2007-12-19 10:03:41|
|Subject: Re: [BUGS] BUG #3829: Wrong index reporting from pgAdmin III (v1.8.0
|Previous:||From: Pavel Stehule||Date: 2007-12-19 09:29:33|
|Subject: Re: [BUGS] BUG #3829: Wrong index reporting from pgAdmin III (v1.8.0 rev 6766-6767)|