Thread: Why dose the planner select one bad scan plan.
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.
> Okay, 225044.255ms VS 83813.808 ms, it obviously seems that the planner > select one bad scan plan by default. Actually no, the planner chose the cheapest plan (more precisely a plan with the lowest computed cost). The first plan has a cost 600830.86 while the second one has a cost 634901.28, so the first one is chosen. To fix this, you'll have to tweak the cost variables, and maybe work_mem. See this - http://www.postgresql.org/docs/9.0/interactive/runtime-config-query.html#RUNTIME-CONFIG-QUERY-CONSTANTS (but I'm not sure which of those influence the Bitmap Heap Scan / HashAggregate plans). So you'll have to modify these values until the hash aggregate plan is cheaper. And you don't need to reboot the machine between EXPLAIN executions. And even if you do EXPLAIN ANALYZE it's not necessary - there are better ways to clear the filesystem cache. BTW this is not a bug, so it's pointless to send it to 'bugs' mailinglist. regards Tomas
> But I doubt your answer. I think the essence of the problem is when the > planner selects 'Bitmap Index Scan' and how the planner computes the cost > of 'Bitmap Index Scan'. The essence of the problem obviously is a bad estimate of the cost. The planner considers the two plans, computes the costs and then chooses the one with the lower cost. But obviously the cost does not reflect the reality (first time when the query is executed and the filesystem cache is empty). > Tom Lane said ��In principle a bitmap index scan should be significantly > faster if the index can return the bitmap more or less "natively" rather > than having to construct it. My recollection though is that a significant > amount of work is needed to make that happen, and that there is no > existing patch that tackled the problem. So I'm not sure that this report > should be taken as indicating that there's no chance of a SELECT > performance improvement. What it does say is that we have to do that work > if we want to make bitmap indexes useful.�� Tom Lane is right (as usual). The point is that when computing the cost, planner does not know whether the data are already in the filesystem cache or if it has to fetch them from the disk (which is much slower). > Okay, I want to know how the planner computes the cost of constructing > bitmap. And when the planner computes the cost of 'Bitmap Index Scan', if > it considers the influence of memory cache? As when I do not clear the > memory cache, I find the 'Bitmap Index Scan' is real fast than 'Seq > Scan'. There are two things here - loading the data from a disk into a cache (filesystem cache at the OS level / shared buffers at the PG level), and then the execution itself. PostgreSQL estimates the first part using an effective_cache_size hint, and uses that to estimate the probability that the data are already in the filesystem cache. But you're confusing him by the 'reboot' which results in an empty cache. The plan itself seems fine to me - you might play with the cost variables, but I think it won't improve the overall perfomance. Actually what you see is a worst case scenario - the plan is not bad if the data are in a cache (filesystem or shared buffers), but when Pg has to read the data from the disk, performance sucks. But is this reflecting reality? How often is the query executed? What other queries are executed on the box? What is the size of shared_buffers? If the query is executed often (compared to other queries) and the shared buffers is set high enough, most of the table will remain in the shared buffers and everything will work fine. Tomas
Thanks for your answer! And I am sorry for trading the question as a bug, and send it to 'bugs' mailing-list.
But I doubt your answer. I think the essence of the problem is when the planner selects 'Bitmap Index Scan' and how the planner computes the cost of 'Bitmap Index Scan'.
Tom Lane said “In principle a bitmap index scan should be significantly faster if the index can return the bitmap more or less "natively" rather than having to construct it. My recollection though is that a significant amount of work is needed to make that happen, and that there is no existing patch that tackled the problem. So I'm not sure that this report should be taken as indicating that there's no chance of a SELECT performance improvement. What it does say is that we have to do that work if we want to make bitmap indexes useful.”
Okay, I want to know how the planner computes the cost of constructing bitmap. And when the planner computes the cost of 'Bitmap Index Scan', if it considers the influence of memory cache? As when I do not clear the memory cache, I find the 'Bitmap Index Scan' is real fast than 'Seq Scan'.
Best Regards!
Asen
On Thu, Nov 11, 2010 at 3:43 AM, <tv@fuzzy.cz> wrote: >> Okay, I want to know how the planner computes the cost of constructing >> bitmap. And when the planner computes the cost of 'Bitmap Index Scan', if >> it considers the influence of memory cache? As when I do not clear the >> memory cache, I find the 'Bitmap Index Scan' is real fast than 'Seq >> Scan'. > > There are two things here - loading the data from a disk into a cache > (filesystem cache at the OS level / shared buffers at the PG level), and > then the execution itself. > > PostgreSQL estimates the first part using an effective_cache_size hint, > and uses that to estimate the probability that the data are already in the > filesystem cache. No, it does not do that. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company