Re: Why does query planner choose slower BitmapAnd ?
От | Seamus Abshere |
---|---|
Тема | Re: Why does query planner choose slower BitmapAnd ? |
Дата | |
Msg-id | 1456159999.999229.528415850.3CB03C80@webmail.messagingengine.com обсуждение исходный текст |
Ответ на | Re: Why does query planner choose slower BitmapAnd ? (Tom Lane <tgl@sss.pgh.pa.us>) |
Ответы |
Re: Why does query planner choose slower BitmapAnd ?
(Tom Lane <tgl@sss.pgh.pa.us>)
Re: Why does query planner choose slower BitmapAnd ? (Stephen Frost <sfrost@snowman.net>) |
Список | pgsql-general |
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 someof 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 oneindex 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=155690loops=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.520rows=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=155690loops=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.424rows=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
В списке pgsql-general по дате отправления: