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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Reply via email to