4 ms·
I don't see how pgsql could perform such optimizations as eliminating a sorting step when ordering results by column(s) in a clustered index if there is no guar
by dhd415 9y ago
I don't see how pgsql could perform such optimizations as eliminating a sorting step when ordering results by column(s) in a clustered index if there is no guarantee that new or updated data will also be ordered by the clustered index. I agree that both models have their pros and cons. While I am a big fan of pgsql, I do also appreciate those databases that offer both heaps and clustered indexes that I can mix and match in my schema as best suits my workload.
- jeltz 9y agoPostgreSQL can use that optimization since an index scan in PostgreSQL still returns all tuples from the heap in index order, not in heap order. During an index scan PostgreSQL traverses the B-tree in index order and for each matching tuple found in the index it fetches the data from the heap (which is a quite cheap O(1) operation).