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
--
Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
