3 ms·
You can optionally cluster a table based on index order with PostgreSQL, simply create the index and then `ALTER TABLE foo CLUSTER ON bar_ix`. Clustered indexes
by snuxoll 10y ago
You can optionally cluster a table based on index order with PostgreSQL, simply create the index and then `ALTER TABLE foo CLUSTER ON bar_ix`. Clustered indexes are actually an issue I run into with SQL server, because, like MySQL, the table data is always indirectly referenced by the clustered index, so any query that uses a secondary index requires a second pointer deref to find the data in the table heap. This has a benefit to insert/update performance, but I'd argue that more tables are higher on reads than mutations.
- dx034 10y agoYou can use INCLUDE to put some columns in the index. Doesn't always help but speeds up many queries. AFAIK Postgres doesn't have that feature, not sure about MySQL.
- snuxoll 10y agoUsing INCLUDE comes at the cost of bloating your indexes making scans take a considerable longer time, not having to chase that extra pointer works well enough in most cases (and if you are able to benefit from Heap-Only Tuples you can even have in-place UPDATES without needing to rewrite the pointer in every index).
- dhd415 10y agoSQL Server supports heaps (tables w/o clustered indexes) on which indexes point directly to the referenced row. It's nice that they allow you to choose between those and tables with clustered indexes where any secondary index references the primary key. See https://msdn.microsoft.com/en-us/library/hh213609.aspx https://msdn.microsoft.com/en-us/library/hh213609.aspx