Sorting a query on a view ignores an index - Mailing list pgsql-performance

From Mathieu De Zutter
Subject Sorting a query on a view ignores an index
Date
Msg-id 20030821133538.GA973@pleyel.kotnet.org
Whole thread Raw
Responses Re: Sorting a query on a view ignores an index
List pgsql-performance
Hi,

I'm having a performance problem in postgresql.

I have a rather complex view (attached) which, on itself, executes very
fast, as it should. Normally this view is unordered. When I order the
view itself (see comments in attachment), the view executes with about
the same speed since the field i'm sorting on has an index.
However, i dont want the view to be presorted, but sort it in the
queries that use the view. When I do that, the index I have on that
field seems to be ignored. It stretches so far that, when I sort the
view on A and sort the query on A too, the query will try to sort
_again_ _without_ index and thus lose all performance.

I've attached the query-plans for the different cases.
preordered:           means the view itself is sorted
with/without sorting: tells whether the query is sorted

Note: the "NOT NULL" in the queries doesn't affect performance

With kind regards,

    Mathieu

pgsql-performance by date:

Previous
From: "Tam MK"
Date:
Subject: More connections in Solaris9
Next
From: Mathieu De Zutter
Date:
Subject: A better look on the same problem (ignored index)