Thread: what is the cause that scan type is showing as 'seq scan' after indexing
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
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
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
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
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
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 > >