Re: Very slow update + not using clustered index

Mike Glover <mpg4@duluoz.net> writes:
> Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> It seems unlikely that the raw row inserts and updating the single
>> index could be quite that slow --- perhaps you have a foreign key
>> or trigger performance problem?

>    There are no foreign keys or triggers for either of the tables.

Hmph.  It's clear that it is the update overhead that's taking the time
(since you show 292 seconds actual time in the update's top plan node
--- that's the time to find the rows and compute their new values, and
all the rest of the elapsed 3162 sec has to be update overhead).  Maybe
you just have a slow disk.

Just out of curiosity, how much time does the update take if you don't
have any index on the summary table?  Try

    create temp table tsummary as select * from summary;
    vacuum analyze tsummary;
    explain analyze update tsummary set ... ;


>> A quick experiment shows that if the planner does not have any reason
>> to prefer one ordering over another, the current coding will put the
>> last WHERE clause first:
> [snip]>

> It looks like the planner is already making a principled choice:

After a little bit of experimentation I was reminded that the planner
does account for the possibility that a merge join can stop short of
full execution when the first mergejoin columns have different data
ranges.  In this case it's preferring to put price first because there
is a greater discrepancy in the ranges of s.price_min and i.price than
there is in the ranges of the isbn columns.  I'm not sure that it's
wrong.  You could try increasing the statistics target on the price
columns (and re-ANALYZing) to see if finer-grain data changes that
estimate at all.

In any case, the fact that the chosen plan doesn't make use of your
index on isbn doesn't mean that such a plan wasn't considered.   It was,
but this plan was estimated to be less expensive.  You could check out
alternative plans and see if the estimate is accurate by fooling with
enable_seqscan and enable_sort.

            regards, tom lane