Slow sql select with index on postgres

Question

I have a production database which is replicated to a another host using londist. The 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 executed on the replicated database, the select is fast as expected, if executed on the production host it's horrible slow. To make things more strange, not all timestamps are slow, some of them are fast on both hosts. The filesystem and the storage behind the production host is fine and not under heavy usage. 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)

Try running SELECT COUNT(timestamp) FROM usermessage WHERE owner = 1234567 on both — Daniel Williams
– Daniel Williams, Commented Mar 7, 2013 at 16:14
Well, sometimes the solution is so easy. After running vacuum analyse on the related table my problem is solved — frank
– frank, Commented Mar 8, 2013 at 15:08

Daniel Williams · Accepted Answer · 2013-03-07 15:58:37Z

4

I am 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 loops=1)

Suggests to me that your production db has more data given that the rows are drastically different.

answered Mar 7, 2013 at 15:58

Daniel Williams

8,9254 gold badges40 silver badges49 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Slow sql select with index on postgres

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related