5 ms·

I develop for MSSQL extensively and PG is missing some things that can be a little surprising. He might have been referring to the fact that PG doesn't cache q

I develop for MSSQL extensively and PG is missing some things that can be a little surprising. He might have been referring to the fact that PG doesn't cache query plans or have way to lock them in. PG replans for every statement unless you manually do prepared statements and that only works per connection. MSSQL will cache plans and reuse them and has done this for a very long time. Consequently the planner can take more time planning. Also MSSQL has hints and you can even lock a plan. PG really needs hints, optimizers are great and all but sometimes I know better and I want to make sure it listens to me. Also PG has no true clustered indexes all tables are heaps which is something most use all the time in MSSQL, usually your primary key is also set as the clustered index so that the table IS the index and any lookup on the key has no indirection. Interesting SQLite is the opposite tables always have clustered index whether you make one or not, MSSQL give you the choice for heap or index organized tables.

sgarland 3y ago
> Also PG has no true clustered indexes all tables are heaps which is something most use all the time in MSSQL You can rewrite a table in PG to be clustered [0], but it a. locks the table b. is a one-shot, so you have to periodically redo it > Interesting SQLite is the opposite tables always have clustered index whether you make one or not AFAICT [1] you have to explicitly make a table `WITHOUT ROWID` to get a Clustered Index in SQLite. > usually your primary key is also set as the clustered index so that the table IS the index and any lookup on the key has no indirection. The main problem with this is there is a tendency by people unfamiliar with proper schema design (so, most) to use a UUID – usually v4 – as the PK. This causes no end of performance issues for RDBMS with and without clustered index, but since InnoDB also uses a clustered index, and MySQL is the most-installed RDBMS (modulo SQLite), it happens a lot. This isn't the fault of the RDBMS; clustered index has some great advantages, as you point out. But it's the reality of the current situation. [0]: https://www.postgresql.org/docs/current/sql-cluster.html https://www.postgresql.org/docs/current/sql-cluster.html [1]: https://www.sqlite.org/withoutrowid.html https://www.sqlite.org/withoutrowid.html
- SigmundA 3y ago
  >You can rewrite a table in PG to be clustered [0], but it a. locks the table b. is a one-shot, so you have to periodically redo it That is not a true clustered index or index organized table, the table is still a heap and there is still a secondary index that is the b-tree. With a real clustered index the table itself is the b-tree there is no heap and no secondary index unless you have other indexed fields as obviously there can be only one clustered index. This may be a terminology thing Oracle calls them index organized tables while MSSQL calls them clustered indexes while PG uses clustered for something that is not actually an index. >AFAICT [1] you have to explicitly make a table `WITHOUT ROWID` to get a Clustered Index in SQLite. The Row ID IS the clustered index unless you specifically say it's one of your columns. In SQLite there are no heaps only b-tree indexes. >The main problem with this is there is a tendency by people unfamiliar with proper schema design (so, most) to use a UUID – usually v4 – as the PK. This causes no end of performance issues for RDBMS with and without clustered index, but since InnoDB also uses a clustered index, and MySQL is the most-installed RDBMS (modulo SQLite), it happens a lot. Since most of your key are indexed typically this is an issue either way since that will cause fragmentation in clustered or non clustered indexes on a random UUID requiring page splits etc.
  - sgarland 3y ago
    > That is not a true clustered index or index organized table, the table is still a heap and there is still a secondary index that is the b-tree. Fair point. The speed increase is still decent [0], although if you don't need most of the tuples (and ignoring TOAST-ed columns), you could achieve similar results with a simple REINDEX. > Since most of your key are indexed typically this is an issue either way since that will cause fragmentation in clustered or non clustered indexes on a random UUID requiring page splits etc. Agreed. I keep trying to convince Postgres fanboys that it matters, to relatively little success. I think the tide is slowly turning as "thought leaders" have begun publishing blog posts on this. [0]: https://gist.github.com/stephanGarland/0ba0d6348ca0dedae8b4c2269f2b3eb9 https://gist.github.com/stephanGarland/0ba0d6348ca0dedae8b4c...
davidrowley 3y ago
> Also PG has no true clustered indexes all tables are heaps which is something most use all the time in MSSQL, usually your primary key is also set as the clustered index so that the table IS the index and any lookup on the key has no indirection. This is true, but I believe if you have an index-organised table then subsequent indexes would have to reference the primary key. With PostgreSQL, indexes can effectively point to the record in the heap by using the block number and item pointer in the block. This should mean, in theory, index lookups are faster in PostgreSQL than non-clustered index lookups in SQL Server. I was wondering, is there a concept of Bitmap Index Scans in SQL Server? PostgreSQL is able to effectively "bitwise" AND and bitwise OR bitmap index scan results from multiple indexes to obtain the subset of tuple identifier (ctids) or blocks (in lossy mode) that should be scanned in the heap. Effectively, that allows indexes on a single column to be used when the query has a condition with an equality condition on multiple columns which are indexed individually. Does SQL Server allow this for heap tables? In theory, supporting both allows DBAs to choose, but I wonder how well each is optimised. It may lead to surprises if certain query plan shapes are no longer possible when someone switches to an IOT.
- SigmundA 3y ago
  >This is true, but I believe if you have an index-organised table then subsequent indexes would have to reference the primary key. With PostgreSQL, indexes can effectively point to the record in the heap by using the block number and item pointer in the block. This should mean, in theory, index lookups are faster in PostgreSQL than non-clustered index lookups in SQL Server. Yes this is a tradeoff, if you primarily access by the clustered index its faster, if you access by secondary it slightly slower. Clustered indexes work very well for tables that have a single access pattern, they can also save significant space and I/O since data is not copied in both heap and index. Having the choice is great, Postgresql forces heaps and SQLite forces clustered, MSSQL lets you do either based on your design choice. >I was wondering, is there a concept of Bitmap Index Scans in SQL Server? Yes you can see the description here: https://learn.microsoft.com/en-us/sql/relational-databases/showplan-logical-and-physical-operators-reference?view=sql-server-ver16#operator-descriptions https://learn.microsoft.com/en-us/sql/relational-databases/s...

sgarland 3y ago

> Also PG has no true clustered indexes all tables are heaps which is something most use all the time in MSSQL You can rewrite a table in PG to be clustered [0], but it a. locks the table b. is a one-shot, so you have to periodically redo it > Interesting SQLite is the opposite tables always have clustered index whether you make one or not AFAICT [1] you have to explicitly make a table `WITHOUT ROWID` to get a Clustered Index in SQLite. > usually your primary key is also set as the clustered index so that the table IS the index and any lookup on the key has no indirection. The main problem with this is there is a tendency by people unfamiliar with proper schema design (so, most) to use a UUID – usually v4 – as the PK. This causes no end of performance issues for RDBMS with and without clustered index, but since InnoDB also uses a clustered index, and MySQL is the most-installed RDBMS (modulo SQLite), it happens a lot. This isn't the fault of the RDBMS; clustered index has some great advantages, as you point out. But it's the reality of the current situation. [0]: https://www.postgresql.org/docs/current/sql-cluster.html https://www.postgresql.org/docs/current/sql-cluster.html [1]: https://www.sqlite.org/withoutrowid.html https://www.sqlite.org/withoutrowid.html

SigmundA 3y ago
>You can rewrite a table in PG to be clustered [0], but it a. locks the table b. is a one-shot, so you have to periodically redo it That is not a true clustered index or index organized table, the table is still a heap and there is still a secondary index that is the b-tree. With a real clustered index the table itself is the b-tree there is no heap and no secondary index unless you have other indexed fields as obviously there can be only one clustered index. This may be a terminology thing Oracle calls them index organized tables while MSSQL calls them clustered indexes while PG uses clustered for something that is not actually an index. >AFAICT [1] you have to explicitly make a table `WITHOUT ROWID` to get a Clustered Index in SQLite. The Row ID IS the clustered index unless you specifically say it's one of your columns. In SQLite there are no heaps only b-tree indexes. >The main problem with this is there is a tendency by people unfamiliar with proper schema design (so, most) to use a UUID – usually v4 – as the PK. This causes no end of performance issues for RDBMS with and without clustered index, but since InnoDB also uses a clustered index, and MySQL is the most-installed RDBMS (modulo SQLite), it happens a lot. Since most of your key are indexed typically this is an issue either way since that will cause fragmentation in clustered or non clustered indexes on a random UUID requiring page splits etc.
- sgarland 3y ago
  > That is not a true clustered index or index organized table, the table is still a heap and there is still a secondary index that is the b-tree. Fair point. The speed increase is still decent [0], although if you don't need most of the tuples (and ignoring TOAST-ed columns), you could achieve similar results with a simple REINDEX. > Since most of your key are indexed typically this is an issue either way since that will cause fragmentation in clustered or non clustered indexes on a random UUID requiring page splits etc. Agreed. I keep trying to convince Postgres fanboys that it matters, to relatively little success. I think the tide is slowly turning as "thought leaders" have begun publishing blog posts on this. [0]: https://gist.github.com/stephanGarland/0ba0d6348ca0dedae8b4c2269f2b3eb9 https://gist.github.com/stephanGarland/0ba0d6348ca0dedae8b4c...

davidrowley 3y ago

> Also PG has no true clustered indexes all tables are heaps which is something most use all the time in MSSQL, usually your primary key is also set as the clustered index so that the table IS the index and any lookup on the key has no indirection. This is true, but I believe if you have an index-organised table then subsequent indexes would have to reference the primary key. With PostgreSQL, indexes can effectively point to the record in the heap by using the block number and item pointer in the block. This should mean, in theory, index lookups are faster in PostgreSQL than non-clustered index lookups in SQL Server. I was wondering, is there a concept of Bitmap Index Scans in SQL Server? PostgreSQL is able to effectively "bitwise" AND and bitwise OR bitmap index scan results from multiple indexes to obtain the subset of tuple identifier (ctids) or blocks (in lossy mode) that should be scanned in the heap. Effectively, that allows indexes on a single column to be used when the query has a condition with an equality condition on multiple columns which are indexed individually. Does SQL Server allow this for heap tables? In theory, supporting both allows DBAs to choose, but I wonder how well each is optimised. It may lead to surprises if certain query plan shapes are no longer possible when someone switches to an IOT.

SigmundA 3y ago
>This is true, but I believe if you have an index-organised table then subsequent indexes would have to reference the primary key. With PostgreSQL, indexes can effectively point to the record in the heap by using the block number and item pointer in the block. This should mean, in theory, index lookups are faster in PostgreSQL than non-clustered index lookups in SQL Server. Yes this is a tradeoff, if you primarily access by the clustered index its faster, if you access by secondary it slightly slower. Clustered indexes work very well for tables that have a single access pattern, they can also save significant space and I/O since data is not copied in both heap and index. Having the choice is great, Postgresql forces heaps and SQLite forces clustered, MSSQL lets you do either based on your design choice. >I was wondering, is there a concept of Bitmap Index Scans in SQL Server? Yes you can see the description here: https://learn.microsoft.com/en-us/sql/relational-databases/showplan-logical-and-physical-operators-reference?view=sql-server-ver16#operator-descriptions https://learn.microsoft.com/en-us/sql/relational-databases/s...