[
https://issues.apache.org/jira/browse/CONNECTORS-1090?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=14192032#comment-14192032
]
Karl Wright commented on CONNECTORS-1090:
-----------------------------------------
More from the 9.3 manual:
{code}
By default, B-tree indexes store their entries in ascending order with nulls
last. This means that a forward scan of an index on column x produces output
satisfying ORDER BY x (or more verbosely, ORDER BY x ASC NULLS LAST). The index
can also be scanned backward, producing output satisfying ORDER BY x DESC (or
more verbosely, ORDER BY x DESC NULLS FIRST, since NULLS FIRST is the default
for ORDER BY DESC).
You can adjust the ordering of a B-tree index by including the options ASC,
DESC, NULLS FIRST, and/or NULLS LAST when creating the index; for example:
CREATE INDEX test2_info_nulls_low ON test2 (info NULLS FIRST);
CREATE INDEX test3_desc_index ON test3 (id DESC NULLS LAST);
An index stored in ascending order with nulls first can satisfy either ORDER BY
x ASC NULLS FIRST or ORDER BY x DESC NULLS LAST depending on which direction it
is scanned in.
You might wonder why bother providing all four options, when two options
together with the possibility of backward scan would cover all the variants of
ORDER BY. In single-column indexes the options are indeed redundant, but in
multicolumn indexes they can be useful. Consider a two-column index on (x, y):
this can satisfy ORDER BY x, y if we scan forward, or ORDER BY x DESC, y DESC
if we scan backward. But it might be that the application frequently needs to
use ORDER BY x ASC, y DESC. There is no way to get that ordering from a plain
index, but it is possible if the index is defined as (x ASC, y DESC) or (x
DESC, y ASC).
{code}
> More PostgreSQL performance improvements
> ----------------------------------------
>
> Key: CONNECTORS-1090
> URL: https://issues.apache.org/jira/browse/CONNECTORS-1090
> Project: ManifoldCF
> Issue Type: Improvement
> Components: Framework core
> Affects Versions: Manifold 1.7.1
> Reporter: Karl Wright
> Assignee: Karl Wright
> Fix For: ManifoldCF 1.8, ManifoldCF 2.0
>
> Attachments: CONNECTORS-1090.patch
>
>
> More performance improvements may be possible.
--
This message was sent by Atlassian JIRA
(v6.3.4#6332)
