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
