Thread: Index scan is not working, why??

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)

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.

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 Thu, Oct 21, 2010 at 1:51 AM, Samuel Gendler
<sgendler@ideasculptor.com> wrote:
> 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.

I know it's mentioned in that wiki doc, but the ddl for the table and
its indexes, or the output of \d tablename is quite useful and should
be included as well.

AI Rumman 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)
Al, what percentage of the rows fits the above criteria? How big are
your histograms?

--
Mladen Gogala
Sr. Oracle DBA
1500 Broadway
New York, NY 10036
(212) 329-5251
www.vmsinfo.com

> -----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