>
> 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
materialis not listed in "dokumentide_read"). And also show actual expenses (in "dokumentide_read"), even if we did not
expectthem (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
notuse indexes, is because of sub-query, not full join. And I think I understood the problem of exposing the right
fieldin 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
toexpose either of them as kst_id. That's why my original query used coalesce to get kst_id, which is always not null.
Butusing coalesce field for filtering of course disabled indexes. Including koostud table in query was good idea,
becausenow 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
queryfrom "dokumentide_read" as sub-query (whether row in "dokumentide_read" is active or not depends if corresponding
rowin "dokumendid" is approved or not (kinnitaja is not null)). And this sub-query does not use indexes. And cross join
isbad.
I think I have to experiment bit more. Does anyone know a good tool (preferably free) to generate test data? I've got
intohabit disabling seqscan to see what indexes get used. More data would give more adequate execution plans.
Tambet
