Index scan is not working, why??

From: AI Rumman <rummandba(at)gmail(dot)com>
To: pgsql-performance(at)postgresql(dot)org
Subject: Index scan is not working, why??
Date: 2010-10-21 05:25:06
Message-ID: AANLkTinvBPfo8KcNVEkvt35fEOeO9OREyvq8WpcqbrBg@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-performance

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.

Responses

Browse pgsql-performance by date

  From Date Subject
Next Message Samuel Gendler 2010-10-21 07:51:21 Re: Index scan is not working, why??
Previous Message Scott Carey 2010-10-21 04:47:24 Re: Slow count(*) again...