Re: full join in view

>
> If you need only not null-kdt_id and dor_id, then just change
> your joins
> into inner joins.
>

The whole idea is to show expected expenses (in "koostude_detailid") even if they were actually not spent (the same material is not listed in "dokumentide_read"). And also show actual expenses (in "dokumentide_read"), even if we did not expect them (the same material is not listed in "koostude_detailid"). Uh, my english is bit rough, but hope you get the idea.

Anyway, that's why full join seemed exactly the right thing. As I understand now, the reason why my original query does not use indexes, is because of sub-query, not full join. And I think I understood the problem of exposing the right field in sub-query, but my query doesn't seem to have the same problem.

> If you are sure, that you will get only not-null results, you
> don't need
> to include koostud and marerjalid.
>

The problem is, that both kdt_kst_id and dor_kst_id can be null, but they never are at the same time. It's not correct to expose either of them as kst_id. That's why my original query used coalesce to get kst_id, which is always not null. But using coalesce field for filtering of course disabled indexes. Including koostud table in query was good idea, because now I have kst_id, which is always not null.

I was not able to eliminate "materjalid" from my query, because that would have forced me to use full join between "koostude_detailid" and "dokumentide_read" again. Which is not automatically bad thing, but this forces me to write query from "dokumentide_read" as sub-query (whether row in "dokumentide_read" is active or not depends if corresponding row in "dokumendid" is approved or not (kinnitaja is not null)). And this sub-query does not use indexes. And cross join is bad.

I think I have to experiment bit more. Does anyone know a good tool (preferably free) to generate test data? I've got into habit disabling seqscan to see what indexes get used. More data would give more adequate execution plans.

Tambet