Simple select, but takes long time

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.

Responses

Browse pgsql-performance by date

  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?