Cons : It seems doing a count(*) is not the best option for PG
For this and some of the following options, if you are going to just test for existence, I would consider adding a limit 1 somewehere on the query, to let the optimizer know you only need one and it will abort the scan on first hit. Probabley not needed if you are going to give a query which uses an unique index, but it shouldn't hurt.