On Mon, Mar 2, 2009 at 8:29 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Merlin Moncure <mmoncure@gmail.com> writes:
>> It looks like for row-wise comparison, only the first column is used
>> for generating the expected row count.
>
> [ shrug... ] Short of multi-column statistics, it's hard to see how to
> do better.
hm... Why can't you just multiply the range estimates for the fields
together when doing an operation over the key?
For example, in this case if the planner estimates 10% of rows for
key, and 5% of matches for ts, just multiply .1 & .05 and get .005
when you fall into the row operation case. This would give a
reasonably accurate answer...formally correct, even. All the
information is there, or am I missing something (not knowing all the
inner workings of the planner, I certainly might be)?
IOW, I don't see this as a 'not enough statistics', more of a 'looking
at the statistics wrong for multi-column index range operation'
problem. Equality works correctly, as it always has. This is a kind
of a stats loophole introduced when we got the ability to correctly do
these types of operations in 8.2.
There's no workaround that I see to this problem short of disabling seq_scan.
The classic form of this query when looking for only one 'key' my problem case):
select * from range where key = x and ts between a and b;
usually gives a plain index scan, which can be really undesirable.
merlin
