Re: best way to fetch next/prev record based on index

Markus wrote:
> > The basic problem is the planner can't always match the query to the
> > index. So, either the planner has to be helped/fixed or I have to
> > explore another solution. This seems to happen most when the 'a'
> > column has very poor selectivity. In this case, the planner will
only
> > examine the 'a' part of the key.
>
> So it may help to add some more indices so you have an index for all
> permutations,
>
> Create an index on (a,b,c), (a,c,b), (b,c,a), (b,a,c), (c,a,b) and
> (c,b,a).

It is mathematically impossible for any index except for (a,b,c) to
work. Although, in theory, (a,b) could be used...but that wouldn't
help. In any event, creating 1000s and 1000s of extra indices is not an
option.

Here is some log snippets illustrating my problem:

Here is a log snippet illustrating things when everything is working ok
(notice the sub-millisecond times):

prepare data3_read_next_menu_item_recent_file_0 (character varying,
numeric, numeric, numeric)
as select xmin, xmax, *
from data3.menu_item_recent_file
where mir_user_id >= $1 and
(mir_user_id > $1 or mir_menu_item_id >= $2) and
(mir_user_id > $1 or mir_menu_item_id > $2 or
mir_sequence_no > $3)
order by mir_user_id, mir_menu_item_id, mir_sequence_no
limit $4 0.000849704 sec
data3_read_next_menu_item_recent_file_0 0.000435999 sec params:
$1=MERLIN $2=00057 $3=00000001 $4=1
data3_read_next_menu_item_recent_file_0 0.0117151 sec params: $1=MERLIN
$2=00058 $3=00000002 $4=2
data3_read_next_menu_item_recent_file_0 0.0385374 sec params: $1=MERLIN
$2=00203 $3=00000005 $4=3
data3_read_next_menu_item_recent_file_0 0.0211677 sec params: $1=MERLIN
$2=00449 $3=00000010 $4=4
data3_read_next_menu_item_recent_file_0 0.000818999 sec params:
$1=MERLIN $2=00813 $3=00000008 $4=5

Here is a log snippet when there is a problem:
data3_start_nl_line_file_0 37.2677 sec params: $1= $2=008768 $3=003 $4=1

prepare data3_read_next_line_file_0 (character, numeric, numeric,
numeric)
as select xmin, xmax, *
from data3.line_file
where li_quote_flag >= $1 and
(li_quote_flag > $1 or li_order_no >= $2) and
(li_quote_flag > $1 or li_order_no > $2 or li_seq_no
> $3)
order by li_quote_flag, li_order_no, li_seq_no
limit $4 0.000839501 sec
data3_read_next_line_file_0 0.313869 sec params: $1= $2=008768 $3=005
$4=1
data3_read_next_line_file_0 0.343179 sec params: $1= $2=008768 $3=006
$4=2
data3_read_next_line_file_0 0.308703 sec params: $1= $2=008768 $3=008
$4=3
data3_read_next_line_file_0 0.306802 sec params: $1= $2=008768 $3=011
$4=4
data3_read_next_line_file_0 0.311033 sec params: $1= $2=008768 $3=015
$4=5

in the above statements, .3 sec to return a single row is very poor.
Explain only matches li_quote_flag to the index which offers very poor
selectivity. li_quote_flag is a char(1) and there is an index on
line_file on the three where columns in the proper order.

Merlin