Index on foreign key makes query extremely slow

indexoracleoracle-11g-r2select

We are recently experiencing a tremendous query slowdown with spilled over temp tablespace. A specific query causes this problem.

The queried table (table3) has an indexed PK, three FK with indexes and a compound unique constraint on the three FKs. The offensive query looks like this:

SELECT ...
  FROM table1 t1, table2 t2, table3 t3
  WHERE t1.abs_id = ?
    AND t3.vgs_id = t1.vgs_id
    AND t3.ai_id > ?
    AND t2.id = t1.t2_id
    AND t2.status = 2
    AND t2.felddimension = 0
    ...

Only instance restart solved the issue. Even killing connections did no help.

After futher investigation on the FKs and the indexes, it turned out that the index on the t3.ai_id column causes the severe drop in performance. After disabling this one the unique constaint served the query extremely fast.

The problematic part is AND t3.ai_id > ? (range scan). Unique scan does not cause any trouble.

Now the question is, how can an index cause such a slowdown and moreover, how can I investigate the cause? It simply doesn't add up for me.

Competitive times: normal 10 s, slowdown > 2 min or never returning.

EDIT 1 (2013-06-05): Upon Jack Douglas' and Chris Saxon's advises, I have ran stats and then performed explain plan, I have made a giant leap forward.

I have calcucated schema stats with and without the index. Regardless whether the index is available or not, the optimizer uses the 3-field-composite-unique-index making the query extremely fast.

Here's the explain plan from SQL Developer:

Fast query

So far so good, now I have added a hint into the query to use the bad index, and the explain plan is:

Slow query

Now, this is extremely slow. But why?

Best Answer

The CBO will consider lots of different ways to execute your query, taking into account all the indexes and statistics on both tables and indexes.

If you disable an index, obviously the CBO then cuts out all the plans it might have considered that involved that index.

The question really boils down to: "why is the CBO choosing a poor plan", and the first thing to consider is whether your stats are accurately reflecting reality in your database. Is this the case? How do you gather stats, and how often on these tables and indexes?

If and only if the stats are good and the CBO is still choosing a bad plan, you'll need to start considering "why?", then you'll want to have a look at the plan, especially the estimated cardinalities for each step, and see which are badly off. In some cases this is because of correlations that standard stats cannot help with, but cross that bridge when you get to it :)

Related Question