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

On Mon, Mar 10, 2014 at 4:46 AM, Jeff Janes <jeff.janes@gmail.com> wrote:
>
> On Fri, Mar 7, 2014 at 6:46 PM, Mohan Krishnan <mohangk@gmail.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