From: | Mathieu De Zutter <mathieu(at)dezutter(dot)org> |
---|---|
To: | pgsql-performance(at)postgresql(dot)org |
Subject: | Sorting a query on a view ignores an index |
Date: | 2003-08-21 13:35:38 |
Message-ID: | 20030821133538.GA973@pleyel.kotnet.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | 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
From | Date | Subject | |
---|---|---|---|
Next Message | Mathieu De Zutter | 2003-08-21 14:06:40 | A better look on the same problem (ignored index) |
Previous Message | Tam MK | 2003-08-21 11:56:57 | More connections in Solaris9 |