Re: Index scan is not working, why??

please provide non-default config options on this host plus the same from a
host which is using an index scan, please. Also, postgresql version, OS,
and all of the other stuff that is asked for in this document:
http://wiki.postgresql.org/wiki/SlowQueryQuestions. It is impossible to say
why the query planner might be choosing a particular plan without any
insight whatsoever as to how the server is configured.

On Wed, Oct 20, 2010 at 10:25 PM, AI Rumman <rummandba(at)gmail(dot)com> wrote:

> I don't know why seq scan is running on the following query where the same
> query is giving index scan on other servers:
> explain analyze
> select *
> from act
> where act.acttype in ( 'Meeting','Call','Task');
> QUERY PLAN
>
> ----------------------------------------------------------------------------------------------------------------------------------------
> Seq Scan on act (cost=0.00..13386.78 rows=259671 width=142) (actual
> time=0.013..484.572 rows=263639 loops=1)
> Filter: (((acttype)::text = 'Meeting'::text) OR ((acttype)::text =
> 'Call'::text) OR ((acttype)::text = 'Task'::text))
> Total runtime: 732.956 ms
> (3 rows)
>
> The above query is giving index scan on other servers and even if I rewrite
> the query as follows I got index scan:
> explain analyze
> select *
> from act
> where act.acttype = 'Meeting'
> or act.acttype = 'Call';
> QUERY PLAN
>
> ----------------------------------------------------------------------------------------------------------------------------------------------
> Bitmap Heap Scan on act (cost=17.98..1083.80 rows=2277 width=142) (actual
> time=1.901..9.722 rows=4808 loops=1)
> Recheck Cond: (((acttype)::text = 'Meeting'::text) OR ((acttype)::text =
> 'Call'::text))
> -> BitmapOr (cost=17.98..17.98 rows=2281 width=0) (actual
> time=1.262..1.262 rows=0 loops=1)
> -> Bitmap Index Scan on act_acttype_idx (cost=0.00..8.99 rows=1141
> width=0) (actual time=0.790..0.790 rows=3181 loops=1)
> Index Cond: ((acttype)::text = 'Meeting'::text)
> -> Bitmap Index Scan on act_acttype_idx (cost=0.00..8.99 rows=1141
> width=0) (actual time=0.469..0.469 rows=1630 loops=1)
> Index Cond: ((acttype)::text = 'Call'::text)
> Total runtime: 14.227 ms
> (8 rows)
>
>
> \d act
> Table "public.act"
> Column | Type | Modifiers
>
> ------------------+------------------------+-------------------------------------------
> actid | integer | not null default 0
> subject | character varying(250) | not null
> semodule | character varying(20) |
> acttype | character varying(200) | not null
> date_start | date | not null
> due_date | date |
> time_start | character varying(50) |
> time_end | character varying(50) |
> sendnotification | character varying(3) | not null default '0'::character
> varying
> duration_hours | character varying(2) |
> duration_minutes | character varying(200) |
> status | character varying(200) |
> eventstatus | character varying(200) |
> priority | character varying(200) |
> location | character varying(150) |
> notime | character varying(3) | not null default '0'::character varying
> visibility | character varying(50) | not null default 'all'::character
> varying
> recurringtype | character varying(200) |
> end_date | date |
> end_time | character varying(50) |
> Indexes:
> "act_pkey" PRIMARY KEY, btree (actid)
> "act_acttype_idx" btree (acttype)
> "act_date_start_idx" btree (date_start)
> "act_due_date_idx" btree (due_date)
> "act_eventstatus_idx" btree (eventstatus)
> "act_status_idx" btree (status)
> "act_subject_idx" btree (subject)
> "act_time_start_idx" btree (time_start)
>
> Any idea please.
>