From: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
---|---|
To: | Matheus de Oliveira <matioli(dot)matheus(at)gmail(dot)com> |
Cc: | joe meiring <josephmeiring(at)gmail(dot)com>, pgsql-performance <pgsql-performance(at)postgresql(dot)org> |
Subject: | Re: Odd behavior with indices |
Date: | 2016-02-29 18:47:12 |
Message-ID: | 19049.1456771632@sss.pgh.pa.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
Matheus de Oliveira <matioli(dot)matheus(at)gmail(dot)com> writes:
> Em 26 de fev de 2016 4:44 PM, "joe meiring" <josephmeiring(at)gmail(dot)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
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2016-03-01 01:22:28 | Re: Merge joins on index scans |
Previous Message | Matheus de Oliveira | 2016-02-28 13:26:11 | Re: Odd behavior with indices |