Thread: what is the cause that scan type is showing as 'seq scan' after indexing

what is the cause that scan type is showing as 'seq scan' after indexing

From
"shreedhar"
Date:
Hi All,

Before indexing query plan was showing cost as 40.00, after indexing query
plan again showing as 'seq scan' and cost as 3060.55.
The field which i indexed is primary key to this table.
May i know
1) what is the cause that scan type is showing as 'seq scan' after indexing
also
2) why it is showing cost as high value compare to previous.


pmnport2=# explain select * from tblpermissions where accountid in
(296,290,292
,293) and raccountid not in (296,290,292,293);
NOTICE:  QUERY PLAN:

Seq Scan on tblpermissions  (cost=0.00..40.00 rows=19 width=8)

EXPLAIN
pmnport2=# create index tblpermissions_accountid_idx on tblpermissions
(account
id);
CREATE
pmnport2=# explain select * from tblpermissions where accountid in
(296,290,292
,293) and raccountid not in (296,290,292,293);
NOTICE:  QUERY PLAN:

Seq Scan on tblpermissions  (cost=0.00..3060.55 rows=1459 width=8)

EXPLAIN
pmnport2=#

With Best Regards,
Sreedhar.


"Faith, faith, faith in ourselves, faith, faith in God, this is the secret
of greatness.
If you have faith in all the three hundred and thirty millions of your
mythological gods,
and in all the gods which foreigners have now and again introduced into your
midst,
and still have no faith in yourselves, there is no salvation for you. "
(III. 190)


Re: what is the cause that scan type is showing as 'seq scan' after indexing

From
"Ross J. Reedstrom"
Date:
On Sun, Jan 26, 2003 at 06:31:50PM +0530, shreedhar wrote:
> Hi All,
>
> Before indexing query plan was showing cost as 40.00, after indexing query
> plan again showing as 'seq scan' and cost as 3060.55.
> The field which i indexed is primary key to this table.
> May i know
> 1) what is the cause that scan type is showing as 'seq scan' after indexing
> also
> 2) why it is showing cost as high value compare to previous.

You trimmed out the other parts of the EXPLAIN, so I'm just guessing,
but that cost seems suspiciously round: I'm guessing that you haven't
run VACUUM ANALYZE at all. One thing indexing does is update the 'number
of tuples' statistic. See the archives for why sequential scans still
show up (short answer: index scans aren't free, so at some point, it's
cheaper to scan the entire table than to scan both the index and the
subset of the table returned)

Ross

Re: what is the cause that scan type is showing as 'seq scan' after indexing

From
Joseph Lemm
Date:
IN RELATION TO THIS POST:

On Sun, Jan 26, 2003 at 06:31:50PM +0530, shreedhar wrote:
> Hi All,
>
> Before indexing query plan was showing cost as 40.00, after indexing query
> plan again showing as 'seq scan' and cost as 3060.55.
> The field which i indexed is primary key to this table.
> May i know
> 1) what is the cause that scan type is showing as 'seq scan' after indexing
> also
> 2) why it is showing cost as high value compare to previous.

TO WHICH ROSS REPLIED:

>You trimmed out the other parts of the EXPLAIN, so I'm just guessing,
>but that cost seems suspiciously round: I'm guessing that you haven't
>run VACUUM ANALYZE at all. One thing indexing does is update the 'number
>of tuples' statistic. See the archives for why sequential scans still
>show up (short answer: index scans aren't free, so at some point, it's
>cheaper to scan the entire table than to scan both the index and the
>subset of the table returned)



OK, so then what is the explanation for this:

               Table "public.post"
 Column |            Type             | Modifiers
--------+-----------------------------+-----------
 id     | integer                     |
 author | character varying(80)       |
 text   | text                        |
 hidden | boolean                     |
 date   | timestamp without time zone |
 host   | character varying(80)       |
Indexes: idx_post_id unique btree (id),
         post_author_index btree (author)


VACUUM ANALYZE;
VACUUM

EXPLAIN ANALYZE select host from post where author='George';
                                               QUERY PLAN

--------------------------------------------------------------------------------------------------------
 Seq Scan on post  (cost=0.00..2869.30 rows=1768 width=27) (actual
time=0.23..520.65 rows=1774 loops=1)
   Filter: (author = 'George'::character varying)
 Total runtime: 525.77 msec
(3 rows)

So the optimizer decided it's less costly to do a sequential scan here than use
the index, right?


Now:

SET ENABLE_SEQSCAN=OFF;

EXPLAIN ANALYZE select host from post where author='George';
                                                            QUERY PLAN

-----------------------------------------------------------------------------------------------------------------------------------
 Index Scan using post_author_index on post  (cost=0.00..5253.63 rows=1768
width=27) (actual time=28.92..210.25 rows=1774 loops=1)
   Index Cond: (author = 'George'::character varying)
 Total runtime: 215.00 msec
(3 rows)


So if I force an index scan, I get much better performance (215 vs 525 msec).
Does this mean that the optimizer screwed up when it recommended a sequential
scan?

Thanks.


=====
J.

__________________________________
Do you Yahoo!?
Find out what made the Top Yahoo! Searches of 2003
http://search.yahoo.com/top2003

Re: what is the cause that scan type is showing as 'seq scan' after

From
Gaetano Mendola
Date:
Joseph Lemm wrote:
> IN RELATION TO THIS POST:
>
> On Sun, Jan 26, 2003 at 06:31:50PM +0530, shreedhar wrote:
>
>>Hi All,
>>
>>Before indexing query plan was showing cost as 40.00, after indexing query
>>plan again showing as 'seq scan' and cost as 3060.55.
>>The field which i indexed is primary key to this table.
>>May i know
>>1) what is the cause that scan type is showing as 'seq scan' after indexing
>>also
>>2) why it is showing cost as high value compare to previous.
>
>
> TO WHICH ROSS REPLIED:
>
>
>>You trimmed out the other parts of the EXPLAIN, so I'm just guessing,
>>but that cost seems suspiciously round: I'm guessing that you haven't
>>run VACUUM ANALYZE at all. One thing indexing does is update the 'number
>>of tuples' statistic. See the archives for why sequential scans still
>>show up (short answer: index scans aren't free, so at some point, it's
>>cheaper to scan the entire table than to scan both the index and the
>>subset of the table returned)
>
>
>
>
> OK, so then what is the explanation for this:
>
>                Table "public.post"
>  Column |            Type             | Modifiers
> --------+-----------------------------+-----------
>  id     | integer                     |
>  author | character varying(80)       |
>  text   | text                        |
>  hidden | boolean                     |
>  date   | timestamp without time zone |
>  host   | character varying(80)       |
> Indexes: idx_post_id unique btree (id),
>          post_author_index btree (author)
>
>
> VACUUM ANALYZE;
> VACUUM
>
> EXPLAIN ANALYZE select host from post where author='George';
>                                                QUERY PLAN
>
> --------------------------------------------------------------------------------------------------------
>  Seq Scan on post  (cost=0.00..2869.30 rows=1768 width=27) (actual
> time=0.23..520.65 rows=1774 loops=1)
>    Filter: (author = 'George'::character varying)
>  Total runtime: 525.77 msec
> (3 rows)
>
> So the optimizer decided it's less costly to do a sequential scan here than use
> the index, right?
>
>
> Now:
>
> SET ENABLE_SEQSCAN=OFF;
>
> EXPLAIN ANALYZE select host from post where author='George';
>                                                             QUERY PLAN
>
-----------------------------------------------------------------------------------------------------------------------------------
>  Index Scan using post_author_index on post  (cost=0.00..5253.63 rows=1768
> width=27) (actual time=28.92..210.25 rows=1774 loops=1)
>    Index Cond: (author = 'George'::character varying)
>  Total runtime: 215.00 msec
> (3 rows)
>
>
> So if I force an index scan, I get much better performance (215 vs 525 msec).
> Does this mean that the optimizer screwed up when it recommended a sequential
> scan?

No this mean that you are instructing your optimizer in a wrong way.


Show us your configuration file and in particular these parameters:

effective_cache_size
random_page_cost
cpu_tuple_cost
cpu_index_tuple_cost
cpu_operator_cost

I use these value, that are good enough for a medium HW:

effective_cache_size = 20000
random_page_cost = 2.5
cpu_tuple_cost = 0.005
cpu_index_tuple_cost = 0.0005
cpu_operator_cost = 0.0025


Regards
Gaetano Mendola


















Re: what is the cause that scan type is showing as 'seq scan' after

From
Joseph Lemm
Date:
Gaetano, thanks.

My db has only one table (about 29,000 records), so I thought leaving
postgreqsql.conf at its defaults would be OK: the params you mention are
commented out, so they must be at their defaults, tho I can't tell what the
defaults are.
Are there any docs that talk specificially about how to set these params and
what the defaults are (the official docs don't say  much)?

Thanks.


--- Gaetano Mendola <mendola@bigfoot.com> wrote:
> Joseph Lemm wrote:
> > IN RELATION TO THIS POST:
> >
> > On Sun, Jan 26, 2003 at 06:31:50PM +0530, shreedhar wrote:
> >
> >>Hi All,
> >>
> >>Before indexing query plan was showing cost as 40.00, after indexing query
> >>plan again showing as 'seq scan' and cost as 3060.55.
> >>The field which i indexed is primary key to this table.
> >>May i know
> >>1) what is the cause that scan type is showing as 'seq scan' after indexing
> >>also
> >>2) why it is showing cost as high value compare to previous.
> >
> >
> > TO WHICH ROSS REPLIED:
> >
> >
> >>You trimmed out the other parts of the EXPLAIN, so I'm just guessing,
> >>but that cost seems suspiciously round: I'm guessing that you haven't
> >>run VACUUM ANALYZE at all. One thing indexing does is update the 'number
> >>of tuples' statistic. See the archives for why sequential scans still
> >>show up (short answer: index scans aren't free, so at some point, it's
> >>cheaper to scan the entire table than to scan both the index and the
> >>subset of the table returned)
> >
> >
> >
> >
> > OK, so then what is the explanation for this:
> >
> >                Table "public.post"
> >  Column |            Type             | Modifiers
> > --------+-----------------------------+-----------
> >  id     | integer                     |
> >  author | character varying(80)       |
> >  text   | text                        |
> >  hidden | boolean                     |
> >  date   | timestamp without time zone |
> >  host   | character varying(80)       |
> > Indexes: idx_post_id unique btree (id),
> >          post_author_index btree (author)
> >
> >
> > VACUUM ANALYZE;
> > VACUUM
> >
> > EXPLAIN ANALYZE select host from post where author='George';
> >                                                QUERY PLAN
> >
> >
>
--------------------------------------------------------------------------------------------------------
> >  Seq Scan on post  (cost=0.00..2869.30 rows=1768 width=27) (actual
> > time=0.23..520.65 rows=1774 loops=1)
> >    Filter: (author = 'George'::character varying)
> >  Total runtime: 525.77 msec
> > (3 rows)
> >
> > So the optimizer decided it's less costly to do a sequential scan here than
> use
> > the index, right?
> >
> >
> > Now:
> >
> > SET ENABLE_SEQSCAN=OFF;
> >
> > EXPLAIN ANALYZE select host from post where author='George';
> >                                                             QUERY PLAN
> >
>

-----------------------------------------------------------------------------------------------------------------------------------
> >  Index Scan using post_author_index on post  (cost=0.00..5253.63 rows=1768
> > width=27) (actual time=28.92..210.25 rows=1774 loops=1)
> >    Index Cond: (author = 'George'::character varying)
> >  Total runtime: 215.00 msec
> > (3 rows)
> >
> >
> > So if I force an index scan, I get much better performance (215 vs 525
> msec).
> > Does this mean that the optimizer screwed up when it recommended a
> sequential
> > scan?
>
> No this mean that you are instructing your optimizer in a wrong way.
>
>
> Show us your configuration file and in particular these parameters:
>
> effective_cache_size
> random_page_cost
> cpu_tuple_cost
> cpu_index_tuple_cost
> cpu_operator_cost
>
> I use these value, that are good enough for a medium HW:
>
> effective_cache_size = 20000
> random_page_cost = 2.5
> cpu_tuple_cost = 0.005
> cpu_index_tuple_cost = 0.0005
> cpu_operator_cost = 0.0025
>
>
> Regards
> Gaetano Mendola
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 8: explain analyze is your friend


=====
J.

__________________________________
Do you Yahoo!?
Find out what made the Top Yahoo! Searches of 2003
http://search.yahoo.com/top2003

Re: what is the cause that scan type is showing as 'seq scan' after

From
Gaetano Mendola
Date:
Joseph Lemm wrote:

> Gaetano, thanks.
>
> My db has only one table (about 29,000 records), so I thought leaving
> postgreqsql.conf at its defaults would be OK: the params you mention are
> commented out, so they must be at their defaults, tho I can't tell what the
> defaults are.

The values on the line commented are the default values.

> Are there any docs that talk specificially about how to set these params and
> what the defaults are (the official docs don't say  much)?


Take a look at performance NG, at least is what I use to do.



Regards
Gaetano Mendola









Re: what is the cause that scan type is showing as 'seq scan' after

From
"Glenn Wiorek"
Date:
Also take a look at  Annotated postgresql.conf and Global User Configuration
(GUC) Guide

 http://www.varlena.com/GeneralBits/Tidbits/annotated_conf_e.html

----- Original Message -----
From: "Gaetano Mendola" <mendola@bigfoot.com>
To: <pgsql-admin@postgresql.org>
Cc: "Joseph Lemm" <joelemm@yahoo.com>
Sent: Monday, January 05, 2004 8:26 AM
Subject: Re: [ADMIN] what is the cause that scan type is showing as 'seq
scan' after


> Joseph Lemm wrote:
>
> > Gaetano, thanks.
> >
> > My db has only one table (about 29,000 records), so I thought leaving
> > postgreqsql.conf at its defaults would be OK: the params you mention are
> > commented out, so they must be at their defaults, tho I can't tell what
the
> > defaults are.
>
> The values on the line commented are the default values.
>
> > Are there any docs that talk specificially about how to set these params
and
> > what the defaults are (the official docs don't say  much)?
>
>
> Take a look at performance NG, at least is what I use to do.
>
>
>
> Regards
> Gaetano Mendola
>
>
>
>
>
>
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
>                http://archives.postgresql.org
>
>