PostgreSQL 9.4 query seems very slow
Mostly, this seems to be a misunderstanding. According to your query plan, you are retrieving rows=1410288 and the query itself is not that slow. It does not "take 224.9 seconds":


Execution time: 697.753 ms



You could probably improve the performance of your query some more by increasing the locality of clustered data, i.e., CLUSTER (or pg_repack) your table based on your index domain_id_btree (that appears under the different name of test_index_9 in your query plan). Details here:


Configuring PostgreSQL for read performance


If you do that (arriving at a favorable physical order of rows) and if you mostly just INSERT rows (UPDATE / DELETE is rare) and since there seem to be many rows per domain_id you might also benefit from a BRIN index (new in Postgres 9.5).

(It's remarkable that your current index is used at all, given that you retrieve ~ 6% of all rows. This indicates that your data is mostly clustered on domain_id already - or cost settings / statistics in your DB are inaccurate.)

But that's only going to reduce the 700 ms Postgres needs to execute the query. The rest of your reported 224.9 seconds are spent somewhere else, probably network transfer and processing in client.

Now, if you could retrieve fewer rows or less data per row ...