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 database each time? > > With the combined index, or just an index on each column; if you disable > seqscans (set enable_seqscan to false), at what cost does the planner > estimate the bitmap index scan that I expect you'll get in that case? Can you > show us the output of 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 prefer a seqscan. Tore's index would > create a better balanced tree as serial is guaranteed to be unique, while > data1 and data2 aren't (collisions). > > It's all probably an artefact of the randomness of your data - many of the > statistics the planner tracks are quite useless here. 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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
