Kevin Grittner wrote:
I rewrote the query to use IN predicates rather than EXISTS predicates,
and the cost estimates look like this:
EXISTS, no index: 1.6 billion
EXISTS, with index: 0.023 billion
IN, no index: 13.7 billion
IN, with index: 10.6 billion
At least for the two EXISTS cases, the estimates were roughly accurate.
These plans were run against the data after the fix, but analyze has
not been run since then, so the estimates should be comparable with the
earlier post.
I'm not used to using the IN construct this way, so maybe someone can
spot something horribly stupid in how I tried to use it.
I will have a look at your queries tomorrow. Some general advice (rdbms
agnostic) on when to use IN and when to use EXISTS taken from "SQL
performance tuning":
- if the inner table has few rows and the outer has many then IN is
preferred
- if however you have a restrictive expression on the outer query you
should preferr EXISTS
- use NOT EXISTS instead of NOT IN (break out early)
regards,
Lukas
---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings
