title: Postgres combining multiple Indexes
body: <p>I have the following table/indexes -</p>
  
  <pre><code>CREATE TABLE test
  (
     coords geography(Point,4326), 
     user_id varchar(50), 
     created_at timestamp
  );
  CREATE INDEX ix_coords ON test USING GIST (coords);
  CREATE INDEX ix_user_id ON test (user_id);
  CREATE INDEX ix_created_at ON test (created_at DESC);
  </code></pre>
  
  <p>This is the query I want to execute:</p>
  
  <pre><code>select * 
  from updates 
  where ST_DWithin(coords, ST_MakePoint(-126.4, 45.32)::geography, 30000) 
  and user_id='3212312' 
  order by created_at desc
  limit 60
  </code></pre>
  
  <p>When I run the query it only uses <code>ix_coords</code> index. How can I ensure that Postgres uses <code>ix_user_id</code> and <code>ix_created_at</code> index as well for the query?</p>
  
  <p>This is a new table in which I did bulk insert of production data. Total rows in the <code>test</code> table: <strong>15,069,489</strong></p>
  
  <p>I am running PostgreSQL 9.2.1 (with Postgis) with (effective_cache_size = 2GB). This is my local OSX with 16GB RAM, Core i7/2.5 GHz, non-SSD disk.</p>
  
  <p>Adding the <code>EXPLAIN ANALYZE</code> output - </p>
  
  <pre><code>Limit  (cost=71.64..71.65 rows=1 width=280) (actual time=1278.652..1278.665 rows=60 loops=1)
     -&gt;  Sort  (cost=71.64..71.65 rows=1 width=280) (actual time=1278.651..1278.662 rows=60 loops=1)
           Sort Key: created_at
           Sort Method: top-N heapsort  Memory: 33kB
           -&gt;  Index Scan using ix_coords on test  (cost=0.00..71.63 rows=1 width=280) (actual time=0.198..1278.227 rows=178 loops=1)
                 Index Cond: (coords &amp;&amp; '0101000020E61000006666666666E63C40C3F5285C8F824440'::geography)
                 Filter: (((user_id)::text = '4f1092000b921a000100015c'::text) AND ('0101000020E61000006666666666E63C40C3F5285C8F824440'::geography &amp;&amp; _st_expand(coords, 30000::double precision)) AND _st_dwithin(coords, '0101000020E61000006666666666E63C40C3F5285C8F824440'::geography, 30000::double precision, true))
                 Rows Removed by Filter: 3122459
   Total runtime: 1278.701 ms
  </code></pre>
  
  <p>UPDATE:</p>
  
  <p>Based on the suggestions below I tried index on cords + user_id:</p>
  
  <pre><code>CREATE INDEX ix_coords_and_user_id ON updates USING GIST (coords, user_id);
  </code></pre>
  
  <p>..but get the following error:</p>
  
  <pre><code>ERROR:  data type character varying has no default operator class for access method "gist"
  HINT:  You must specify an operator class for the index or define a default operator class for the data type.
  </code></pre>
  
  <p>UPDATE:</p>
  
  <p>So the <code>CREATE EXTENSION btree_gist;</code> solved the btree/gist compound index issue. And now my index looks like</p>
  
  <pre><code>CREATE INDEX ix_coords_user_id_created_at ON test USING GIST (coords, user_id, created_at);
  </code></pre>
  
  <p>NOTE: btree_gist does not accept DESC/ASC.</p>
  
  <p>New query plan:</p>
  
  <pre><code>Limit  (cost=134.99..135.00 rows=1 width=280) (actual time=273.282..273.292 rows=60 loops=1)
     -&gt;  Sort  (cost=134.99..135.00 rows=1 width=280) (actual time=273.281..273.285 rows=60 loops=1)
           Sort Key: created_at
           Sort Method: quicksort  Memory: 41kB
           -&gt;  Index Scan using ix_updates_coords_user_id_created_at on updates  (cost=0.00..134.98 rows=1 width=280) (actual time=0.406..273.110 rows=115 loops=1)
                 Index Cond: ((coords &amp;&amp; '0101000020E61000006666666666E63C40C3F5285C8F824440'::geography) AND ((user_id)::text = '4e952bb5b9a77200010019ad'::text))
                 Filter: (('0101000020E61000006666666666E63C40C3F5285C8F824440'::geography &amp;&amp; _st_expand(coords, 30000::double precision)) AND _st_dwithin(coords, '0101000020E61000006666666666E63C40C3F5285C8F824440'::geography, 30000::double precision, true))
                 Rows Removed by Filter: 1
   Total runtime: 273.331 ms
  </code></pre>
  
  <p>The query is performing better than before, almost a second better but still not great. I guess this is the best that I can get?? I was hoping somewhere around 60-80ms. Also taking <code>order by created_at desc</code> from the query, shaves off another 100ms, meaning it is unable to use the index. Anyway to fix this?</p>