Postgresql not using multi-column indexes (btree_gin)
<p>I am having issues making postgres use my multi-column indexes for full search using the btree_gin extension. This is for a search page for articles. The idea behind the use of btree_gin is to be able to get the 'id' field for sorting and magazine_id as a filter:</p>

<pre><code>CREATE INDEX idx_gin_search ON article USING gin(id, magazine_id, search_vector_full) WITH (fastupdate = off);
</code></pre>

<p>Postgres decides to use a btree index on magazine instead and then filter (=slow):</p>

<pre><code>Executed SQL
SELECT ••• FROM article WHERE (( (article.search_vector) @@    
(plainto_tsquery('pg_catalog.english', 'interesting'))) AND    
article.magazine_id = 7) ORDER BY article.id ASC LIMIT 36
Time 13.4780406952 ms

QUERY PLAN
Limit  (cost=2021.87..2021.96 rows=36 width=384) (actual time=9.782..9.787 rows=36 loops=1)
  -&gt;  Sort  (cost=2021.87..2027.49 rows=2248 width=384) (actual time=9.781..9.784 rows=36 loops=1)
    Sort Key: id
    Sort Method: top-N heapsort  Memory: 53kB
    -&gt;  Index Scan using idx_magazine_id on article (cost=0.29..1952.53 rows=2248 width=384) (actual time=0.035..8.924 rows=2249 loops=1)
          Index Cond: (magazine_id = 7)
          Filter: (search_vector @@ '''interesting'''::tsquery)
          Rows Removed by Filter: 11413
Planning time: 4.600 ms
Execution time: 9.860 ms
</code></pre>

<hr>

<p>Then, what I find even understand less, is that it also refuses to use this simple btree index on the LIST page for articles, where they are just listed x per page in descending order:</p>

<pre><code>CREATE INDEX idx_btree_listing ON article USING btree(id DESC, magazine_id);
</code></pre>

<p>Again it doesn't use the multi-column index:</p>

<pre><code>Executed SQL
SELECT ••• FROM article WHERE article.magazine_id = 7
ORDER BY article.id DESC LIMIT 36
Time 1.4750957489 ms

QUERY PLAN
Limit  (cost=0.29..7.48 rows=36 width=384) (actual time=0.034..0.115 rows=36 loops=1)
-&gt;  Index Scan Backward using idx_magazine_id on article  (cost=0.29..2729.56 rows=13662 width=384) (actual time=0.031..0.107 rows=36 loops=1)
    Filter: (magazine_id = 7)
    Planning time: 1.354 ms
    Execution time: 0.207 ms
</code></pre>

<p>EDIT:
The above is a development setup with less records and only 1 magazine, hence the fast speed. Here is a log produced by auto_explain on the production server:</p>

<pre><code>duration: 230.629 ms  plan:
SELECT article.id, article.title, article.date, article.content FROM article WHERE article.magazine_id = 7 ORDER BY article.id DESC LIMIT 36

Limit  (cost=0.42..43.67 rows=36 width=306) (actual time=229.876..229.995 rows=36 loops=1)
    -&gt;  Index Scan Backward using idx_magazine_id on article (cost=0.42..239539.22 rows=199379 width=306) (actual time=229.866..229.968 rows=36 loops=1)
    Filter: (article.magazine_id = 7)
    Rows Removed by Filter: 116414
</code></pre>

<p>I would be grateful anyone could give me tips for my further debugging on this one.</p>