sql - PostgreSQL 9.4 Very Slow Query -

i'm experiencing long response time query returning relatively small number of rows. takes on 3 minutes return ~1.3 million rows. assume indexing problem except counting rows takes no time @ all.

amazon aws: it's running on rds t2.medium ssd (iops disabled) according 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 created hash index on afterwards doesn't seem have effect.

the slow query

select * ip_range_domains domain_id = 400266; 

the query above took 224.9 seconds

=> select count(*) ip_range_domains domain_id = 400266;   count   ---------  1383530 (1 row) 

the query above took 164 milliseconds return.

=> select count(*) ip_range_domains;   count    ----------  23156869 (1 row) 

the query above took 1.9 seconds complete.

=> explain (analyze, buffers) select * ip_range_domains 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 gcc (gcc) 4.8.2 20140120 (red hat 4.8.2-16), 64-bit (1 row) 

rds info:

db.t2.medium iops: disabled