Re: Index (re)-creation speed

Steve Lane <slane@moyergroup.com> writes:
> When I drop and rebuild the indexes, they take oddly varying amounts of time
> to rebuild. I rebuilt them in the following order, with the following rough
> times. I took a guess that the speed of the rebuild might be related to the
> number of distinct values in the column -- this seems true in some cases but
> not in others. Here are the times:

> id_response       38 secs (86000 distinct)
> id_topic          33 secs (6 distinct)
> id_survey         13 secs (1 distinct)
> id_code           39 secs (1444 distinct)
> id_item           40 secs (65 distinct)
> id_administration 13 secs (1 distinct)

How many rows altogether in this table?  What sort_mem are you using
(and have you tried altering it)?

When I read your previous message I was wondering about different
datatypes having different comparison costs, but since these are all
integer columns that's clearly not the issue here.  I think you may
be seeing something related to the number of initial runs created in the
sorting algorithm --- with only one distinct value, there'd always be
just one run and no need for any merge passes.

(Why are you bothering to index columns with only one or a few values in
them, anyway?  Such an index will be useless for searches ...)

            regards, tom lane