Jake Magner <jakemagner90@gmail.com> writes:
> I am trying to insert rows that don't already exist from a temp table into
> another table. I am using a LEFT JOIN on all the columns and checking for
> nulls in the base table to know which rows to insert. The problem is that
> the planner is choosing a nested loop plan which is very slow over the much
> faster (~40x) hash join. What's interesting is that all the row estimates
> appear to be fairly accurate. I'm wondering if it has something to do with
> the GIN indexes on bigint_array_1 and bigint_array_2. Perhaps it
> misestimates the cost of each index scan?
I'm curious about what happens to the runtime if you repeatedly roll back
the INSERT and do it over (without vacuuming in between).
What I'm thinking is that as the INSERT proceeds, it'd be making entries
in those GIN indexes' pending-item lists, which the bitmap indexscan would
have to scan through since it's examining the same table you're inserting
into. The pending-item list is unsorted so it's relatively expensive to
scan. Since, after you insert each row from temp_rows_to_insert, you're
doing a fresh bitmap indexscan, it seems like the cost to deal with the
pending item list would be proportional to O(N^2) --- so even though the
cost per pending item is not that large, N=4000 might be enough to hurt.
If this theory is correct, a second attempt to do the INSERT without
having flushed the pending-list would be even more expensive; while
if I'm wrong and that cost is negligible, the time wouldn't change much.
The hashjoin approach avoids this problem by not using the index (and
even if it did, the index would be scanned only once before any
insertions happen). The planner unfortunately has no idea about this
interaction.
If this diagnosis is correct, there are a couple ways you could get around
the problem:
* disable use of the pending list by turning off "fastupdate" for these
indexes.
* construct the set of rows to be inserted in a separate command and
put them into a second temp table, then insert to the main table.
The second choice is probably preferable; doing bulk GIN inserts
without fastupdate is kind of expensive itself.
regards, tom lane