Alvaro Herrera <alvherre@xxxxxxxxxxxxxxxxx> writes:
> Tom Lane escribió:
>> In the long run, for queries inserting many rows it might be interesting
>> to accumulate all the entries intended for a btree index and sort them
>> before inserting. Not sure about possible downsides of that.
> Yeah, in the long run we need a more flexible approach towards index
> insertion. I doubt we want to know _all_ the entries beforehand; that
> would probably involve disk-spilling, etc. Perhaps accumulate a
> reasonable number (using up to work_mem?) and insert those in batches.
After sleeping on it, the main problem I can see is that it'd mean the
index is transiently invalid. As an example of the problem, it's always
been true in the past that a BEFORE INSERT trigger could "see" rows
already inserted by the calling command --- but if it chanced to try to
use an index with delayed insertions, it might not see them. You could
work around this if the indexscan code knew to go search in the list of
pending insertions, but that's pretty ugly and possibly slow too.
regards, tom lane
---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
