On Mon, Mar 2, 2009 at 4:43 PM, Merlin Moncure <mmoncure@gmail.com> wrote:
> It looks like for row-wise comparison, only the first column is used
> for generating the expected row count. This can lead to bad plans in
> some cases.
>
> Test case (takes seconds to minutes hardware depending):
>
> create table range as select v as id, v % 500 as key, now() +
> ((random() * 1000) || 'days')::interval as ts from
> generate_series(1,10000000) v;
>
> create index range_idx on range(key, ts);
>
> explain analyze select * from range where (key, ts) >= (222, '7/11/2009') and
> (key, ts) <= (222, '7/12/2009')
> order by key, ts;
>
> result (cut down a bit)
> Sort (cost=469723.46..475876.12 rows=2461061 width=16) (actual
> time=0.054..0.056 rows=13 loops=1)
> Sort Key: key, ts
> Sort Method: quicksort Memory: 25kB
>
> note the row count expected vs. got. Varying the ts parameters
> changes the expected rows, but varying the key does not. Note for the
oop, thats backwards. rows expected depends on key (the first column
in the index) only.
merlin