Hi,
I am having a really hard time trying to figure out why my simple count(*) query is taking so long. I have a table with 1,296,070 rows in it. There are 2 different types of information that each row has that I care about:
status : character(1)
source_id : bigint
Then, I have the following index on the table:
“this_index” (status, source_id, <another_column>)
Now when I do the following select, it takes a really long time:
stingray_4_4_d=# explain analyze select count(*) from listings where insert_status = '1' and data_source_id = 52;
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=15222.83..15222.84 rows=1 width=0) (actual time=87050.129..87050.130 rows=1 loops=1)
-> Index Scan using listing_status_idx on listings (cost=0.00..15211.20 rows=4649 width=0) (actual time=31.118..87031.776 rows=17209 loops=1)
Index Cond: ((insert_status = '1'::bpchar) AND (data_source_id = 52))
Total runtime: 87050.213 ms
(4 rows)
I actually have the same exact data over on a Mysql box, with the same exact index, and that runs in 0.10 seconds.
Clearly something is wrong. Here are a couple of the parameters I have set on my Postgres box:
stingray_4_4_d=# show shared_buffers;
shared_buffers
----------------
1900MB
(1 row)
stingray_4_4_d=# show max_fsm_pages;
max_fsm_pages
---------------
5000000
(1 row)
Any help would be much appreciated. This is really frustrating. Thanks.
