"Jeffrey W. Baker" <[EMAIL PROTECTED]> writes:
> ... If bitmap
> scan is disabled, the planner will pick index scan even in cases when
> sequential scan is 10x faster:
> scratch=# set enable_bitmapscan to off;
> SET
> scratch=# explain analyze select count(1) from test where random >=
> 1429076987 and random < 1429076987 + 10000000;
> QUERY PLAN
>
> --------------------------------------------------------------------------------------------------------------------------------------------
> Aggregate (cost=170142.03..170142.03 rows=1 width=0) (actual
> time=177419.182..177419.185 rows=1 loops=1)
> -> Index Scan using test_rand_idx on test (cost=0.00..170034.11
> rows=43167 width=0) (actual time=0.035..177255.696 rows=46764 loops=1)
> Index Cond: ((random >= 1429076987) AND (random < 1439076987))
> Total runtime: 177419.302 ms
> (4 rows)
> scratch=# set enable_indexscan to off;
> SET
> scratch=# explain analyze select count(1) from test where random >=
> 1429076987 and random < 1429076987 + 10000000;
> QUERY PLAN
>
> ----------------------------------------------------------------------------------------------------------------------
> Aggregate (cost=204165.55..204165.55 rows=1 width=0) (actual
> time=12334.042..12334.045 rows=1 loops=1)
> -> Seq Scan on test (cost=0.00..204057.62 rows=43167 width=0) (actual
> time=17.436..12174.150 rows=46764 loops=1)
> Filter: ((random >= 1429076987) AND (random < 1439076987))
> Total runtime: 12334.156 ms
> (4 rows)
> Obviously in this case sequential scan was (would have been) a huge win.
> Incrementing random_page_cost from 4 (the default) to 5 causes the
> planner to make a better decision.
But to get the estimated cost ratio to match up with the actual cost
ratio, we'd have to raise random_page_cost to nearly 70, which is a bit
hard to credit. What was the platform being tested here?
regards, tom lane
---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match
