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.
