title: Slow Postgres 9.3 queries
body: <p>I'm trying to figure out if I can speed up two queries on a database storing email messages. Here's the table:</p>
  
  <pre><code>\d messages;
                               Table "public.messages"
       Column     |  Type   |                       Modifiers
  ----------------+---------+-------------------------------------------------------
   id             | bigint  | not null default nextval('messages_id_seq'::regclass)
   created        | bigint  |
   updated        | bigint  |
   version        | bigint  |
   threadid       | bigint  |
   userid         | bigint  |
   groupid        | bigint  |
   messageid      | text    |
   date           | bigint  |
   num            | bigint  |
   hasattachments | boolean |
   placeholder    | boolean |
   compressedmsg  | bytea   |
   revcount       | bigint  |
   subject        | text    |
   isreply        | boolean |
   likes          | bytea   |
   isspecial      | boolean |
   pollid         | bigint  |
   username       | text    |
   fullname       | text    |
  Indexes:
      "messages_pkey" PRIMARY KEY, btree (id)
      "idx_unique_message_messageid" UNIQUE, btree (groupid, messageid)
      "idx_unique_message_num" UNIQUE, btree (groupid, num)
      "idx_group_id" btree (groupid)
      "idx_message_id" btree (messageid)
      "idx_thread_id" btree (threadid)
      "idx_user_id" btree (userid)
  </code></pre>
  
  <p>Output from <code>SELECT relname, relpages, reltuples::numeric, pg_size_pretty(pg_table_size(oid)) FROM pg_class WHERE oid='messages'::regclass;</code></p>
  
  <p>is</p>
  
  <pre><code> relname  | relpages | reltuples | pg_size_pretty
  ----------+----------+-----------+----------------
   messages |  1584913 |   7337880 | 32 GB
  </code></pre>
  
  <p>Some possibly relevant postgres config values:</p>
  
  <pre><code>shared_buffers = 1536MB
  effective_cache_size = 4608MB
  work_mem = 7864kB
  maintenance_work_mem = 384MB
  </code></pre>
  
  <p>Here are the explain analyze outputs:</p>
  
  <pre><code>explain analyze SELECT * FROM messages WHERE groupid=1886 ORDER BY id ASC LIMIT 20 offset 4440;
                                                                        QUERY PLAN
  -------------------------------------------------------------------------------------------------------------------------------------------------------
   Limit  (cost=479243.63..481402.39 rows=20 width=747) (actual time=14167.374..14167.408 rows=20 loops=1)
     -&gt;  Index Scan using messages_pkey on messages  (cost=0.43..19589605.98 rows=181490 width=747) (actual time=14105.172..14167.188 rows=4460 loops=1)
           Filter: (groupid = 1886)
           Rows Removed by Filter: 2364949
   Total runtime: 14167.455 ms
  (5 rows)
  </code></pre>
  
  <p>The second query:</p>
  
  <pre><code>explain analyze SELECT * FROM messages WHERE groupid=1886 ORDER BY created ASC LIMIT 20 offset 4440;
                                                                          QUERY PLAN
  ----------------------------------------------------------------------------------------------------------------------------------------------------------
   Limit  (cost=538650.72..538650.77 rows=20 width=747) (actual time=671.983..671.992 rows=20 loops=1)
     -&gt;  Sort  (cost=538639.62..539093.34 rows=181490 width=747) (actual time=670.680..671.829 rows=4460 loops=1)
           Sort Key: created
           Sort Method: top-N heapsort  Memory: 7078kB
           -&gt;  Bitmap Heap Scan on messages  (cost=7299.11..526731.31 rows=181490 width=747) (actual time=84.975..512.969 rows=200561 loops=1)
                 Recheck Cond: (groupid = 1886)
                 -&gt;  Bitmap Index Scan on idx_unique_message_num  (cost=0.00..7253.73 rows=181490 width=0) (actual time=57.239..57.239 rows=203423 loops=1)
                       Index Cond: (groupid = 1886)
   Total runtime: 672.787 ms
  (9 rows)
  </code></pre>
  
  <p>This is on an SSD, 8GB Ram instance, load average is usually around 0.15.</p>
  
  <p>I'm definitely no expert. Is this a case of the data just being spread throughout the disk? Is my only solution to use CLUSTER?</p>
  
  <p>One thing I don't understand is why is it using <code>idx_unique_message_num</code> as the index for the second query. And why is ordering by ID so much slower?</p>