Re: Index on immutable function call

On Tue, Jan 19, 2010 at 11:57 AM, Alban Hertroys
> Strange. I noticed that the number of records you get from each method differs somewhat, are you recreating the
databaseeach time? 
>
> With the combined index, or just an index on each column; if you disable seqscans (set enable_seqscan to false), at
whatcost does the planner estimate the bitmap index scan that I expect you'll get in that case? Can you show us the
outputof explain for that case? 
>
> I don't get why it'd be estimated so much more expensive than the partial index Tore came up with that it would
prefera seqscan. Tore's index would create a better balanced tree as serial is guaranteed to be unique, while data1 and
data2aren't (collisions). 
>
> It's all probably an artefact of the randomness of your data - many of the statistics the planner tracks are quite
uselesshere. Real data tends to be a lot less random so estimates are usually much better there. 

I'm not sure, but I think a comparison of the index-termes may be hard
to use as a lookup. I can't quite formulate why, though...

A slightly more generic solution may be somthing like...

CREATE OR REPLACE FUNCTION compare(a numeric, b numeric)
  RETURNS smallint AS // or create an enum to return
$BODY$
select case $1 = $2 when true then 0::smallint else case $1 < $2 when
true then -1::smallint else 1::smallint end end
$BODY$
  LANGUAGE 'sql' IMMUTABLE STRICT;

CREATE INDEX compare_index
  ON indexed_table(compare(data1, this_is_a_long_transformation(data2)));

SELECT * FROM indexed_table where
compare(data1, this_is_a_long_transformation(data2)) in (0, 1); // for
greater-equal

--
Eld på åren og sol på eng gjer mannen fegen og fjåg. [Jøtul]
<demo> 2010 Tore Halvorsen || +052 0553034554