PostgreSQL 9.4 query seems very slow

Question

I'm experiencing a very long response time for a query returning a relatively small number of rows. It takes over three minutes to return ~1.3 million rows. I would assume it was an indexing problem except counting the rows takes almost no time at all.

Amazon AWS: It's running on an RDS t2.medium with a SSD (IOPS disabled) according to AWS console.

The table:

CREATE TABLE IF NOT EXISTS ip_range_domains (
  ip_range_domain_id     BIGSERIAL PRIMARY KEY,
  domain_id              BIGINT REFERENCES domains       NOT NULL,
  source_type_id         INTEGER REFERENCES source_types NOT NULL,
  low                    INET                            NOT NULL,
  high                   INET                            NOT NULL,
  auto_high_conf         BOOLEAN                         NOT NULL DEFAULT FALSE,
  invalidation_reason_id INTEGER REFERENCES invalidation_reasons  DEFAULT NULL,
  invalidated_at         TIMESTAMP WITHOUT TIME ZONE              DEFAULT NULL,
  created_at             TIMESTAMP WITHOUT TIME ZONE     NOT NULL DEFAULT current_timestamp
);
CREATE INDEX domain_id_btree ON ip_range_domains (domain_id);

I also created a hash index on it afterwards but it doesn't seem to have any effect.

The slow query:

SELECT * FROM ip_range_domains WHERE domain_id = 400266;

The query above took 224.9 Seconds:

=> SELECT COUNT(*) FROM ip_range_domains WHERE domain_id = 400266;
  count  
---------
 1383530

The query above took 164 milliseconds to return:

=> SELECT COUNT(*) FROM ip_range_domains;
  count   
----------
 23156869

The query above took 1.9 seconds to complete:

=> EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM ip_range_domains WHERE domain_id = 400266;
                                                                QUERY PLAN                                                                 
-------------------------------------------------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on ip_range_domains  (cost=26398.17..282905.77 rows=1410288 width=55) (actual time=94.046..476.018 rows=1383530 loops=1)
   Recheck Cond: (domain_id = 400266)
   Heap Blocks: exact=44000
   Buffers: shared hit=47783
   ->  Bitmap Index Scan on test_index_9  (cost=0.00..26045.60 rows=1410288 width=0) (actual time=85.699..85.699 rows=1383530 loops=1)
         Index Cond: (domain_id = 400266)
         Buffers: shared hit=3783
 Planning time: 0.122 ms
 Execution time: 697.753 ms
(9 rows)

Postgres Version:

=> select version();
                                                    version                                                    
---------------------------------------------------------------------------------------------------------------
 PostgreSQL 9.4.4 on x86_64-unknown-linux-gnu, compiled by gcc (GCC) 4.8.2 20140120 (Red Hat 4.8.2-16), 64-bit
(1 row)

RDS Info:

db.t2.medium
IOPS: disabled

Answer 1

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 ...