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