Strange situation.
After indexscan enabling the cost is seriously decreased.
hashes=# set enable_bitmapscan=on;
SET
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..10000596612.97 rows=200 width=32)
(actual time=0.187..7424.799 rows=4001 loops=1)
Output: name, count(name)
-> Index Only Scan using hashcheck_name_rev_idx on public.hashcheck
(cost=10000000000.00..10000466660.96 rows=25990002 width=32) (act
ual time=0.166..3698.776 rows=25990002 loops=1)
Output: name
Heap Fetches: 0
Total runtime: 7425.403 ms
(6 rows)
hashes=# set enable_indexscan=on;
SET
hashes=# explain analyse verbose select name, count(name) as cnt from
hashcheck group by name order by name desc;
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------
---------------------------------
GroupAggregate (cost=0.00..596612.97 rows=200 width=32) (actual
time=0.148..7339.115 rows=4001 loops=1)
Output: name, count(name)
-> Index Only Scan using hashcheck_name_rev_idx on public.hashcheck
(cost=0.00..466660.96 rows=25990002 width=32) (actual time=0.129.
.3653.848 rows=25990002 loops=1)
Output: name
Heap Fetches: 0
Total runtime: 7339.592 ms
(6 rows)
hashes=# set enable_seqscan=on;
SET
hashes=# explain analyse verbose select name, count(name) as cnt from
hashcheck group by name order by name desc;
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------
-----
Sort (cost=565411.67..565412.17 rows=200 width=32) (actual
time=21746.799..21747.026 rows=4001 loops=1)
Output: name, (count(name))
Sort Key: hashcheck.name
Sort Method: quicksort Memory: 315kB
-> HashAggregate (cost=565402.03..565404.03 rows=200 width=32) (actual
time=21731.551..21733.277 rows=4001 loops=1)
Output: name, count(name)
-> Seq Scan on public.hashcheck (cost=0.00..435452.02 rows=25990002
width=32) (actual time=29.431..13383.812 rows=25990002 loop
s=1)
Output: id, name, value
Total runtime: 21747.356 ms
(9 rows)
--
Sent via pgsql-performance mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
