> -----Original Message-----
> From: AI Rumman [mailto:rummandba@gmail.com]
> Sent: Thursday, October 21, 2010 1:25 AM
> To: pgsql-performance@postgresql.org
> Subject: Index scan is not working, why??
>
> 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)
>
>
"Index Scan" is not alwayes prefarable to "Seq Scan", it depends on
selectivity of your query.
When retrieving substancial portion of big table seq scan is usually
faster, that's why optimizer chooses it.
Your queries (and possibly data sets in the tables on different servers)
are not the same.
Your first query (which uses seq scan) returns 259671 which is probably
substantial part of the whole table.
Your second query (which uses index scan) returns only 4808 rows, which
makes index access less costly in this case.
Regards,
Igor Neyman