1. When I join two tables with "WHERE id IN (...)" versus with an explicit
join, and the join column for the inner table is a primary key, I would expect
the same behavior in both cases, but the optimizer is choosing a merge join in
one case and a merge semi join in the other. There's at most one customer
with a given id. Why not do a semi join?
Unfortunately the 9.2 planner does not make any checks to verify that customers.id is unique to perform a semi join. There is a pending patch in the 9.6 cycle to add this optimisation.
production=> select count(*) from customers;
count
--------
473733
(1 row)
...
-> Index Scan using customers_pkey on customers o (cost=0.00..63836.61 rows=836 width=8) (actual time=159.840..201915.765 rows=7672 loops=1)
Filter: (group_id = 45)
Rows Removed by Filter: 212699113
Rows Removed by Filter: 212699113 seems to indicate that your 473733 row count for "customers" is incorrect.
If you're doing lots of filtering on group_id, then perhaps you should think about adding an index on customers (group_id,id)
