Hi All I have a query where the planner makes a wrong cost estimate, it looks like it underestimates the cost of a "Bitmap Heap Scan" compared to an "Index Scan". This it the two plans, I have also pasted them below: Slow (189ms): http://explain.depesz.com/s/2Wq Fast (21ms): http://explain.depesz.com/s/ThQ I have run "VACUUM FULL VERBOSE ANALYZE". I have configured shared_buffers and effective_cache_size, that didn't solve my problem, the estimates was kept the same and both queries got faster. What can I do to fix the cost estimate? Regards, Kim Hansen ======== yield=> SELECT version(); version ------------------------------------------------------------------------------------------------------- PostgreSQL 9.1.3 on x86_64-unknown-linux-gnu, compiled by gcc-4.4.real (Debian 4.4.5-8) 4.4.5, 64-bit (1 row) yield=> explain analyze select "filtered_demands"."pol" as "c0" from "demands"."filtered_demands" as "filtered_demands" where ("filtered_demands"."pod" = 'VELAG') group by "filtered_demands"."pol" order by "filtered_demands"."pol" ASC NULLS LAST; QUERY PLAN ----------------------------------------------------------------------------------------------------------------------------------------------------------- Sort (cost=38564.80..38564.80 rows=2 width=6) (actual time=188.987..189.003 rows=221 loops=1) Sort Key: pol Sort Method: quicksort Memory: 35kB -> HashAggregate (cost=38564.77..38564.79 rows=2 width=6) (actual time=188.796..188.835 rows=221 loops=1) -> Bitmap Heap Scan on filtered_demands (cost=566.23..38503.77 rows=24401 width=6) (actual time=6.501..182.634 rows=18588 loops=1) Recheck Cond: (pod = 'VELAG'::text) -> Bitmap Index Scan on filtered_demands_pod_pol_idx (cost=0.00..560.12 rows=24401 width=0) (actual time=4.917..4.917 rows=18588 loops=1) Index Cond: (pod = 'VELAG'::text) Total runtime: 189.065 ms (9 rows) yield=> set enable_bitmapscan = false; SET yield=> explain analyze select "filtered_demands"."pol" as "c0" from "demands"."filtered_demands" as "filtered_demands" where ("filtered_demands"."pod" = 'VELAG') group by "filtered_demands"."pol" order by "filtered_demands"."pol" ASC NULLS LAST; QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------------------------------------------ Group (cost=0.00..76534.33 rows=2 width=6) (actual time=0.028..20.823 rows=221 loops=1) -> Index Scan using filtered_demands_pod_pol_idx on filtered_demands (cost=0.00..76473.33 rows=24401 width=6) (actual time=0.027..17.174 rows=18588 loops=1) Index Cond: (pod = 'VELAG'::text) Total runtime: 20.877 ms (4 rows) yield=> -- Kim Rydhof Thor Hansen Vadgårdsvej 3, 2. tv. 2860 Søborg Phone: +45 3091 2437 -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
