Re: Optimizer confusion? - Mailing list pgsql-hackers

Philip Warner <pjw@rhyme.com.au> writes:
>> Indexscan estimates are supposed to be nonlinear, actually, to account
>> for the effects of caching.  I doubt the shapes of the curves are right
>> in detail, but I haven't had time to do any research about it.

> As to the non-linearity, I would have thought the majority of I/Os by far
> would be reading rows, and with retrieval by index, you may not get much
> buffering benefit on table pages. For a large table, ISTM linear estimates
> would be a good estimate.

The estimate is linear, for number-of-tuples-to-fetch << number-of-pages-
in-table. See src/backend/optimizer/path/costsize.c for the gory details.

>> You could push random_page_cost and effective_cache_size around to try
>> to match your platform better.  Let me know if that helps...

> Setting it to 0.1 works (it was 4).

Unfortunately, random_page_cost < 1 is ridiculous on its face ... but
that's not where the problem is anyway, as your next comment makes clear.

> But this (I think) just highlights the
> fact that the index is sorted by date, and the rows were added in date
> order. As a result (for this table, in this query), the index scan get's a
> much better cache-hit rate, so the actual IO cost is low.

> Does that sound reasonable?

Quite.  The cost estimates are based on the assumption that the tuples
visited by an indexscan are scattered randomly throughout the table.
Obviously, if that's wrong then the estimates will be way too high.

> Does the optimizer know if I have used clustering?

Nope.  To quote from the code:

     * XXX if the relation has recently been "clustered" using this index,
     * then in fact the target tuples will be highly nonuniformly
     * distributed, and we will be seriously overestimating the scan cost!
     * Currently we have no way to know whether the relation has been
     * clustered, nor how much it's been modified since the last
     * clustering, so we ignore this effect.  Would be nice to do better
     * someday.

The killer implementation problem here is keeping track of how much the
table ordering has been altered since the last CLUSTER command.  We have
talked about using an assumption of "once clustered, always clustered",
ie, ignore the issue of sort order degrading over time.  That's pretty
ugly but it might still be more serviceable than the current state of
ignorance.  For a table like this one, where rows are added in date
order and (I imagine) seldom updated, the sort order isn't going to
degrade anyway.  For other tables, you could assume that you're going
to run CLUSTER on a periodic maintenance basis to keep the sort order
fairly good.

I have not yet done anything about this, mainly because I'm unwilling to
encourage people to use CLUSTER, since it's so far from being ready for
prime time (see TODO list).  Once we've done something about table
versioning, we can rewrite CLUSTER so that it's actually reasonable to
use on a regular basis, and at that point it'd make sense to make the
optimizer CLUSTER-aware.

> I don't suppose I can get the backend to tell me how many logical IOs and
> how much CPU it used?

Yes you can.  Run psql with
    PGOPTIONS="-s"
and look in the postmaster log.  There's also -tparse, -tplan,
-texec if you'd rather see the query time broken down by stages.

            regards, tom lane