I have a situation that is giving me small fits, and would like to see if anyone can shed any light on it.
I have a modest table (@1.4 million rows, and growing), that has a variety of queries run against it. One is
a very straightforward one - pull a set of distinct rows out based on two columns, with a simple where clause
based on one of the indexed columns. For illustration here, I've removed the distinct and order-by clauses, as
they are not the culprits.
Before I go on - v7.4.1, currently on a test box, dual P3, 1G ram, 10K scsi, Slackware 9 or so. The table has been
vacuumed and analyzed. Even offered pizza and beer. Production box will be a dual Xeon with 2G ram and RAID 5.
When the query is run with a where clause that returns small number of rows, the query uses the index and is quite speedy:
rav=# explain analyze select casno, parameter from hai.results where site_id = 9982;
QUERY PLAN
------------------------------------------------------------------------ --------------------------------------------------------------
Index Scan using hai_res_siteid_ndx on results (cost=0.00..7720.87 rows=2394 width=30) (actual time=12.118..12.933 rows=50 loops=1)
Index Cond: (site_id = 9982)
Total runtime: 13.145 ms
When a query is run that returns a much larger set, the index is not used, I assume because the planner thinks that a sequential scan
would work just as well with a large result set:
rav=# explain analyze select casno, parameter from hai.results where site_id = 18;
QUERY PLAN
------------------------------------------------------------------------ ----------------------------------------------
Seq Scan on results (cost=0.00..73396.39 rows=211205 width=30) (actual time=619.020..15012.807 rows=186564 loops=1)
Filter: (site_id = 18)
Total runtime: 15279.789 ms
(3 rows)
Unfortunately, its way off:
rav=# set enable_seqscan=off;
SET
rav=# explain analyze select casno, parameter from hai.results where site_id = 18;
QUERY PLAN
------------------------------------------------------------------------ -----------------------------------------------------------------------
Index Scan using hai_res_siteid_ndx on results (cost=0.00..678587.01 rows=211205 width=30) (actual time=9.575..3569.387 rows=186564 loops=1)
Index Cond: (site_id = 18)
Total runtime: 3872.292 ms
(3 rows)
I would like, of course, for it to use the index, given that it takes 20-25% of the time. Fiddling with CPU_TUPLE_COST doesn't do anything until I exceed
0.5, which strikes me as a bit high (though please correct me if I am assuming too much...). RANDOM_PAGE_COST seems to have no effect. I suppose I could
cluster it, but it is constantly being added to, and would have to be re-done on a daily basis (if not more).
Any suggestions?
--------------------
Andrew Rawnsley President The Ravensfield Digital Resource Group, Ltd. (740) 587-0114 www.ravensfield.com
---------------------------(end of broadcast)--------------------------- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
