[prev in list] [next in list] [prev in thread] [next in thread] 

List:       pgsql-performance
Subject:    Re: [PERFORM] How can I get the query planner to use a bitmap index scap instead of an index scan ?
From:       Mohan Krishnan <mohangk () gmail ! com>
Date:       2014-03-10 8:14:51
Message-ID: CAK6uQa_YR6XZrdvqeF2xyc-3Qto3=AtgJtpyOXWJKeub810VCQ () mail ! gmail ! com
[Download RAW message or body]

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


-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance


[prev in list] [next in list] [prev in thread] [next in thread]