Re: Bitmap scan is undercosted?
On Fri, Dec 1, 2017 at 11:08 PM, Vitaliy Garnashevich < [email protected]> 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 > time=82.212..82.212 rows=0 loops=1) > -> Bitmap Index Scan on i1 (cost=0.00..750.43 rows=10 width=0) > (actual time=17.401..17.401 rows=10 loops=1) > > Index Scan using i1 on aaa (cost=0.44..1750.43 rows=10257 width=5) > (actual time=49.766..49.766 rows=0 loops=1) > > The bitmap plan was reduced to use only one bitmap scan, and finally it > costs more than the index plan. > 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 make a difference to your real query. If you set the page costs to zero, what happens to your real query? > But I doubt that the settings seq_page_cost = random_page_cost = 0.0 > should actually be used. > Why not? If your production server really has everything in memory during normal operation, that is the correct course of action. If you ever restart the server, then you could have some unpleasant time getting it back up to speed again, but pg_prewarm could help with that. > Probably it should be instead something like 1.0/1.0 or 1.0/1.1, but other > costs increased, to have more weight. > This doesn't make any sense to me. Halving the page costs is mathematically the same as doubling all the other constants. But the first way of doing things says what you are doing, and the second way is an obfuscation of what you are doing. > > # 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 cpu_index_tuple_cost = 0.02; > set cpu_operator_cost = 0.01; > If you really want to target the plan with the BitmapAnd, you should increase cpu_index_tuple_cost and/or cpu_operator_cost but not increase cpu_tuple_cost. That is because the unselective bitmap index scan does not incur any cpu_tuple_cost, but does incur index_tuple and operator costs. Unfortunately all other index scans in the system will also be skewed by such a change if you make the change system-wide. Incidentally, the "actual rows" field of BitmapAnd is always zero. That field is not implemented for that node type. Why do you have an index on flag in the first place? What does the index accomplish, other than enticing the planner into bad plans? I don't know how this translates back into your real query, but dropping that index should be considered. Or replace both indexes with one on (num,flag). Or you can re-write the part of the WHERE clause in a way that it can't use an index, something like: and flag::text ='t' Cheers, Jeff
Re: Bitmap scan is undercosted?
Jeff Janes writes: > On Fri, Dec 1, 2017 at 11:08 PM, Vitaliy Garnashevich < > [email protected]> 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 cpu_index_tuple_cost = 0.02; >> set cpu_operator_cost = 0.01; > If you really want to target the plan with the BitmapAnd, you should > increase cpu_index_tuple_cost and/or cpu_operator_cost but not increase > cpu_tuple_cost. That is because the unselective bitmap index scan does > not incur any cpu_tuple_cost, but does incur index_tuple and operator > costs. Unfortunately all other index scans in the system will also be > skewed by such a change if you make the change system-wide. I think it'd be a serious error to screw around with your cost settings on the basis of a single case in which the rowcount estimates are so far off. It's really those estimates that are the problem AFAICS. The core issue in this example is that, the way the test data is set up, the "flag = true" condition actually adds no selectivity at all, because every row with "num = 1" is certain to have "flag = true". If the planner realized that, it would certainly not bother with BitmapAnd'ing the flag index onto the results of the num index. But it doesn't know that those columns are correlated, so it supposes that adding the extra index will give a 10x reduction in the number of heap rows that have to be visited (since it knows that only 1/10th of the rows have "flag = true"). *That* is what causes the overly optimistic cost estimate for the two-index bitmapscan, and no amount of fiddling with the cost parameters will make that better. 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 statistics are supposed to fix exactly this type of problem. I suspect there may be something in the extended-stats code that causes it not to work right for boolean columns --- this wouldn't be excessively surprising because of the way the planner messes around with converting "flag = true" to just "flag" and sometimes back again. But I've not looked closer yet. regards, tom lane
Re: Bitmap scan is undercosted?
On Sat, Dec 2, 2017 at 3:44 PM, Tom Lane wrote: > Jeff Janes writes: > > On Fri, Dec 1, 2017 at 11:08 PM, Vitaliy Garnashevich < > > [email protected]> 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 cpu_index_tuple_cost = 0.02; > >> set cpu_operator_cost = 0.01; > > > If you really want to target the plan with the BitmapAnd, you should > > increase cpu_index_tuple_cost and/or cpu_operator_cost but not increase > > cpu_tuple_cost. That is because the unselective bitmap index scan does > > not incur any cpu_tuple_cost, but does incur index_tuple and operator > > costs. Unfortunately all other index scans in the system will also be > > skewed by such a change if you make the change system-wide. > > I think it'd be a serious error to screw around with your cost settings > on the basis of a single case in which the rowcount estimates are so > far off. It's really those estimates that are the problem AFAICS. > > The core issue in this example is that, the way the test data is set up, > the "flag = true" condition actually adds no selectivity at all, because > every row with "num = 1" is certain to have "flag = true". If the planner > realized that, it would certainly not bother with BitmapAnd'ing the flag > index onto the results of the num index. But it doesn't know that those > columns are correlated, so it supposes that adding the extra index will > give a 10x reduction in the number of heap rows that have to be visited > (since it knows that only 1/10th of the rows have "flag = true"). > *That* is what causes the overly optimistic cost estimate for the > two-index bitmapscan, and no amount of fiddling with the cost parameters > will make that better. > But he also tested with num=2 and num=39, which reverses the situation so the bitmap is 100% selective rather than the 90% the planner thinks it will be. But it is still slower for him (I am having trouble replicating that exact behavior), so building the bitmap to rule out 100% of the rows is empirically not worth it, I don't see how building it to rule out 90%, as the planner things, would be any better. > 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 statistics are supposed to fix exactly this type of problem. > I suspect there may be something in the extended-stats code that causes it > not to work right for boolean columns --- this wouldn't be excessively > surprising because of the way the planner messes around with converting > "flag = true" to just "flag" and sometimes back again. But I've not > looked closer yet. > 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. 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. Cheers, Jeff
Re: Bitmap scan is undercosted? - boolean correlation
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 due to the boolean though; you see the same thing if you do:
CREATE INDEX aaa_f ON aaa((flag::text));
ANALYZE aaa;
correlation | 0.81193
or:
ALTER TABLE aaa ADD flag2 int; UPDATE aaa SET flag2= flag::int
correlation | 0.81193
I think it's caused by having so few (2) values to correlate.
most_common_vals | {f,t}
most_common_freqs | {0.9014,0.0986}
correlation| 0.822792
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 value) are the same value ('t'), and the CTIDs are in order on
the new index, so 90% of the values are 100% correlated.
It improves (by which I mean here that it spits out a lower number) if it's not
a 90/10 split:
CREATE TABLE aaa5 AS SELECT (id%100)::int num, (id%10>5)::bool flag FROM
generate_series(1, 1000) id;
CREATE INDEX ON aaa5 (flag);
tablename | aaa5
attname | flag
correlation | 0.522184
Justin
