To expand on what Shaun said:
> But your fundamental problem is that you're joining two
> giant tables with no clause to limit the result set. If you were only
> getting back 10,000 rows, or even a million rows, your query could execute
> in a fraction of the time. But joining every row in both tables and
> returning a 30-million row result set isn't going to be fun for anyone.
Indexes aren't a magical performance fairy dust. An index gives you a
way to look up a single row directly (you can't do that with a scan),
but it's a terrible way to look up 90% (or even 50%) of the rows in a
table, because the per-row cost of lookup is actually higher than in a
scan. That is, once you need to look up more than a certain percentage
of rows in a table, it's actually cheaper to scan it and ignore what
you don't care about rather than going through the index for each row.
It looks like your query is hitting this situation.
Try turning off the merge join, as Tomas suggested, to validate the
assumption that using the index would actually be worse.
To resolve your problem, you shouldn't be trying to make the planner
pick a better plan, you should optimize your settings to get this plan
to perform better or (ideally) optimize your application so you don't
need such an expensive query (because the fundamental problem is that
this query is inherently expensive).
---
Maciek Sakrejda | System Architect | Truviso
1065 E. Hillsdale Blvd., Suite 215
Foster City, CA 94404
(650) 242-3500 Main
www.truviso.com
