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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
