Re: Odd behavior with indices - Mailing list pgsql-performance

From Tom Lane
Subject Re: Odd behavior with indices
Date
Msg-id 19049.1456771632@sss.pgh.pa.us
Whole thread Raw
In response to Re: Odd behavior with indices  (Matheus de Oliveira <matioli.matheus@gmail.com>)
Responses Re: Odd behavior with indices  (Merlin Moncure <mmoncure@gmail.com>)
List pgsql-performance
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


pgsql-performance by date:

Previous
From: Matheus de Oliveira
Date:
Subject: Re: Odd behavior with indices
Next
From: Tom Lane
Date:
Subject: Re: Merge joins on index scans