Hi Svein,

> >>> AND (NOT KAN.SUGU IS NULL)
> >>> This quey took 6 minutes(plan had 2xnatural). Without "where"
> >>> clause it ran ~1 sec(1xnatural). I removed all clauses and

started

> >>> to put them back one by one. Would you beleive, that it was the
> >>> "NOT" inside the last clause. Without NOT it used index(1 sec),
> >>> with NOT it didn't(6 min). As changing of the order had no
> >>> impact(as always) to quey plan, I was out of ideas.
> >>>
> >> Hmm, interesting. Why would a NOT have this effect? I'd try a
> >> couple of changes to see if they made any impact:
> >
> > NOT NULL doesn't use a index, that's correct.
>
> Yes, that bit sounds correct, but from what I read I thought he

said

> that AND (NOT KAN.SUGU IS NULL) actually removed an index from the
> plan

it was only the word "NOT" .. well, now, when you ask, I'm not a
100% sure any more

>(that presumably was used due to SUGU being part of the WHERE or
> JOIN clause of a view definition).

...
FROM YLD_TABEL_VIEW YTW
JOIN OSALEJA OS ON OS.ONNETUS_ID = YTW.LO_ID
JOIN KANNATANU KAN ON KAN.OSALEJA_ID = OS.ID
JOIN YLD_TABEL YLD ON YLD.LO_ID = KAN.ONNETUS_ID
WHERE ...

All JOIN-s use primary keys and unique indexes on columns, that are
not available to users - so I thought it must be safe. But it
appears, that even an the unique index can have a bad statistics.
Users also dont have access to columns that are used in join clause
of the view definition.

I forgot from my first message, that it was a SELECT DISTINCT query.
But it shouldn't matter.

regards,
Harri