pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedIndex scan is not working, why??
5+ messages / 5 participants
[nested] [flat]
* Index scan is not working, why??
@ 2010-10-21 05:25 AI Rumman <rummandba@gmail.com>
2010-10-21 07:51 ` Re: Index scan is not working, why?? Samuel Gendler <sgendler@ideasculptor.com>
2010-10-21 13:27 ` Re: Index scan is not working, why?? Mladen Gogala <mladen.gogala@vmsinfo.com>
2010-10-21 20:13 ` Re: Index scan is not working, why?? Igor Neyman <ineyman@perceptron.com>
0 siblings, 3 replies; 5+ messages in thread
From: AI Rumman @ 2010-10-21 05:25 UTC (permalink / raw)
To: 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.
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Index scan is not working, why??
2010-10-21 05:25 Index scan is not working, why?? AI Rumman <rummandba@gmail.com>
@ 2010-10-21 07:51 ` Samuel Gendler <sgendler@ideasculptor.com>
2010-10-21 07:54 ` Re: Index scan is not working, why?? Scott Marlowe <scott.marlowe@gmail.com>
2 siblings, 1 reply; 5+ messages in thread
From: Samuel Gendler @ 2010-10-21 07:51 UTC (permalink / raw)
To: AI Rumman <rummandba@gmail.com>; +Cc: pgsql-performance
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 <rummandba@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.
>
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Index scan is not working, why??
2010-10-21 05:25 Index scan is not working, why?? AI Rumman <rummandba@gmail.com>
2010-10-21 07:51 ` Re: Index scan is not working, why?? Samuel Gendler <sgendler@ideasculptor.com>
@ 2010-10-21 07:54 ` Scott Marlowe <scott.marlowe@gmail.com>
0 siblings, 0 replies; 5+ messages in thread
From: Scott Marlowe @ 2010-10-21 07:54 UTC (permalink / raw)
To: Samuel Gendler <sgendler@ideasculptor.com>; +Cc: AI Rumman <rummandba@gmail.com>; pgsql-performance
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.
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Index scan is not working, why??
2010-10-21 05:25 Index scan is not working, why?? AI Rumman <rummandba@gmail.com>
@ 2010-10-21 13:27 ` Mladen Gogala <mladen.gogala@vmsinfo.com>
2 siblings, 0 replies; 5+ messages in thread
From: Mladen Gogala @ 2010-10-21 13:27 UTC (permalink / raw)
To: AI Rumman <rummandba@gmail.com>; +Cc: pgsql-performance
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Index scan is not working, why??
2010-10-21 05:25 Index scan is not working, why?? AI Rumman <rummandba@gmail.com>
@ 2010-10-21 20:13 ` Igor Neyman <ineyman@perceptron.com>
2 siblings, 0 replies; 5+ messages in thread
From: Igor Neyman @ 2010-10-21 20:13 UTC (permalink / raw)
To: AI Rumman <rummandba@gmail.com>; pgsql-performance
> -----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
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2010-10-21 20:13 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2010-10-21 05:25 Index scan is not working, why?? AI Rumman <rummandba@gmail.com>
2010-10-21 07:51 ` Samuel Gendler <sgendler@ideasculptor.com>
2010-10-21 07:54 ` Scott Marlowe <scott.marlowe@gmail.com>
2010-10-21 13:27 ` Mladen Gogala <mladen.gogala@vmsinfo.com>
2010-10-21 20:13 ` Igor Neyman <ineyman@perceptron.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox