Hi:
I was testing a database when notice that it does not
used the new index I created. So after a couple of
VACUUM ANALYZE it tried the following test queries.
**** TEST CASE #1 ***********
loyalty=# set enable_seqscan=off;
SET VARIABLE
loyalty=# explain analyze select count(*) from points
where branch_cd=1 ;
NOTICE: QUERY PLAN:
Aggregate (cost=119123.54..119123.54 rows=1 width=0)
(actual time=811.08..811.0
8 rows=1 loops=1)
-> Index Scan using idx_monthly_branch on points
(cost=0.00..1187
65.86 rows=143073 width=0) (actual time=0.19..689.75
rows=136790 loops=1)
Total runtime: 811.17 msec
***** TEST CASE #2 *********
loyalty=# set enable_seqscan=on;
SET VARIABLE
loyalty=# explain analyze select count(*) from points
where branch_cd=1 ;
NOTICE: QUERY PLAN:
Aggregate (cost=62752.34..62752.34 rows=1 width=0)
(actual time=3593.93..3593.9
3 rows=1 loops=1)
-> Seq Scan on points (cost=0.00..62681.70
rows=28254 width=0) (a
ctual time=0.33..3471.54 rows=136790 loops=1)
Total runtime: 3594.01 msec
*** TEST CASE #3 (Sequential scan turned off) ******
loyalty=# explain select * from points where
branch_cd=5;
NOTICE: QUERY PLAN:
Index Scan using idx_monthly_branch on points
(cost=0.00..49765.12 r
ows=16142 width=55)
I am wondering why in test case #2 it did not use
an index scan, where as in case #3 it did. The number
of rows in test #2 and #3 are just a small subset of
table "points".
The following are the number of elements in the
table:
branch_cd = 1 ---> 136,970
branch_cd = 5 ---> 39,385
count(*) ---> 2,570,173
Its rather strange why "SELECT COUNT(*)...WHERE
branch_cd=1" uses sequential scan even though it just
comprises 5.3% of whole table...
I'ts also strange because of the ff: (Remember test
case 1 and 2 are the same query)
test 1 --> seq_scan=off --> 811.17 msec
test 2 --> seq_scan=on --> 3594.01 msec
Test #1 have 400% improvement over Test #2, yet the
query plan for test #2 is the default.
Are there way to let the planner improve the choice
in using an index or not? BTW the "cost" variables
are set to the default for the test.
Thank you in advance.
ludwig.
__________________________________________________
Do you Yahoo!?
New DSL Internet Access from SBC & Yahoo!
http://sbc.yahoo.com
