Re: Incorrect index being used

On 09/10/2013 18:06, Tom Lane wrote:
> Jesse Long <jpl(at)unknown(dot)za(dot)net> writes:
>> The query runs for much longer than I expect it to run for, and I think
>> this is due to it using the incorrect subplan. As you can see, subplans
>> 1 and 3 make use of and index, but these subplans are not used.
>> Subplans and 4 are seqscan, and they are used.
>> How can I get PostgreSQL to use subplan 1 and 3?
> You can't, and you would not like the results if you did.
>
> The construct that's being described (perhaps not very intelligibly)
> by this EXPLAIN output is an alternative pair of subplans. Actually
> there are two such alternative pairs in this example. The indexscan
> variants are subplans that would be fast if executed only once or
> twice. The seqscan variants, if used, are used to load a hashtable
> that is then probed for each row of the outer plan. If there are a
> lot of rows to be considered in the outer plan, then it's better to
> pay the price of loading the hashtable, because each hashtable probe
> will be a lot cheaper than doing a fresh indexscan with the comparison
> value from the current outer row.
>
> In this example, we can see that the outer scan that the subplans
> are attached to eliminated 710851 rows by means of the subplan filters,
> meaning that the subplans were probed 710851+2 times. If each of those
> probes had been done with a separate indexscan, you'd likely still be
> waiting for the result. Using the seqscan+hashtable was definitely the
> right choice here.
>
> BTW, the reason it looks like this rather than just hard-wiring the
> seqscan choice is a planner implementation artifact --- at the time
> that the subplan plans are created, we don't know how many rows are
> expected to pass through the outer plan level. So we plan it both
> ways and leave the choice to be made during executor startup.
>
> What I'd suggest is that you see if you can't get rid of the "EXISTS() OR
> EXISTS()" construction in favor of a single EXISTS clause --- I'm too lazy
> to work out the details but it looks like you could do the OR in the WHERE
> clause of a single EXISTS sub-select. That would allow the planner to
> convert the EXISTS into a semi-join, which might work better than what
> you've got. As is, you're dealing with fairly generic sub-select logic
> that isn't going to be terribly well optimized.
>

Hi Tom,

I am very grateful for your detailed reply. I have not had much time to
pursue this issue further, but as soon as I have I will investigate and
study what you have written.

Thanks for taking the time to write your thoughts in detail.

Cheers,
Jesse