Re: [GENERAL] Why does query planner choose slower BitmapAnd ?

> On 22 Feb 2016, at 16:58, Tom Lane wrote: > > (BTW, is that index really on just a boolean column? It seems > unlikely that "phoneable" would be a sufficiently selective > condition to justify having an index on it. I'd seriously consider > dropping that index as another

Re: [GENERAL] Why does query planner choose slower BitmapAnd ?

* Seamus Abshere (sea...@abshere.net) wrote: > 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.) Have to admit that I continue to be interested in this as it might

Re: [GENERAL] Why does query planner choose slower BitmapAnd ?

On Mon, Feb 22, 2016, at 02:53 PM, Seamus Abshere wrote: > On Mon, Feb 22, 2016, at 02:49 PM, Tom Lane wrote: > > Seamus Abshere writes: > > > Inspired, I changed cpu_index_tuple_cost to 0.1 (default: 0.005). It > > > "fixed" my problem by preventing the BitmapAnd. > > > Is

Re: [GENERAL] Why does query planner choose slower BitmapAnd ?

On Mon, Feb 22, 2016, at 02:49 PM, Tom Lane wrote: > Seamus Abshere writes: > > Inspired, I changed cpu_index_tuple_cost to 0.1 (default: 0.005). It > > "fixed" my problem by preventing the BitmapAnd. > > Is this dangerous? > > Use a gentle tap, man, don't swing the hammer

Re: [GENERAL] Why does query planner choose slower BitmapAnd ?

Seamus Abshere writes: > Inspired, I changed cpu_index_tuple_cost to 0.1 (default: 0.005). It > "fixed" my problem by preventing the BitmapAnd. > Is this dangerous? Use a gentle tap, man, don't swing the hammer with quite so much abandon. I'd have tried doubling the setting

Re: [GENERAL] Why does query planner choose slower BitmapAnd ?

Jeff Janes writes: > I looked into this before as well, and I think it is vastly > underestimating the cost of adding a bit into the bitmap, near this > comment: > /* > * Charge a small amount per retrieved tuple to reflect the costs of > *

Re: [GENERAL] Why does query planner choose slower BitmapAnd ?

On Mon, Feb 22, 2016, at 02:30 PM, Jeff Janes wrote: > It charges 0.1 CPU_operator_cost, while reality seemed to be more like 6 > CPU_operator_cost. fdy=> select name, setting, boot_val from pg_settings where name ~ 'cpu'; name | setting | boot_val

Re: [GENERAL] Why does query planner choose slower BitmapAnd ?

On Mon, Feb 22, 2016 at 8:20 AM, Stephen Frost wrote: > > Also agreed here, but I've seen field evidence (with reasonable > configurations) that definitely shows that we're a bit too happy to go > with a BitmapAnd scan across two indexes where one returns an order of >

Re: [GENERAL] Why does query planner choose slower BitmapAnd ?

Seamus Abshere writes: > 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.) Hm. 10% is above the threshold where I'd usually think that an

Re: [GENERAL] Why does query planner choose slower BitmapAnd ?

Seamus Abshere writes: > On Mon, Feb 22, 2016, at 02:14 PM, Tom Lane wrote: >> IOW, almost certainly we *don't* realize that the query will involve >> scanning through gigabytes of index pages. But btree indexes are much >> simpler and easier to make that estimate for... >

Re: [GENERAL] Why does query planner choose slower BitmapAnd ?

On Mon, Feb 22, 2016, at 02:14 PM, Tom Lane wrote: > IOW, almost certainly we *don't* realize that the query will involve scanning > through gigabytes of index pages. But btree indexes are much simpler and > easier to make that estimate for... Isn't this the crux of my issue, at least? --

Re: [GENERAL] Why does query planner choose slower BitmapAnd ?

Stephen Frost writes: > * Tom Lane (t...@sss.pgh.pa.us) 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. > Unfortunately, this doesn't actually

Re: [GENERAL] Why does query planner choose slower BitmapAnd ?

* Tom Lane (t...@sss.pgh.pa.us) wrote: > Stephen Frost writes: > > I've not looked into the specific costing here to see why the BitmapAnd > > ended up being chosen over just doing an index scan with the btree and > > then filtering, but I do believe it to be a problem area

Re: [GENERAL] 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

Re: [GENERAL] Why does query planner choose slower BitmapAnd ?

Stephen Frost writes: > I've not looked into the specific costing here to see why the BitmapAnd > ended up being chosen over just doing an index scan with the btree and > then filtering, but I do believe it to be a problem area that would be > good to try and improve. The

Re: [GENERAL] Why does query planner choose slower BitmapAnd ?

Tom, all, * Tom Lane (t...@sss.pgh.pa.us) wrote: > Seamus Abshere writes: > > I don't understand why the query planner is choosing a BitmapAnd when an > > Index Scan followed by a filter is obviously better. > > > On Postgres 9.4.4 with 244gb memory and SSDs > > >

Re: [GENERAL] Why does query planner choose slower BitmapAnd ?

Seamus Abshere writes: > I don't understand why the query planner is choosing a BitmapAnd when an > Index Scan followed by a filter is obviously better. > On Postgres 9.4.4 with 244gb memory and SSDs > maintenance_work_mem 100 > work_mem 50 > random_page_cost 1 >

[GENERAL] Why does query planner choose slower BitmapAnd ?

hi, I don't understand why the query planner is choosing a BitmapAnd when an Index Scan followed by a filter is obviously better. (Note that "new_york_houses" is a view of table "houses" with one condition on city - and there is an index idx_houses_city. That is the Index Scan that I think it