Re: How can I get the query planner to use a bitmap index scap instead of an index scan ?

On Fri, Mar 7, 2014 at 6:46 PM, Mohan Krishnan <mohangk(at)gmail(dot)com> wrote:

> Hello folks,
>
> I have a table of about 700k rows in Postgres 9.3.3, which has the
> following structure:
>
> Columns:
> content_body - text
> publish_date - timestamp without time zone
> published - boolean
>
> Indexes:
> "articles_pkey" PRIMARY KEY, btree (id)
> "article_text_gin" gin (article_text)
> "articles_publish_date_id_index" btree (publish_date DESC NULLS
> LAST, id DESC)
>

Your indexes are on columns that are not in the list of columns you gave.
Can you show us the actual table and index definitions?

-> Index Scan using articles_pkey on articles
> (cost=0.42..462150.49 rows=3573 width=1298) (actual time=2.055..9.711
> rows=10 loops=1)
> Filter: (article_text @@ '''in_index'''::tsquery)
>
...

> -> Index Scan using articles_pkey on articles
> (cost=0.42..462150.49 rows=3573 width=1298) (actual
> time=5633.672..5633.672 rows=0 loops=1)
> Filter: (article_text @@ '''not_in_index'''::tsquery)
>

Those estimates are way off, and it is not clear why they would be. Have
you analyzed your table recently?

Cheers,

Jeff