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

On Mon, Mar 10, 2014 at 4:46 AM, Jeff Janes <jeff(dot)janes(at)gmail(dot)com> wrote:
>
> 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?

Sorry about that, here is the table and the index definitions

Table "public.articles"
Column | Type |
Modifiers
----------------------+-----------------------------+-------------------------------------------------------
id | integer | not null default
nextval('articles_id_seq'::regclass)
title | text | not null
content_body | text |
publish_date | timestamp without time zone |
created_at | timestamp without time zone | not null
published | boolean |
updated_at | timestamp without time zone | not null
category_id | integer | not null
article_text | tsvector |

Indexes:
"articles_pkey" PRIMARY KEY, btree (id)
"article_text_gin" gin (article_text)
"articles_category_id_index" btree (category_id)
"articles_created_at" btree (created_at)
"articles_publish_date_id_index" btree (publish_date DESC NULLS
LAST, id DESC)
"articles_published_index" btree (published)

>
>> -> 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?

Yes I have analyzed them and rerun the queries - there is no
difference. What more debugging information can should I look at to
determine why the estimates are way off ?

> Cheers,
>
> Jeff

--
Mohan