Speed up count queries on a couple million rows
While computing numbers for all or most users, it's much more efficient to use plain subqueries to aggregate counts per user before joining instead of correlated subqueries:

SELECT u.*, p.product_count, c.collection_count
FROM   users u
LEFT   JOIN (
   SELECT user_id AS id, count(*) AS product_count
   FROM   products
   GROUP  BY 1
   ) p USING (id)
LEFT   JOIN (
   SELECT user_id  As id, count(*) AS collection_count
   FROM   collections
   GROUP  BY 1
   ) c USING (id);


The index-only scan and bitmap index we see in the EXPLAIN output only benefit queries for a small subset of rows (LIMIT 100). Your test is misleading in this respect. While computing numbers for all (or most) users, indexes won't help. Sequential scans will be faster.

The Bitmap Heap Scan you see is only the second step needed for the bitmap index scan - which comes as a surprise for just 100 rows. Either your table statistics are out of date or those 100 users have a lot of related products or rows in products are highly clustered (physically, meaning multiple rows for one user reside on the same or few data pages). Postgres only switches to a bitmap index scan if it expects to find multiple rows per data page, which comes as a surprise for 100 users and 52.000.000 products (rows=355 expected and rows=382 found).

Related:


Index usage on a temporary table
Configuring PostgreSQL for read performance