[davidrowley]

> 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]

>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/s...