From: | "James DeMichele" <James(dot)DeMichele(at)redfin(dot)com> |
---|---|
To: | <pgsql-performance(at)postgresql(dot)org> |
Subject: | Simple select, but takes long time |
Date: | 2008-01-12 01:33:54 |
Message-ID: | 082D8A131DF72A4D88C908A1AD3DEB2201AF140E@mail-1.rf.lan |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
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.
From | Date | Subject | |
---|---|---|---|
Next Message | Michael Stone | 2008-01-12 01:58:47 | Re: Best way to index IP data? |
Previous Message | Michael Stone | 2008-01-12 01:07:40 | Re: Best way to index IP data? |