Re: Why does query planner choose slower BitmapAnd ?

On Mon, Feb 22, 2016, at 01:48 PM, Tom Lane wrote:
> Given how remarkably quick the single-index scan is, I also wonder if that index is fully cached while we had to read some of the other index from kernel or SSD.

This makes sense, except that the speed of the query is the same if I
run it many times in a row. Shouldn't the partially-cached index get
loaded fully by the second query?

On Mon, Feb 22, 2016, at 01:20 PM, Stephen Frost wrote:
> The first question is probably- are we properly accounting for the cost of scanning the index vs the cost of scanning one index and then applying the filter?

I can affect the query planner's cost estimates with random_page_cost
(only), but I still can't get it to avoid the BitmapAnd - probably
because I am affecting other cost estimates in the same proportion.

No change with original settings OR cpu_tuple_cost=10 OR
seq_page_cost=10 OR (cpu_tuple_cost=0.05, seq_page_cost=1,
random_page_cost=1)

> -> BitmapAnd (cost=105894.80..105894.80 rows=21002 width=0) (actual time=4859.397..4859.397 rows=0 loops=1)
> -> Bitmap Index Scan on idx_houses_city (cost=0.00..1666.90 rows=164044 width=0) (actual time=16.098..16.098 rows=155690 loops=1)
> Index Cond: (city = 'New York'::text)
> -> Bitmap Index Scan on idx_houses_phoneable (cost=0.00..104224.60 rows=10271471 width=0) (actual time=4771.520..4771.520 rows=10647041 loops=1)
> Index Cond: (phoneable = true)

However with random_page_cost=10 (hint: cost estimates go up by 4x or
so)

> -> BitmapAnd (cost=354510.80..354510.80 rows=21002 width=0) (actual time=4603.575..4603.575 rows=0 loops=1)
> -> Bitmap Index Scan on idx_houses_city (cost=0.00..5590.90 rows=164044 width=0) (actual time=16.529..16.529 rows=155690 loops=1)
> Index Cond: (city = 'New York'::text)
> -> Bitmap Index Scan on idx_houses_phoneable (cost=0.00..348916.60 rows=10271471 width=0) (actual time=4530.424..4530.424 rows=10647041 loops=1)
> Index Cond: (phoneable = true)

I think this is why we originally set random_page_cost so "low"... it
was our way of "forcing" more index usage (we have a big, wide table).

Is there any other way to differentiate the 2 index scans? FWIW, 10% of
houses are phoneable, 0.2% are in the city. (Maybe I'm just supposed to
drop the index like Tom said.)

Best, thanks,
Seamus

--
Seamus Abshere, SCEA
+598 99 54 99 54
https://github.com/seamusabshere