I use the postgresql in default configuration and use inheritance way to create table.
My postgresql version is:
SELECT version();
version
--------------------------------------------------------------------------------
PostgreSQL 9.0.1 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 3.3.5, 32-bit
(1 row)
Reboot the computer to avoid memory cache. And then get the following explain:
EXPLAIN ANALYZE SELECT authdomain,authuser,count(*),sum(SIZE) FROM tbltrafficlog WHERE (PROTOCOL in ('HTTP','HTTPS','FTP')) and (TIME >= '2010-10-01 00:00:00' AND TIME < '2010-11-01 00:00:00') GROUP BY authdomain,authuser order by count(*) DESC LIMIT 10 OFFSET 0;
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=600830.83..600830.86 rows=10 width=19) (actual time=225034.470..225034.483 rows=10 loops=1)
-> Sort (cost=600830.83..600833.25 rows=968 width=19) (actual time=225034.469..225034.473 rows=10 loops=1)
Sort Key: (count(*))
Sort Method: top-N heapsort Memory: 17kB
-> HashAggregate (cost=600795.40..600809.92 rows=968 width=19) (actual time=225018.666..225019.522 rows=904 loops=1)
-> Append (cost=0.00..535281.08 rows=6551432 width=19) (actual time=4734.441..205514.878 rows=7776000 loops=1)
-> Seq Scan on tbltrafficlog (cost=0.00..11.50 rows=1 width=298) (actual time=0.001..0.001 rows=0 loops=1)
Filter: (("time" >= '2010-10-01 00:00:00'::timestamp without time zone) AND ("time" < '2010-11-01 00:00:00'::timestamp without time zone) AND ((protocol)::text = ANY ('{HTTP,HTTPS,FTP}'::text[])))
-> Bitmap Heap Scan on tbltrafficlog_20101001 tbltrafficlog (cost=4471.33..17819.25 rows=218129 width=19) (actual time=4734.437..6096.206 rows=259200 loops=1)
Recheck Cond: ((protocol)::text = ANY ('{HTTP,HTTPS,FTP}'::text[]))
Filter: (("time" >= '2010-10-01 00:00:00'::timestamp without time zone) AND ("time" < '2010-11-01 00:00:00'::timestamp without time zone))
-> Bitmap Index Scan on tbltrafficlog_20101001_protocol_idx (cost=0.00..4416.80 rows=218129 width=0) (actual time=4731.860..4731.860 rows=259200 loops=1)
Index Cond: ((protocol)::text = ANY ('{HTTP,HTTPS,FTP}'::text[]))
…
-> Bitmap Heap Scan on tbltrafficlog_20101030 tbltrafficlog (cost=4472.75..17824.12 rows=218313 width=19) (actual time=4685.536..6090.222 rows=259200 loops=1)
Recheck Cond: ((protocol)::text = ANY ('{HTTP,HTTPS,FTP}'::text[]))
Filter: (("time" >= '2010-10-01 00:00:00'::timestamp without time zone) AND ("time" < '2010-11-01 00:00:00'::timestamp without time zone))
-> Bitmap Index Scan on tbltrafficlog_20101030_protocol_idx (cost=0.00..4418.17 rows=218313 width=0) (actual time=4677.147..4677.147 rows=259200 loops=1)
Index Cond: ((protocol)::text = ANY ('{HTTP,HTTPS,FTP}'::text[]))
Total runtime: 225044.255 ms
Reboot the computer again. And then I close bitmap scan manually and get the following explain:
SET SET enable_bitmapscan TO off;
EXPLAIN ANALYZE SELECT authdomain,authuser,count(*),sum(SIZE) FROM tbltrafficlog WHERE (PROTOCOL in ('HTTP','HTTPS','FTP')) and (TIME >= '2010-10-01 00:00:00' AND TIME < '2010-11-01 00:00:00') GROUP BY authdomain,authuser order by count(*) DESC LIMIT 10 OFFSET 0;
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Limit (cost=634901.26..634901.28 rows=10 width=19) (actual time=83805.465..83805.477 rows=10 loops=1)
-> Sort (cost=634901.26..634903.68 rows=968 width=19) (actual time=83805.463..83805.467 rows=10 loops=1)
Sort Key: (count(*))
Sort Method: top-N heapsort Memory: 17kB
-> HashAggregate (cost=634865.82..634880.34 rows=968 width=19) (actual time=83789.686..83790.540 rows=904 loops=1)
-> Append (cost=0.00..569351.50 rows=6551432 width=19) (actual time=0.010..64393.284 rows=7776000 loops=1)
-> Seq Scan on tbltrafficlog (cost=0.00..11.50 rows=1 width=298) (actual time=0.001..0.001 rows=0 loops=1)
Filter: (("time" >= '2010-10-01 00:00:00'::timestamp without time zone) AND ("time" < '2010-11-01 00:00:00'::timestamp without time zone) AND ((protocol)::text = ANY ('{HTTP,HTTPS,FTP}'::text[])))
-> Seq Scan on tbltrafficlog_20101001 tbltrafficlog (cost=0.00..18978.00 rows=218129 width=19) (actual time=0.008..1454.757 rows=259200 loops=1)
Filter: (("time" >= '2010-10-01 00:00:00'::timestamp without time zone) AND ("time" < '2010-11-01 00:00:00'::timestamp without time zone) AND ((protocol)::text = ANY ('{HTTP,HTTPS,FTP}'::text[])))
…
-> Seq Scan on tbltrafficlog_20101030 tbltrafficlog (cost=0.00..18978.00 rows=218313 width=19) (actual time=0.025..1483.817 rows=259200 loops=1)
Filter: (("time" >= '2010-10-01 00:00:00'::timestamp without time zone) AND ("time" < '2010-11-01 00:00:00'::timestamp without time zone) AND ((protocol)::text = ANY ('{HTTP,HTTPS,FTP}'::text[])))
Total runtime: 83813.808 ms
Okay, 225044.255ms VS 83813.808 ms, it obviously seems that the planner select one bad scan plan by default.
