Re: statistics horribly broken for row-wise comparison

On Mon, Mar 2, 2009 at 8:29 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Merlin Moncure <mmoncure(at)gmail(dot)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