Moving GiST index constant to parameter

Started by Thomas Merciecaalmost 9 years ago2 messages
#1Thomas Mercieca
tmercieca@msn.com

Hi all,

The GiST index has a picksplit support procedure (in gistproc.c) with a constant set up using #define - LIMIT_RATIO, set to 0.3. The PostGIS extension which shares similar code for GiST has this set to 0.1 (gserialized_gist_picksplit_2d.c). Compiling new builds, I could see a difference in my indexes with different values.

I would like to introduce it as a parameter so the procedure will behave according to the user's defined value at run-time. In other words, different GiST indexes would have different values of LIMIT_RATIO without requiring a new PostgreSQL/PostGIS build. However, I am not sure what the best approach is.

I have tried introducing a GUC variable (entry in guc.c/config_real) and I have also tried having the parser recognize it (same as fillfactor is recognized), but irrelevant of what I set the value to at run-time, I always get the same unusual index stats (cannot reproduce results) and the following messages in the logfile, suggesting that I broke the function:

"DEBUG: picksplit method for column 1 of index "idx2243" failed
HINT: The index is not optimal. To optimize it, contact a developer, or try to use the column as the second one in the CREATE INDEX command.
STATEMENT: create index idx2243 on geom_table using gist(geom)"

and also

"DEBUG: mapped win32 error code 2 to 2
STATEMENT: create index idx2243 on geom_table using gist(geom)"

I was thinking that picksplit would always look at an updated version of LIMIT_RATIO. But I'm at a loss at how to approach the problem at this stage. Perhaps my implementation is not correct or my approach not appropriate at all.

Is there a better way of introducing this parameter? Or maybe I am missing something? I would be very grateful for any feedback, pointers or comments.

Best regards,
Thomas

#2Tom Lane
tgl@sss.pgh.pa.us
In reply to: Thomas Mercieca (#1)
Re: Moving GiST index constant to parameter

Thomas Mercieca <tmercieca@msn.com> writes:

I have tried introducing a GUC variable (entry in guc.c/config_real) and I have also tried having the parser recognize it (same as fillfactor is recognized), but irrelevant of what I set the value to at run-time, I always get the same unusual index stats (cannot reproduce results) and the following messages in the logfile, suggesting that I broke the function:

A GUC is certainly not the right thing; you should use an index storage
parameter. However, it seems like your problem is independent of that.

"DEBUG: picksplit method for column 1 of index "idx2243" failed
HINT: The index is not optimal. To optimize it, contact a developer, or try to use the column as the second one in the CREATE INDEX command.
STATEMENT: create index idx2243 on geom_table using gist(geom)"

It looks like picksplit would necessarily fail if you tried to use
a ratio >= 0.5, or even very close to 0.5, because you'd be demanding
unachievable perfection of the split.

Beyond that observation, it's hard to say much without seeing what
you changed.

regards, tom lane

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers