Questions about index usage
От | Viktor Rosenfeld |
---|---|
Тема | Questions about index usage |
Дата | |
Msg-id | 5A9F5260-5088-4F1A-990A-55FDE4D2DB34@informatik.hu-berlin.de обсуждение исходный текст |
Список | pgsql-general |
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
В списке pgsql-general по дате отправления: