On 10/11/2012 12:13 PM, Korisk wrote:
> Thanx for the advice, but increment table is not acceptable because it should be a plenty of them.
> Nevertheless in the investigations was achieved some progress (7.4 sec vs 19.6 sec).
> But using IOS scan
"IOS scan" ?
Do you mean some kind of I/O monitoring tool?
> you can see that there is an abnormal cost calculations it make me suspicious of little bugs.
Abnormal how?
The cost estimates aren't times, I/Os, or anything you know, they're a
purely relative figure for comparing plan costs.
> hashes=# set enable_seqscan = off;
> SET
What's your seq_page_cost and random_page_cost?
> hashes=# explain analyse verbose select name, count(name) as cnt from hashcheck group by name order by name desc;
> QUERY PLAN
>
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
> GroupAggregate (cost=10000000000.00..10000528610.88 rows=200 width=32) (actual time=0.116..7452.005 rows=4001
loops=1)
> Output: name, count(name)
> -> Index Only Scan Backward using hashcheck_name_idx on public.hashcheck
^^^^^^^^^^^^^^^^^^^^^^^^
If you don't mind the increased cost of insert/update/delete try:
CREATE INDEX hashcheck_name_rev_idx
ON public.hashcheck (name DESC);
ie create the index in descending order.
--
Craig Ringer