From: | Tore Halvorsen <tore(dot)halvorsen(at)gmail(dot)com> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: Index on immutable function call |
Date: | 2010-01-19 11:47:26 |
Message-ID: | 50e97fa71001190347s24f7ffb3nbf02403fc5fe2ef2@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Sam Mason | 2010-01-19 11:54:33 | Re: Index on immutable function call |
Previous Message | Jayadevan M | 2010-01-19 11:16:00 | Re: postgres external table |