Re: Bitmap scan is undercosted? - overestimated correlation and cost_index

On Fri, Dec 15, 2017 at 02:54:06PM -0600, Justin Pryzby wrote: > SSD: good question. > > Here's an rackspace VM with PG9.6.6, 2GB shared_buffers, 8GB RAM (~4GB of > which > is being used as OS page cache), and 32GB SSD (with random_page_cost=1). The > server is in use by our application. > > I

Re: Bitmap scan is undercosted? - overestimated correlation and cost_index

On Fri, Dec 15, 2017 at 02:54:06PM -0600, Justin Pryzby wrote: > SSD: good question. > > Here's an rackspace VM with PG9.6.6, 2GB shared_buffers, 8GB RAM (~4GB of > which > is being used as OS page cache), and 32GB SSD (with random_page_cost=1). The > server is in use by our application. > > I

Re: Bitmap scan is undercosted? - overestimated correlation and cost_index

On Tue, Dec 05, 2017 at 01:50:11PM -0500, Tom Lane wrote: > Jeff Janes writes: > > On Dec 3, 2017 15:31, "Tom Lane" wrote: > >> Jeff Janes writes: > >>> But I do see that ties within the logical order of the column values are > >>>

Re: Bitmap scan is undercosted? - boolean correlation

Jeff Janes writes: > On Dec 3, 2017 15:31, "Tom Lane" wrote: >> Jeff Janes writes: >>> But I do see that ties within the logical order of the column values are >>> broken to agree with the physical order. That is wrong, right? Is

Re: Bitmap scan is undercosted?

I wrote: > I tried creating multiple-column statistics using the v10 facility for > that: > regression=# create statistics s1 on num, flag from aaa; > CREATE STATISTICS > regression=# analyze aaa; > ANALYZE > but that changed the estimate not at all, which surprised me because > dependency

Re: Bitmap scan is undercosted? - boolean correlation

On Dec 3, 2017 15:31, "Tom Lane" wrote: Jeff Janes writes: > On Sat, Dec 2, 2017 at 8:04 PM, Justin Pryzby wrote: >> It thinks there's somewhat-high correlation since it gets a list of x >> and y values (integer positions by

Re: Bitmap scan is undercosted? - boolean correlation

Jeff Janes writes: > On Sat, Dec 2, 2017 at 8:04 PM, Justin Pryzby wrote: >> It thinks there's somewhat-high correlation since it gets a list of x >> and y values (integer positions by logical and physical sort order) and >> 90% of the x list (logical

Re: Bitmap scan is undercosted? - boolean correlation

On Sat, Dec 2, 2017 at 8:04 PM, Justin Pryzby wrote: > On Sat, Dec 02, 2017 at 05:27:51PM -0800, Jeff Janes wrote: > > I think the non-extended stats code also has trouble with booleans. > > pg_stats gives me a correlation of 0.8 or higher for the flag column. > > It's not

Re: Bitmap scan is undercosted?

On 03/12/2017 03:27, Jeff Janes wrote: Due to that, when I disable bitmapscans and seqscans, I start getting slow index scans on the wrong index, i2 rather than i1.  I don't know why he doesn't see that in his example. When I increase effective_cache_size to 1024MB, I start getting the plan

Re: Bitmap scan is undercosted?

On 02/12/2017 23:17, Jeff Janes wrote: Right, so there is a cpu costing problem (which could only be fixed by hacking postgresql and recompiling it), but it is much smaller of a problem than the IO cost not being accurate due to the high hit rate. Fixing the CPU costing problem is unlikely to

Re: Bitmap scan is undercosted?

Jeff Janes writes: > On Fri, Dec 1, 2017 at 11:08 PM, Vitaliy Garnashevich < > vgarnashev...@gmail.com> wrote: >> # x4 tuple/operator costs - bitmap scan still a bit cheaper >> set seq_page_cost = 1.0; >> set random_page_cost = 1.0; >> set cpu_tuple_cost = 0.04; >> set

Re: Bitmap scan is undercosted?

On Fri, Dec 1, 2017 at 11:08 PM, Vitaliy Garnashevich < vgarnashev...@gmail.com> wrote: > > > seq_page_cost = 0.0 > random_page_cost = 0.0 > explain analyze select * from aaa where num = 2 and flag = true; > > Bitmap Heap Scan on aaa (cost=753.00..2003.00 rows=10257 width=5) (actual >

Re: Bitmap scan is undercosted?

On 02/12/2017 07:51, Jeff Janes wrote: On Fri, Dec 1, 2017 at 3:54 PM, Vitaliy Garnashevich > wrote: On 02/12/2017 01:11, Justin Pryzby wrote: I tried to reproduce this issue and couldn't, under PG95 and 10.1: On

Re: Bitmap scan is undercosted?

On 02/12/2017 01:11, Justin Pryzby wrote: I tried to reproduce this issue and couldn't, under PG95 and 10.1: On Fri, Dec 01, 2017 at 12:34:27PM -0600, Justin Pryzby wrote: On Fri, Dec 01, 2017 at 07:40:08PM +0200, Vitaliy Garnashevich wrote: We recently had an issue in production, where a

Re: Bitmap scan is undercosted?

I tried to reproduce this issue and couldn't, under PG95 and 10.1: On Fri, Dec 01, 2017 at 12:34:27PM -0600, Justin Pryzby wrote: > On Fri, Dec 01, 2017 at 07:40:08PM +0200, Vitaliy Garnashevich wrote: > > We recently had an issue in production, where a bitmap scan was chosen > > instead of an

Re: Bitmap scan is undercosted?

On 01/12/2017 20:34, Justin Pryzby wrote: On Fri, Dec 01, 2017 at 07:40:08PM +0200, Vitaliy Garnashevich wrote: We recently had an issue in production, where a bitmap scan was chosen instead of an index scan. Despite being 30x slower, the bitmap scan had about the same cost as the index scan.

Re: Bitmap scan is undercosted?

On Fri, Dec 01, 2017 at 07:40:08PM +0200, Vitaliy Garnashevich wrote: > We recently had an issue in production, where a bitmap scan was chosen > instead of an index scan. Despite being 30x slower, the bitmap scan had > about the same cost as the index scan. Me too, see also:

Bitmap scan is undercosted?

Hi, We recently had an issue in production, where a bitmap scan was chosen instead of an index scan. Despite being 30x slower, the bitmap scan had about the same cost as the index scan. I've found some cases where similar issues with bitmap scans were reported before: