Re: Is this planner choice easily explained?

Philip Warner <pjw(at)rhyme(dot)com(dot)au> writes:
> Strange you should ask; it is actually taking some effort to persuade them
> it's a good idea. They also don't believe that a LIMIT clause affects
> strategy choice, so it's an uphill battle.

If they don't believe that, they are wrong (and pretty muleheaded, to
continue disbelieving it in the face of indisputable evidence to the
contrary...). The entire reason that the planner estimates both startup
and total cost is so that it can make an informed choice about what to do
in the presence of LIMIT.

> Am I correct in my interpretation that:

> explain SELECT messageblk FROM messageblks WHERE message_idnr =
> 100::bigint
> ORDER BY messageblk_idnr ;

> Sort (cost=5793.33..5793.33 rows=1453 width=40)
> -> Index Scan using messageblks_msg_idx on messageblks
> (cost=0.00..5716.99 rows=1453 width=40)

> means it expects to get 1453 rows based on a search for a specific key
> (hence why it has a high cost)?

It looks that way. It's always tricky to be sure what the index
condition actually is (which is why 7.3's EXPLAIN displays conditions
explicitly) --- but I'd interpret the above as an indexed search for
message_idnr = 100 (estimated to yield 1453 rows) followed by a sort
on messageblk_idnr.

> Based on other tests I have done, I have
> concluded that it assumes a selectivity of 0.5% for non-unique indexes - is
> that right?

Yeah, I think that's the default selectivity in the absence of pg_stats
data, at least in recent releases. Look in src/backend/utils/adt/selfuncs.c.

> Whereas with:

> explain SELECT messageblk FROM messageblks WHERE message_idnr =
> 100::bigint
> ORDER BY messageblk_idnr
> limit 1;

> Limit (cost=0.00..777.50 rows=1 width=40)
> -> Index Scan using messageblks_id_idx on messageblks
> (cost=0.00..1129984.15 rows=1453 width=40)

> it looks like the high cost on the last line here is based on the number of
> pages/tuples in the file, and that the limit is causing 1/1453th of the
> cost to be applied. It looks like it gets the 1453 as a basic default
> selectivity again.

What is presumably happening here is that the index is being used only
to retrieve rows in messageblk_idnr order. There is no index condition
per se (ie, the whole index is potentially scanned) --- but there is a
filter condition ("qpqual") for message_idnr = 100 on the indexscan's
output, so the indexscan node is still estimated to yield the same 1453
rows. But notice the very high cost to run the indexscan to completion;
under the hood it'd be visiting all the rows.

The reason this plan is chosen is that because of the LIMIT 1, it's not
going to be run to completion. Essentially, the system is betting that
scanning the messageblk_idnr index in order till it hits the first row
with message_idnr = 100 will be quicker than finding and sorting all the
rows with message_idnr = 100 to get the one with lowest messageblk_idnr.

This bet is evidently wrong, but it's hard to tell whether it's wrong
because no statistics are available, or because the system isn't making
the right deductions from the stats it has, or because the stats aren't
adequate to model the situation. (For example, we currently do not have
any cross-column correlation stats. If message_idnr and messageblk_idnr
are strongly correlated, which I'm suspecting is likely, the rows with
message_idnr = 100 would not be randomly scattered in the
messageblk_idnr index --- but the system is assuming they will be in
order to estimate how long it will take to find the first one.)

regards, tom lane