Received: from sss2.sss.pgh.pa.us (sss.pgh.pa.us [209.114.166.2]) by hub.org (8.9.3/8.9.3) with ESMTP id KAA00159 for ; Fri, 14 Jan 2000 10:18:24 -0500 (EST) (envelope-from tgl@sss.pgh.pa.us) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss2.sss.pgh.pa.us (8.9.3/8.9.3) with ESMTP id KAA29408; Fri, 14 Jan 2000 10:18:17 -0500 (EST) To: Dirk Lutzebaeck cc: pgsql-sql@postgreSQL.org Subject: Re: [SQL] Optimizing ORDER BY with indexes? In-reply-to: <14462.60668.225811.469623@ampato.aeccom.com> References: <14462.57679.783969.863812@ampato.aeccom.com> <14462.60668.225811.469623@ampato.aeccom.com> Comments: In-reply-to Dirk Lutzebaeck message dated "Fri, 14 Jan 2000 10:31:40 +0100" Date: Fri, 14 Jan 2000 10:18:16 -0500 Message-ID: <29405.947863096@sss.pgh.pa.us> From: Tom Lane Dirk Lutzebaeck writes: > Reading about indexes in the Bowman SQL book I think a > _clustered index_ is needed for the problem above. > Does Postgresql support these? It's there, but (1) the clustering operation itself isn't really production-grade code (it tends to drop auxiliary data about the table), and (2) the optimizer doesn't make any allowance in its estimates for recently clustered indexes, so it might fail to choose an indexscan when that would actually be the best way to do things. I've been thinking about fixing (2), but the problem is to know how long ago the cluster operation was done --- updates to the table will gradually destroy the cluster order, so you really want to somehow scale the cost estimate back towards the unordered case as more and more updates are done following a cluster. But that'd mean keeping an update counter, which is pretty expensive in itself. regards, tom lane