Thread: Re: Indexes not always used after inserts/updates/vacuum
On Thu, 28 Feb 2002 at 09:51, Tom Lane wrote: > Reinhard Max <max@suse.de> writes: > > I've just found a case where forcing indexscans results in much higher > > speed. > > > -> Index Scan using foo_pkey on foo > > (cost=0.00..25153.18 rows=352072 width=4) > > (actual time=0.03..157.57 rows=38432 loops=1) > > The major estimation error is evidently in this indexscan. What > statistics does pg_stats show for this table? See attached file. BTW, I've just done the same test on PostgreSQL 7.1 and got similar results. cu Reinhard
Reinhard Max <max@suse.de> writes: >> The major estimation error is evidently in this indexscan. What >> statistics does pg_stats show for this table? > See attached file. Okay. It looks like foo.id has a pretty strong but not perfect descending order (the correlation statistic is -0.563276). The planner is evidently not rating that effect strongly enough. If you look in cost_index (see approx. lines 270-340 in src/backend/optimizer/path/costsize.c) you'll see that it computes access cost estimates for both the perfectly sequential case and the perfectly uncorrelated case, and then tries to interpolate between them. I have reasonable faith in both of the endpoint estimation methods, but very little in the interpolation equation --- it was chosen on the spur of the moment and hasn't really been tested. It might be interesting to replace csquared with just fabs(indexCorrelation) to see if the results are better. Also, if you cared to step through the code with a debugger or add some printout statements, we could learn what the min and max costs are that it's interpolating between; that'd be interesting to know as well. regards, tom lane
On Thu, 28 Feb 2002 at 10:15, Tom Lane wrote: > Okay. It looks like foo.id has a pretty strong but not perfect > descending order (the correlation statistic is -0.563276). The > planner is evidently not rating that effect strongly enough. Yes, that seems to be the reason. When I try SELECT * into foo2 from foo order by id; CREATE index foo2_id on foo2(id); VACUUM ANALYZE foo2; and repeat the join with foo2 instead of foo, index scans are used even when seqscans are not forbidden. > [...] > It might be interesting to replace csquared with just > fabs(indexCorrelation) to see if the results are better. Also, if you > cared to step through the code with a debugger or add some printout > statements, we could learn what the min and max costs are that it's > interpolating between; that'd be interesting to know as well. OK, this is what I've changed: - csquared = indexCorrelation * indexCorrelation; + elog(NOTICE, "min_IO_cost = %f, max_IO_cost = %f, indexCorrelation = %f", + min_IO_cost, max_IO_cost, indexCorrelation); + csquared = fabs (indexCorrelation); Are these the addtional values you wanted to see? These are the results: max=# EXPLAIN analyze SELECT count(foo.id) FROM foo, bar WHERE foo.id = bar.ref2foo; NOTICE: min_IO_cost = 299.000000, max_IO_cost = 1196.000000 indexCorrelation = -1.000000 NOTICE: min_IO_cost = 1.000000, max_IO_cost = 3.993322 indexCorrelation = -1.000000 NOTICE: min_IO_cost = 5880.000000, max_IO_cost = 1169154.985307 indexCorrelation = -0.532557 NOTICE: min_IO_cost = 1.000000, max_IO_cost = 3.999660 indexCorrelation = -0.532557 NOTICE: QUERY PLAN: Aggregate (cost=18709.65..18709.65 rows=1 width=8) (actual time=7229.15..7229.15 rows=1 loops=1) -> Hash Join (cost=911.39..18613.58 rows=38431 width=8) (actual time=208.23..7184.68 rows=38431 loops=1) -> Seq Scan on foo (cost=0.00..9400.72 rows=352072 width=4) (actual time=0.02..810.92 rows=352072 loops=1) -> Hash (cost=683.31..683.31 rows=38431 width=4) (actual time=149.87..149.87 rows=0 loops=1) -> Seq Scan on bar (cost=0.00..683.31 rows=38431 width=4) (actual time=0.02..83.32 rows=38431 loops=1) Total runtime: 7229.29 msec EXPLAIN max=# EXPLAIN analyze SELECT count(foo2.id) FROM foo2, bar WHERE foo2.id = bar.ref2foo; NOTICE: min_IO_cost = 299.000000, max_IO_cost = 1196.000000 indexCorrelation = -1.000000 NOTICE: min_IO_cost = 1.000000, max_IO_cost = 3.993322 indexCorrelation = -1.000000 NOTICE: min_IO_cost = 5741.000000, max_IO_cost = 1163366.000920 indexCorrelation = 1.000000 NOTICE: min_IO_cost = 1.000000, max_IO_cost = 3.999652 indexCorrelation = 1.000000 NOTICE: QUERY PLAN: Aggregate (cost=12748.26..12748.26 rows=1 width=8) (actual time=687.08..687.08 rows=1 loops=1) -> Merge Join (cost=0.00..12652.18 rows=38431 width=8) (actual time=0.44..633.53 rows=38431 loops=1) -> Index Scan using foo2_pkey on foo2 (cost=0.00..10387.79 rows=352072 width=4) (actual time=0.26..174.32 rows=38432 loops=1) -> Index Scan using idx_bar_ref2foo on bar (cost=0.00..807.74 rows=38431 width=4) (actual time=0.17..180.34 rows=38431 loops=1) Total runtime: 687.31 msec
> Are these the addtional values you wanted to see? Yes, but I just noticed something else strange: > -> Index Scan using foo2_pkey on foo2 > (cost=0.00..10387.79 rows=352072 width=4) > (actual time=0.26..174.32 rows=38432 loops=1) The actual rows read from this indexscan seem to be many fewer than the number of rows in the table. What are the ranges of the id values in tables foo and bar? I'm wondering if the merge could have stopped far short of the end of the foo table; if so, *that* is the effect that we are failing to model accurately. regards, tom lane
Hi, On Thu, 28 Feb 2002 at 16:10, Tom Lane wrote: > > -> Index Scan using foo2_pkey on foo2 > > (cost=0.00..10387.79 rows=352072 width=4) > > (actual time=0.26..174.32 rows=38432 loops=1) > > The actual rows read from this indexscan seem to be many fewer than > the number of rows in the table. What are the ranges of the id > values in tables foo and bar? I'm wondering if the merge could have > stopped far short of the end of the foo table; if so, *that* is the > effect that we are failing to model accurately. Good guess :) max=# SELECT 'bar' AS tablename, min(ref2foo), max(ref2foo), count(ref2foo) FROM bar UNION SELECT 'foo', min(id), max(id), count(id) from foo; tablename | min | max | count -----------+----------+----------+-------- bar | 10000010 | 10049999 | 38431 foo | 10000010 | 10423442 | 352072 (2 rows) I'll tell my colleague (it's his test database, after all) that he should take more realistic test data before complaining about bad performance... cu Reinhard
Reinhard Max <max@suse.de> writes: >> The actual rows read from this indexscan seem to be many fewer than >> the number of rows in the table. What are the ranges of the id >> values in tables foo and bar? I'm wondering if the merge could have >> stopped far short of the end of the foo table; if so, *that* is the >> effect that we are failing to model accurately. > Good guess :) > I'll tell my colleague (it's his test database, after all) that he > should take more realistic test data before complaining about bad > performance... Actually, is it unrealistic test data? After thinking about it awhile, I concluded that this is an effect the planner could and should model. We have statistics that will tell us the maximum values of both variables (at least in common cases), so it's not hard to estimate which input stream will be exhausted first and how much of the other one will actually be read. This could make a big difference in the cost of an indexscan-based merge. I have committed changes for 7.3 that do this. It's probably too big a change to risk back-patching for 7.2.1, but if you care to experiment with CVS tip then you could try it out. regards, tom lane
Hi, On Fri, 1 Mar 2002 at 09:37, Tom Lane wrote: > Reinhard Max <max@suse.de> writes: > > > I'll tell my colleague (it's his test database, after all) that he > > should take more realistic test data before complaining about bad > > performance... > > Actually, is it unrealistic test data? maybe not from the Database's point of view, but certainly from the application's. It is unrealistic insofar as it doesn't match the scenario very good it was meant to be a test case for. I think customer IDs usually appear in a more or less strict ascending order and foreign keys that reference them are likely to be rather equally distributed over the IDs or at least not that much biassed towards one end of the ID range. When I thought about the structure of this test data and experimented with an ascending ordered copy of the address table, I rememered a feature I've once seen in Informix. I think they call it "clustering" or something the like. I don't remember the precise syntax, but it was possible to order a table's rows physically by a given column. Do you think it would be worth the effort to add support for such a thing to the VACUUM command? I could imagine it to improve situations where long tables have to be joined very often. The syntax I have in mind is something like: "VACUUM foo ORDER BY id" or simply "VACUUM foo(id)". Another way would be to enhance the DDL so that the table itself could be told which column(s) to order by and then a "VACUUM ORDER" would physically re-order the tables by that column(s). > I have committed changes for 7.3 that do this. It's probably too > big a change to risk back-patching for 7.2.1, but if you care to > experiment with CVS tip then you could try it out. Hopefully I find some time to have a look at it when SuSE Linux 8.0 is done... Thanks for all your help, Tom. Greetings from Nuremberg, Reinhard