Yes, updates will be slower if you add more indexes, but if you're doing significantly more reads than writes then it's not much of an issue.
Also Postgres' query planner has been pretty smart about indexes for a long time, and its index format is pretty good as well. For example, if you have table (a, b, c) and index (b, c) Postgres can use the index to filter c by itself, which SQL Server certainly can't.
This is normally because indexes are sorted and hierarchical b-trees - it's ordered by b then c, so c is not ordered. However, a since the index could be smaller than the table itself it might be faster to scan every record of the index than the table.
Also Postgres' query planner has been pretty smart about indexes for a long time, and its index format is pretty good as well. For example, if you have table (a, b, c) and index (b, c) Postgres can use the index to filter c by itself, which SQL Server certainly can't.
This is normally because indexes are sorted and hierarchical b-trees - it's ordered by b then c, so c is not ordered. However, a since the index could be smaller than the table itself it might be faster to scan every record of the index than the table.
Postgres' query planner is a thing of wonder.