Обсуждение: Strange choice of general index over partial index
This is an obfuscation and mock up, but:
table files (
id serial pk,
filename text not null,
state varchar(20) not null
... 18 more columns
)
index file_state on (state)
(35GB in size)
index file_in_flight_state (state) where state in (
'waiting','assigning', 'processing' )
(600MB in size)
... 10 more indexes
More important facts:
* state = 'done' 95% of the time. thereform the partial index
represents only 5% of the table
* all indexes and the table are very bloated
* server has 128GB RAM
* Version 9.2.
Given this setup, I would expect the planner to *always* choose
file_in_flight_state over file_state for this query:
SELECT id, filename FROM files WHERE state = 'waiting';
... and yet it keeps selecting file_state based on extremely small
changes to the stats. This is important because the same query, using
file_state, is 20X to 50X slower, because that index frequently gets
pushed out of memory.
What am I missing? Or is this potentially a planner bug for costing?
--
Josh Berkus
PostgreSQL Experts Inc.
http://pgexperts.com
On Thu, Jan 15, 2015 at 2:30 PM, Josh Berkus <josh@agliodbs.com> wrote:
This is an obfuscation and mock up, but:
table files (
id serial pk,
filename text not null,
state varchar(20) not null
... 18 more columns
)
index file_state on (state)
(35GB in size)
index file_in_flight_state (state) where state in (
'waiting','assigning', 'processing' )
(600MB in size)
... 10 more indexes
More important facts:
* state = 'done' 95% of the time. thereform the partial index
represents only 5% of the table
* all indexes and the table are very bloated
* server has 128GB RAM
* Version 9.2.
Given this setup, I would expect the planner to *always* choose
file_in_flight_state over file_state for this query:
SELECT id, filename FROM files WHERE state = 'waiting';
... and yet it keeps selecting file_state based on extremely small
changes to the stats. This is important because the same query, using
file_state, is 20X to 50X slower, because that index frequently gets
pushed out of memory.
What am I missing? Or is this potentially a planner bug for costing?
I wonder if this could be related to 3e9960e9d935e7e7c12e78441, which first appeared in 9.2.3.
But I don't know why the small index *should* be better. If this query is frequent, it should have no problem keeping just those leaf pages that contain the 'waiting' rows out of the full index in memory, without having to keep the 'done' leaf pages around. And if it is not frequent, then it would have just as much problem keeping the smaller index in memory as it would a small portion of the large index.
Of course if it randomly switches back and forth, now you have to keep twice as much data in memory, the relevant parts of both indexes.
What is the point of having the full index at all, in this case?
Cheers,
Jeff
On 16/01/15 11:30, Josh Berkus wrote: > This is an obfuscation and mock up, but: > > table files ( > id serial pk, > filename text not null, > state varchar(20) not null > ... 18 more columns > ) > > index file_state on (state) > (35GB in size) > index file_in_flight_state (state) where state in ( > 'waiting','assigning', 'processing' ) > (600MB in size) > ... 10 more indexes > > More important facts: > * state = 'done' 95% of the time. thereform the partial index > represents only 5% of the table > * all indexes and the table are very bloated > * server has 128GB RAM > * Version 9.2. > > Given this setup, I would expect the planner to *always* choose > file_in_flight_state over file_state for this query: > > SELECT id, filename FROM files WHERE state = 'waiting'; > > ... and yet it keeps selecting file_state based on extremely small > changes to the stats. This is important because the same query, using > file_state, is 20X to 50X slower, because that index frequently gets > pushed out of memory. > > What am I missing? Or is this potentially a planner bug for costing? > Are you seeing a bitmapscan access plan? If so see if disabling it gets you a plan on the files_in_flight index. I'm seeing this scenario with a fake/generated dataset a bit like yours in 9.2 (9.5 uses the files_in_flight w/o any coercing). regards Mark
Josh Berkus <josh@agliodbs.com> writes:
> index file_state on (state)
> (35GB in size)
> index file_in_flight_state (state) where state in (
> 'waiting','assigning', 'processing' )
> (600MB in size)
> ... 10 more indexes
> More important facts:
> * state = 'done' 95% of the time. thereform the partial index
> represents only 5% of the table
> * all indexes and the table are very bloated
> * server has 128GB RAM
> * Version 9.2.
9.2.what? And how much of the table is 'waiting' state?
> What am I missing? Or is this potentially a planner bug for costing?
The only real difference between the two cases is index descent costs:
the number of heap pages visited will be the same whichever index is
used, and the number of index leaf pages visited is probably about the
same too. 9.3 is the first release that makes any real attempt to
model index descent costs realistically. Before that there were some
dubious fudge factors, which we're unlikely to change in long-stable
branches no matter how badly the results might suck in specific instances.
Having said that, though, I'd have thought that the old fudge factors
would strongly prefer the smaller index given such a large difference in
index size. Have you neglected to mention some nondefault planner cost
settings?
regards, tom lane
On 16/01/15 13:37, Mark Kirkwood wrote:
> On 16/01/15 11:30, Josh Berkus wrote:
>> This is an obfuscation and mock up, but:
>>
>> table files (
>> id serial pk,
>> filename text not null,
>> state varchar(20) not null
>> ... 18 more columns
>> )
>>
>> index file_state on (state)
>> (35GB in size)
>> index file_in_flight_state (state) where state in (
>> 'waiting','assigning', 'processing' )
>> (600MB in size)
>> ... 10 more indexes
>>
>> More important facts:
>> * state = 'done' 95% of the time. thereform the partial index
>> represents only 5% of the table
>> * all indexes and the table are very bloated
>> * server has 128GB RAM
>> * Version 9.2.
>>
>> Given this setup, I would expect the planner to *always* choose
>> file_in_flight_state over file_state for this query:
>>
>> SELECT id, filename FROM files WHERE state = 'waiting';
>>
>> ... and yet it keeps selecting file_state based on extremely small
>> changes to the stats. This is important because the same query, using
>> file_state, is 20X to 50X slower, because that index frequently gets
>> pushed out of memory.
>>
>> What am I missing? Or is this potentially a planner bug for costing?
>>
>
> Are you seeing a bitmapscan access plan? If so see if disabling it gets
> you a plan on the files_in_flight index. I'm seeing this scenario with a
> fake/generated dataset a bit like yours in 9.2 (9.5 uses the
> files_in_flight w/o any coercing).
>
FWIW: For me 9.2 and 9.3 (default config) generate plans like:
state=# EXPLAIN ANALYZE
SELECT id, filename
FROM files
WHERE state = 'processing';
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------
Bitmap Heap Scan on files (cost=3102.02..89228.68 rows=164333
width=15) (actual time=26.629..803.507 rows=166696 loops=1)
Recheck Cond: ((state)::text = 'processing'::text)
Rows Removed by Index Recheck: 7714304
-> Bitmap Index Scan on file_state (cost=0.00..3060.93 rows=164333
width=0) (actual time=25.682..25.682 rows=166696 loops=1)
Index Cond: ((state)::text = 'processing'::text)
Total runtime: 808.662 ms
(6 rows)
whereas 9.4 and 9.5 get:
QUERY
PLAN
---------------------------------------------------------------------------------------------------------------------------------------
Index Scan using file_in_flight on files (cost=0.42..62857.39
rows=158330 width=15) (actual time=0.055..202.732 rows=166696 loops=1)
Index Cond: ((state)::text = 'processing'::text)
Planning time: 24.203 ms
Execution time: 208.926 ms
(4 rows)
This is with each version loading exactly the same dataset (generated by
the attached scripty). Obviously this is a vast simplification of what
Josh is looking at - but it is (hopefully) interesting that these later
versions are doing so much better...
Cheers
Mark
Вложения
Mark Kirkwood <mark.kirkwood@catalyst.net.nz> writes:
> This is with each version loading exactly the same dataset (generated by
> the attached scripty). Obviously this is a vast simplification of what
> Josh is looking at - but it is (hopefully) interesting that these later
> versions are doing so much better...
Actually, what I see when using this dataset is that both the estimated
cost and the actual runtime of the query are within a percent or so of
being the same when using either index. (Try forcing it to use the
non-preferred index by dropping the preferred one, and you'll see what
I mean.) The absolute magnitude of the cost estimate varies across
versions, but not the fact that we're getting about the same estimate
for both indexes.
I suspect the same may be true for Josh's real-world database, meaning
that the index choice is depending on phase-of-the-moon factors like
which index has the lower OID, which is doubtless contributing to
his frustration :-(
I think that the real key to this problem lies in the index bloat pattern,
which might be quite a bit different between the two indexes. This might
mean traversing many more index leaf pages in one case than the other,
which would account for the difference in real runtimes that he's seeing
and I'm not. I don't recall at the moment whether 9.2's cost estimation
rules would do a good job of accounting for such effects. (And even if
it's trying, it'd be working from an average-case estimate, which might
not have much to do with reality for this specific query.)
regards, tom lane
On 16/01/15 15:32, Mark Kirkwood wrote: > On 16/01/15 13:37, Mark Kirkwood wrote: >> On 16/01/15 11:30, Josh Berkus wrote: >>> This is an obfuscation and mock up, but: >>> >>> table files ( >>> id serial pk, >>> filename text not null, >>> state varchar(20) not null >>> ... 18 more columns >>> ) >>> >>> index file_state on (state) >>> (35GB in size) >>> index file_in_flight_state (state) where state in ( >>> 'waiting','assigning', 'processing' ) >>> (600MB in size) >>> ... 10 more indexes >>> >>> More important facts: >>> * state = 'done' 95% of the time. thereform the partial index >>> represents only 5% of the table >>> * all indexes and the table are very bloated >>> * server has 128GB RAM >>> * Version 9.2. >>> >>> Given this setup, I would expect the planner to *always* choose >>> file_in_flight_state over file_state for this query: >>> >>> SELECT id, filename FROM files WHERE state = 'waiting'; >>> >>> ... and yet it keeps selecting file_state based on extremely small >>> changes to the stats. This is important because the same query, using >>> file_state, is 20X to 50X slower, because that index frequently gets >>> pushed out of memory. >>> >>> What am I missing? Or is this potentially a planner bug for costing? >>> >> >> Are you seeing a bitmapscan access plan? If so see if disabling it gets >> you a plan on the files_in_flight index. I'm seeing this scenario with a >> fake/generated dataset a bit like yours in 9.2 (9.5 uses the >> files_in_flight w/o any coercing). >> > > FWIW: For me 9.2 and 9.3 (default config) generate plans like: > state=# EXPLAIN ANALYZE > SELECT id, filename > FROM files > WHERE state = 'processing'; > QUERY PLAN > ---------------------------------------------------------------------------------------------------------------------------------- > > Bitmap Heap Scan on files (cost=3102.02..89228.68 rows=164333 > width=15) (actual time=26.629..803.507 rows=166696 loops=1) > Recheck Cond: ((state)::text = 'processing'::text) > Rows Removed by Index Recheck: 7714304 > -> Bitmap Index Scan on file_state (cost=0.00..3060.93 rows=164333 > width=0) (actual time=25.682..25.682 rows=166696 loops=1) > Index Cond: ((state)::text = 'processing'::text) > Total runtime: 808.662 ms > (6 rows) > > > whereas 9.4 and 9.5 get: > > QUERY PLAN > > --------------------------------------------------------------------------------------------------------------------------------------- > > Index Scan using file_in_flight on files (cost=0.42..62857.39 > rows=158330 width=15) (actual time=0.055..202.732 rows=166696 loops=1) > Index Cond: ((state)::text = 'processing'::text) > Planning time: 24.203 ms > Execution time: 208.926 ms > (4 rows) > > > This is with each version loading exactly the same dataset (generated by > the attached scripty). Obviously this is a vast simplification of what > Josh is looking at - but it is (hopefully) interesting that these later > versions are doing so much better... > A bit more poking about shows that the major factor (which this fake dataset anyway) is the default for effective_cache_size (changes from 128MB to 4GB in 9.4). Increasing this makes 9.2 start using the files_in_flight index in a plain index scan too. Josh - might be worth experimenting with this parameter. regards Mark
On 16/01/15 16:06, Mark Kirkwood wrote: > A bit more poking about shows that the major factor (which this fake > dataset anyway) is the default for effective_cache_size (changes from > 128MB to 4GB in 9.4). Increasing this makes 9.2 start using the > files_in_flight index in a plain index scan too. > Arrg - misread the planner output....in 9.2 what changes is a plan that uses an index scan on the *file_state* index (not files_in_flight)...which appears much faster than the bitmap scan on file_state. Apologies for the confusion. I'm thinking that I'm seeing the effect Tom has just mentioned. regards Mark
On 01/16/2015 04:17 PM, Mark Kirkwood wrote: > On 16/01/15 16:06, Mark Kirkwood wrote: > >> A bit more poking about shows that the major factor (which this fake >> dataset anyway) is the default for effective_cache_size (changes from >> 128MB to 4GB in 9.4). Increasing this makes 9.2 start using the >> files_in_flight index in a plain index scan too. >> > > Arrg - misread the planner output....in 9.2 what changes is a plan that > uses an index scan on the *file_state* index (not > files_in_flight)...which appears much faster than the bitmap scan on > file_state. Apologies for the confusion. > > I'm thinking that I'm seeing the effect Tom has just mentioned. It's not using a bitmapscan in either case; it's a straight indexscan. -- Josh Berkus PostgreSQL Experts Inc. http://pgexperts.com
On 16/01/15 16:28, Josh Berkus wrote: > On 01/16/2015 04:17 PM, Mark Kirkwood wrote: >> On 16/01/15 16:06, Mark Kirkwood wrote: >> >>> A bit more poking about shows that the major factor (which this fake >>> dataset anyway) is the default for effective_cache_size (changes from >>> 128MB to 4GB in 9.4). Increasing this makes 9.2 start using the >>> files_in_flight index in a plain index scan too. >>> >> >> Arrg - misread the planner output....in 9.2 what changes is a plan that >> uses an index scan on the *file_state* index (not >> files_in_flight)...which appears much faster than the bitmap scan on >> file_state. Apologies for the confusion. >> >> I'm thinking that I'm seeing the effect Tom has just mentioned. > > It's not using a bitmapscan in either case; it's a straight indexscan. > > Right, I suspect that bloating is possibly the significant factor then - can you REINDEX? Cheers Mark
> Right, I suspect that bloating is possibly the significant factor then - > can you REINDEX? Believe me, it's on the agenda. Of course, this is on a server with 90% saturated IO, so doing a repack is going to take some finessing. BTW, effective_cache_size is set to 100GB. So I suspect that it's the other issue with Tom mentioned, which is that 9.2 really doesn't take physical index size into account. -- Josh Berkus PostgreSQL Experts Inc. http://pgexperts.com