Re: Odd behavior with indices

Matheus de Oliveira <matioli.matheus@gmail.com> writes:
> Em 26 de fev de 2016 4:44 PM, "joe meiring" <josephmeiring@gmail.com>
> escreveu:
>> The same query for parameters is rather slow and does NOT use the index:
>>
>> EXPLAIN ANALYZE
>> select *
>> from parameter
>> where exists (
>> select 1 from datavalue
>> where datavalue.parameter_id = parameter.id limit 1
>> );

> Please, could you execute both queries without the LIMIT 1 and show us the
> plans?

> LIMIT in the inner query is like a fence and it caps some optimizations
> available for EXISTS, you'd better avoid it and see if you get a proper
> semi-join plan then.

FWIW, PG >= 9.5 will ignore a LIMIT 1 inside an EXISTS, so that you get
the same plan with or without it.  But that does act as an optimization
fence in earlier releases.

            regards, tom lane