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 <rumman...@gmail.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. >