Hi,
I have a query with the following joins:
annotations2.attribute = 'pos' AND
annotations2.value = 'KOUS' AND
annotations4.span = 'man' AND
annotations6.span = 'sich' AND
annotations2.text_ref = annotations4.text_ref AND
annotations2.right = annotations4.left - 1 AND
annotations4.text_ref = annotations6.text_ref AND
annotations4.right = annotations6.left - 1
(annotations{2,4,6} are aliases to a table annotations)
And I have the following indexes:
create index idx_anno_span_text_ref_left_minus_1 on annotations
(span, text_ref, ("left" - 1));
create index idx_anno_attribute_value_text_ref_right on annotations
(attribute, value, text_ref, right);
The plan of the query above can be found here: http://explain-analyze.info/query_plans/2053-query-plan-786
My questions are:
- Why, after annotations2 and annotation4 are scanned with the
respective index are the index constraints re-checked? For example
the inner-most scan on annotation2 takes 58 ms to access tuples with
attribute = 'pos' and value = 'KOUS', but then the conditions are re-
checked which takes almost 4 seconds. The same happens on the
annotations4 scan, but not on the annotations6 scan, although the same
index is used and the conditions are the similar.
- Secondly, I included text_ref and right (or left) in the indexes
above to have the tuples that are returned sorted by these columns, so
I can use them in a Merge Join. Why then, are the the tuples
explicitly sorted? (I'm guessing that it's because of the re-check,
because I have seen a Merge Join using an index that returns the
tuples in sorted order before.)
- Finally, after the Merge Join the tuples should still be sorted on
the colums text_ref and right. (Well, maybe not on right, but
definately on text_ref.) Why then, is a Nested Loop join used and not
again a Merge Join as the outer-most join? (I'm guessing, because
postgres estimates that only 1 row is returned.)
Thanks,
Viktor
