Slow sql selection with postgres index

Question

I have a production database that replicates to another host using londist. Table looks like

# \d+ usermessage Table "public.usermessage" Column | Type | Modifiers | Description -------------------+-------------------+-----------+------------- id | bigint | not null | subject | character varying | | message | character varying | | read | boolean | | timestamp | bigint | | owner | bigint | | sender | bigint | | recipient | bigint | | dao_created | bigint | | dao_updated | bigint | | type | integer | | replymessageid | character varying | | originalmessageid | character varying | | replied | boolean | | mheader | boolean | | mbody | boolean | | Indexes: "usermessage_pkey" PRIMARY KEY, btree (id) "usermessage_owner_key" btree (owner) "usermessage_recipient_key" btree (recipient) "usermessage_timestamp_key" btree ("timestamp") "usermessage_type_key" btree (type) Has OIDs: no 

If it is running in a replicated database, the selection is fast, as expected, if it is running on a production host, it is terribly slow. To make things weirder, not all timestamps are slow, some of them work quickly on both hosts. The file system and storage located behind the hosting work fine and are not under heavy use. Any ideas?

 replication# explain analyse SELECT COUNT(id) FROM usermessage WHERE owner = 1234567 AND timestamp > 1362077127010; QUERY PLAN ---------------------------------------------------------------------------------------------------------------------------------------------------- Aggregate (cost=263.37..263.38 rows=1 width=8) (actual time=0.059..0.060 rows=1 loops=1) -> Bitmap Heap Scan on usermessage (cost=259.35..263.36 rows=1 width=8) (actual time=0.055..0.055 rows=0 loops=1) Recheck Cond: ((owner = 1234567) AND ("timestamp" > 1362077127010::bigint)) -> BitmapAnd (cost=259.35..259.35 rows=1 width=0) (actual time=0.054..0.054 rows=0 loops=1) -> Bitmap Index Scan on usermessage_owner_key (cost=0.00..19.27 rows=241 width=0) (actual time=0.032..0.032 rows=33 loops=1) Index Cond: (owner = 1234567) -> Bitmap Index Scan on usermessage_timestamp_key (cost=0.00..239.82 rows=12048 width=0) (actual time=0.013..0.013 rows=0 loops=1) Index Cond: ("timestamp" > 1362077127010::bigint) Total runtime: 0.103 ms (9 rows) production# explain analyse SELECT COUNT(id) FROM usermessage WHERE owner = 1234567 AND timestamp > 1362077127010; QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------------------------------------------ Aggregate (cost=267.39..267.40 rows=1 width=8) (actual time=47536.590..47536.590 rows=1 loops=1) -> Bitmap Heap Scan on usermessage (cost=263.37..267.38 rows=1 width=8) (actual time=47532.520..47536.579 rows=3 loops=1) Recheck Cond: ((owner = 1234567) AND ("timestamp" > 1362077127010::bigint)) -> BitmapAnd (cost=263.37..263.37 rows=1 width=0) (actual time=47532.334..47532.334 rows=0 loops=1) -> Bitmap Index Scan on usermessage_owner_key (cost=0.00..21.90 rows=168 width=0) (actual time=0.123..0.123 rows=46 loops=1) Index Cond: (owner = 1234567) -> Bitmap Index Scan on usermessage_timestamp_key (cost=0.00..241.22 rows=12209 width=0) (actual time=47530.255..47530.255 rows=5255617 loops=1) Index Cond: ("timestamp" > 1362077127010::bigint) Total runtime: 47536.668 ms (9 rows) 

Answer 1

I'm less familiar with postgresql than mysql, but

(actual time = 0.013..0.013 rows = 0 loops = 1)

and

(actual time = 47530.255..47530.255 rows = 5255617 cycles = 1)

Suggests to me that there is more data in your db production, given that the strings are very different.