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
with the slower index i2, too.
*Bitmap Heap Scan* on public.aaa (cost=12600.90..*23688**.70* rows=9488
width=5) (actual time=107.529..*115.902* rows=9976 loops=1)
-> BitmapAnd (cost=12600.90..12600.90 rows=9488 width=0) (actual
time=105.133..105.133 rows=0 loops=1)
-> Bitmap Index Scan on i1 (cost=0.00..1116.43 rows=96000
width=0) (actual time=16.313..16.313 rows=100508 loops=1)
-> Bitmap Index Scan on i2 (cost=0.00..11479.47 rows=988338
width=0) (actual time=77.950..77.950 rows=1000200 loops=1)
*Index Scan* using i2 on public.aaa (cost=0.44..*48227.31* rows=9488
width=5) (actual time=0.020..*285.695* rows=9976 loops=1)
*Seq Scan* on public.aaa (cost=0.00..*169248.54* rows=9488 width=5)
(actual time=0.024..*966.469* rows=9976 loops=1)
This way the estimates and the actual time get more sense. But then
there's the question - maybe it's i1 runs too fast, and is estimated
incorrectly? Why that happens?
Here are the complete plans with the two different kinds of index scans
once again:
Index Scan using i1 on public.aaa (cost=0.44..66621.56 rows=10340
width=5) (actual time=0.027..47.075 rows=9944 loops=1)
Output: num, flag
Index Cond: (aaa.num = 1)
Filter: aaa.flag
Rows Removed by Filter: 89687
Buffers: shared hit=39949
Planning time: 0.104 ms
Execution time: 47.351 ms
Index Scan using i2 on public.aaa (cost=0.44..48227.31 rows=9488
width=5) (actual time=0.020..285.695 rows=9976 loops=1)
Output: num, flag
Index Cond: (aaa.flag = true)
Filter: (aaa.flag AND (aaa.num = 1))
Rows Removed by Filter: 990224
Buffers: shared hit=46984
Planning time: 0.098 ms
Execution time: 286.081 ms
// The test DB was populated with: create table aaa as select
floor(random()*100)::int num, (random()*10 < 1)::bool flag from
generate_series(1, 10000000) id;
Regards,
Vitaliy