Re: Indexes not always used after inserts/updates/vacuum

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