Re: same plan, add 1 condition, 1900x slower

Mitch Skinner <mitch(at)egcrc(dot)net> writes:
> On Fri, 2005-11-11 at 09:17 -0500, Tom Lane wrote:
>> Please don't, actually, until we understand what's going on.

> Ack, I was the middle of the vacuum full already when I got this.

Given what you said about no deletions or updates, the vacuum should
have no effect anyway, so don't panic.

> I put up some files at: http://arctur.us/pgsql/

Great, I'll take a look ...

> Pardon my ignorance, but do the visibility check and the check of the
> condition happen at different stages of execution? Would it end up
> checking the condition for all 15M rows, but only checking visibility
> for the 1200 rows that come back from the join?

No, the visibility check happens first. The timing does seem consistent
with the idea that the comparison is being done at all 15M rows, but
your other EXPLAIN shows that only 2K rows are actually retrieved, which
presumably is because the merge doesn't need the rest. (Merge will stop
scanning either input when it runs out of rows on the other side; so
this sort of plan is very fast if the range of keys on one side is
smaller than the range on the other. The numbers from the no-comparison
EXPLAIN ANALYZE indicate that that is happening for your case.) So the
comparison should happen for at most 2K rows too.

regards, tom lane