Thread: advanced index (descending and table-presorted descending)
Can pgSQL 8.x do descending indexes like mySQL 5.1?
(so 1st column is descending and rest are asscending?)
Can pgSQL 8.x do "physically" sorted table (like a forced index order) so we don't need order by?
tia,
.V
(so 1st column is descending and rest are asscending?)
Can pgSQL 8.x do "physically" sorted table (like a forced index order) so we don't need order by?
tia,
.V
On 21/11/06, Vic Cekvenich <v.cekvenich@yahoo.com> wrote: > > Can pgSQL 8.x do descending indexes like mySQL 5.1? > (so 1st column is descending and rest are asscending?) > > > Can pgSQL 8.x do "physically" sorted table (like a forced index order) so we > don't need order by? > > tia, > .V > > No and I don't really believe mySQL can if it can its a bug, I would not trust it. If you want your results sorted you need the order by...... However, Cluster might work for you, but you need to re-cluster after every updates or inserts, so it will probably be fine for static data. Which I suspect is the same for mySql but I can't be bothered to check, If mysql really works like this its worse that I originally thought it was. Even so I would always include the order by clause for safety. (Its a bug because the table(file) will always grow and grow and grow and eventually take up the entire disk with tonnes of gaps which can be closed without unsorting the data or rewriting the entire file ie cluster....) Peter.
> However, Cluster might work for you, but you need to re-cluster after > every updates or inserts, so it will probably be fine for static data. This reminds me of a (somewhat off-topic) question I have had: I have a static database, and most of the tables are 100% correlated with one column or another (because I build them that way, or due to clustering). In some cases I join two tables on one of these perfectly correlated columns, and so the planner wants to sort the two on that column. Of course, this is unnecessary, and for large tables, the sorts get spilled to disk (I suppose) and can take a while. Is there any way to convince the planner that the sorts are unnecessary, and it can just zip the two tables together as is? This is under PG 7.4, by the way. Any comments welcome. - John D. Burger MITRE
On Nov 22, 2006, at 1:51 PM, John D. Burger wrote: >> However, Cluster might work for you, but you need to re-cluster after >> every updates or inserts, so it will probably be fine for static >> data. > > This reminds me of a (somewhat off-topic) question I have had: > > I have a static database, and most of the tables are 100% > correlated with one column or another (because I build them that > way, or due to clustering). In some cases I join two tables on one > of these perfectly correlated columns, and so the planner wants to > sort the two on that column. Of course, this is unnecessary, and > for large tables, the sorts get spilled to disk (I suppose) and can > take a while. Is there any way to convince the planner that the > sorts are unnecessary, and it can just zip the two tables together > as is? > > This is under PG 7.4, by the way. Any comments welcome. The problem is that there's no way to actually guarantee that the table is already pre-sorted. If we had the concept of read-only tables, and you clustered one of them, it could be made to work... -- Jim Nasby jim.nasby@enterprisedb.com EnterpriseDB http://enterprisedb.com 512.569.9461 (cell)