Re: Ok, why isn't it using *this* index?

Paul Tomblin <ptomblin(at)xcski(dot)com> writes:
> waypoint=> explain select * from waypoint where country = 'CANADA';
> NOTICE: QUERY PLAN:

> Seq Scan on waypoint (cost=455.13 rows=6813 width=130)

I take it the majority of rows have country = 'CANADA'? How many rows
in the table all together, anyway? Presumably you're seeing the results
of an estimate that this WHERE clause is too unselective for an index
scan to be profitable. But I can't tell if the rows estimate is any
good or not.

A rule of thumb is that an indexscan will only be used if the index
clauses select no more than a few percent of the rows in the table.
Otherwise the additional I/O to scan the index and to read the table
in nonsequential fashion costs more than a sequential scan does.

> Also, can anybody explain why the "rows=" doesn't correspond to anything
> logical? For instance, in the first one it says "rows=84" even though
> there are 107 matching records, and 71 different states.

ROTFL ... given the thinness of the statistics used to make the
estimate, I'd call rows=84 practically dead on, if the true value
is 107. The system is doing real good here. You can read in the
archives about lots of cases where the estimate is off by a factor
of ten or worse, leading to bad plan choices. This estimate is
plenty close enough to arrive at a reasonable plan.

regards, tom lane