Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1aa1MY-00087q-FO for pgsql-performance@arkaria.postgresql.org; Sun, 28 Feb 2016 13:26:18 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.84) (envelope-from ) id 1aa1MX-000126-UE for pgsql-performance@arkaria.postgresql.org; Sun, 28 Feb 2016 13:26:17 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.84) (envelope-from ) id 1aa1MX-00011t-BU for pgsql-performance@postgresql.org; Sun, 28 Feb 2016 13:26:17 +0000 Received: from mail-oi0-x229.google.com ([2607:f8b0:4003:c06::229]) by magus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.84) (envelope-from ) id 1aa1MU-0003kp-4X for pgsql-performance@postgresql.org; Sun, 28 Feb 2016 13:26:16 +0000 Received: by mail-oi0-x229.google.com with SMTP id d205so6791588oia.0 for ; Sun, 28 Feb 2016 05:26:13 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20120113; h=mime-version:in-reply-to:references:date:message-id:subject:from:to :cc; bh=M6MSlW4G5BxRZ84lGL92dPMIUBH7+Gyc6gKXzPzZ+yo=; b=OIKYdUHPATzFvd6jYVoQjNut53Dh171z9MecDkD4FGNFNkISZ1FkKMaudwRxFboKKB jYzZnscNVymBZXf+lRS9Sgh+uiSx3RzI6NMrgbYscb8rvYKVM2iA+JR0ypL0eRO1i/Gq r12b/gsQVFWsHpOGAuvHVmbmNB4JXok1iFgocHUNDX9w0Yc5+2T5bCliyWMUCnfOPW2n hr5w+fBXwqGX+TH3ZWrqdyeH4j+QQBj05TIo9Hlk/7S+yCiG3UnhgSr7aT5BP+rSopDL 8n/XsTWKJw8bx7RPyzV/r8cP9zc3jHJQvw77iJaFPTA5VuwEOvHtJe/ZGFW53BgHpgN6 StaQ== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20130820; h=x-gm-message-state:mime-version:in-reply-to:references:date :message-id:subject:from:to:cc; bh=M6MSlW4G5BxRZ84lGL92dPMIUBH7+Gyc6gKXzPzZ+yo=; b=erdC69tKkXIr6O8uGo59avw9ToRkhxIfpbF2bOAI5GI1iwWbKTJ+enO0Ib4S/KTNxV 67XkwR6PVIlhd6GlvmU+7hH/THSs357KZ2u+PDYBvRme+t8FvNcsEycJU/t1qc4By44f UNaLGOKHoVEntTiDFh6/S3KNthsgXoPEsksyyWJrJibuzXeZnfrtthkWc6fzg5uOsmI3 lrCL7/z4uBWYGGmJNZTOzZT+XeJ4S4NwC6ctTd0XcOH+GtVsSBhjUZg9xZ/c/pSb4IWi 54cD7ZD+Dmp9vxvLx7suKsvio6747njunv/OvH/s6t6tKoxstyAQOuVK/IT+RzgbHM+c 7DTw== X-Gm-Message-State: AD7BkJL0NcNeRvpGJxtkzbd22BYINBvkXtQ2nmHLjayl/NoWUCjd19NcbwcdE/97/SEsQK1xEX1eE7ZNJjSG8Q== MIME-Version: 1.0 X-Received: by 10.202.206.66 with SMTP id e63mr7746574oig.88.1456665972211; Sun, 28 Feb 2016 05:26:12 -0800 (PST) Received: by 10.202.105.147 with HTTP; Sun, 28 Feb 2016 05:26:11 -0800 (PST) Received: by 10.202.105.147 with HTTP; Sun, 28 Feb 2016 05:26:11 -0800 (PST) In-Reply-To: References: Date: Sun, 28 Feb 2016 10:26:11 -0300 Message-ID: Subject: Re: Odd behavior with indices From: Matheus de Oliveira To: joe meiring Cc: pgsql-performance Content-Type: multipart/alternative; boundary=001a113d395cd486dd052cd4799d X-Pg-Spam-Score: -2.7 (--) List-Archive: List-Help: List-ID: List-Owner: List-Post: List-Subscribe: List-Unsubscribe: X-Mailing-List: pgsql-performance Precedence: bulk Sender: pgsql-performance-owner@postgresql.org --001a113d395cd486dd052cd4799d Content-Type: text/plain; charset=UTF-8 Em 26 de fev de 2016 4:44 PM, "joe meiring" 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. Regards. --001a113d395cd486dd052cd4799d Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: quoted-printable


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 inde= x:
>
> EXPLAIN ANALYZE
> select *
> from parameter
> where exists (
>=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 select 1 from datavalue
>=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 where datavalue.parameter_id =3D <= a href=3D"http://parameter.id">parameter.id limit 1
> );
>

Please, could you execute both queries without the LIMIT 1 a= nd show us the plans?

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

Regards.

--001a113d395cd486dd052cd4799d--