Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.84_2) (envelope-from ) id 1bDZby-0005da-Nj for pgsql-performance@arkaria.postgresql.org; Thu, 16 Jun 2016 15:53:42 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.84_2) (envelope-from ) id 1bDZbx-0008Bb-VI for pgsql-performance@arkaria.postgresql.org; Thu, 16 Jun 2016 15:53:42 +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_2) (envelope-from ) id 1bDZbw-000891-Jt for pgsql-performance@postgresql.org; Thu, 16 Jun 2016 15:53:40 +0000 Received: from mail-oi0-x231.google.com ([2607:f8b0:4003:c06::231]) by magus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.84_2) (envelope-from ) id 1bDZbt-0003AK-73 for pgsql-performance@postgresql.org; Thu, 16 Jun 2016 15:53:40 +0000 Received: by mail-oi0-x231.google.com with SMTP id p204so75935959oih.3 for ; Thu, 16 Jun 2016 08:53:36 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20120113; h=mime-version:in-reply-to:references:from:date:message-id:subject:to :cc; bh=AL66jcq43JHS3O85y6FPzLeRmHkCbEiAgoVwerZXw28=; b=ZE0cAgLLzVClJAaXpL9insw5aQVjyBgP1AyG6yq9CVw5JGLcKuwgJ1uTjlsTGnb86m fXONrtR5AlFmo3MYKIkUkf5TNm+YKzyLoG4D+OV1oKhLVC8EHNeK0MTgyZTSsL0GxUqu 6XcUMJwgVMaa/h6LVe8RhTp2C8OOln5rRaQdlXTjHiCCVguTAeHlmYmJWmTTcKrI0rng CahsShO53hrqMrQX6E1nHf/5aO8gW/QEmxp8o3CBH7NKfXm7VEZwy15ZWEiqQIw9gRwb atrx/lmiRjPDE+6PQZR84kUG6cYCoBvqkS+Ei6Sa46UV1suzDgJA3kDqavOP3CdBSRMj lVxw== 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:from:date :message-id:subject:to:cc; bh=AL66jcq43JHS3O85y6FPzLeRmHkCbEiAgoVwerZXw28=; b=NmhtZzHviA41GDE3osu3jtBV2FUiBpI9vY/YmrVvtTec+JymszHb36JUxsVv4iXJGq 0ClDUU4RbtqDRtDkFDFWguhyCidZ4b++0IfNlXVk6OfIg+JCy9CHJ57gEWwAL2aNLxk2 YnOsFv04Bkdkp92BseohGTyKXy1yq2QLiQDStFvaFMAW1ARCMHjJHIwVILnhVjhsRzq6 +8OOhjgwRRjj2GtTVA/sIPTNuaorgDx5Iy4i236Rp2i620QRa9eIxpkWTE3WJG57zaeG 1FhaTJQXwGgknHkQ1fwRhyqbJs24rjQVyb+Z7xbzcFsnl58XsNkBJEKJZKB5WHqJdYZ9 vTCw== X-Gm-Message-State: ALyK8tKQBKmAy8/B4P3E/I0cD0mE4LKM+xsbYKfd/WhZqi4wipBVxOy9mCy+WPJ0/MKL83NKw6O6EMpdmctgbA== X-Received: by 10.202.214.86 with SMTP id n83mr3144325oig.66.1466092415269; Thu, 16 Jun 2016 08:53:35 -0700 (PDT) MIME-Version: 1.0 Received: by 10.157.34.104 with HTTP; Thu, 16 Jun 2016 08:53:34 -0700 (PDT) In-Reply-To: <20334.1466089532@sss.pgh.pa.us> References: <20334.1466089532@sss.pgh.pa.us> From: "David G. Johnston" Date: Thu, 16 Jun 2016 11:53:34 -0400 Message-ID: Subject: Re: Index not used To: Tom Lane Cc: meike.talbach@women-at-work.org, "pgsql-performance@postgresql.org" Content-Type: multipart/alternative; boundary=001a113de3489ec3920535673d24 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 --001a113de3489ec3920535673d24 Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: quoted-printable On Thu, Jun 16, 2016 at 11:05 AM, Tom Lane wrote: > meike.talbach@women-at-work.org writes: > > When I query this through pgsql, the queries are fast as expected. > > select * from push_topic where guid =3D > 'DD748CCD-B8A4-3B9F-8F60-67F1F673CFE5' > > Index Scan using push_topic_idx_topicguid on push_topic > (cost=3D0.42..8.44 rows=3D1 width=3D103) (actual time=3D0.117..0.121 rows= =3D1 loops=3D1) > > Index Cond: ((guid)::bpchar =3D > 'DD748CCD-B8A4-3B9F-8F60-67F1F673CFE5'::bpchar) > > Buffers: shared hit=3D3 read=3D1 > > Total runtime: 0.191 ms > > > However when I run the exact query through a different application > (CodeSynthesis ORM) the query is very slow (~ 115ms logged) > > I noted this is due to a sequential scan happening on the table instead > of an index scan. > > It looks like what that app is actually issuing is something different > from what you tested by hand, to wit > > select * from push_topic where guid =3D > 'DD748CCD-B8A4-3B9F-8F60-67F1F673CFE5'::text > > which causes the comparison to be resolved as texteq not bpchareq, ie you > effectively have > > select * from push_topic where guid::text =3D > 'DD748CCD-B8A4-3B9F-8F60-67F1F673CFE5'::text > > and that doesn't match a bpchar index. If you can't persuade the app to > label the comparison value as bpchar not text, the easiest fix would be > to create an additional index on "guid::text". > =E2=80=8BOr, better, persuade the app to label the value " =E2=80=8B public.push_guid =E2=80=8B" since that is the column's type=E2=80=8B...a type you haven't de= fined for us. If you get to add explicit casts this should be easy...but I'm not familiar with the framework you are using. David J. --001a113de3489ec3920535673d24 Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: quoted-printable
On Thu, Ju= n 16, 2016 at 11:05 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
meike.talbach@women-at-work.org writes:
> When I query this through pgsql, the queries are fast as expected.
> select * from push_topic where guid =3D 'D= D748CCD-B8A4-3B9F-8F60-67F1F673CFE5'
> Index Scan using push_topic_idx_topicguid on p= ush_topic=C2=A0 (cost=3D0.42..8.44 rows=3D1 width=3D103) (actual time=3D0.1= 17..0.121 rows=3D1 loops=3D1)
>=C2=A0 =C2=A0Index Cond: ((guid)::bpchar =3D 'DD748CCD-B8A4-3B9F-8F= 60-67F1F673CFE5'::bpchar)
>=C2=A0 =C2=A0Buffers: shared hit=3D3 read=3D1
> Total runtime: 0.191 ms

> However when I run the exact query through a different application (Co= deSynthesis ORM) the query is very slow (~ 115ms logged)
> I noted this is due to a sequential scan happening on the table instea= d of an index scan.

It looks like what that app is actually issuing is something differe= nt
from what you tested by hand, to wit

select * from push_topic where guid =3D 'DD748CCD-B8A4-3B9F-8F60-67F1F6= 73CFE5'::text

which causes the comparison to be resolved as texteq not bpchareq, ie you effectively have

select * from push_topic where guid::text =3D 'DD748CCD-B8A4-3B9F-8F60-= 67F1F673CFE5'::text

and that doesn't match a bpchar index.=C2=A0 If you can't persuade = the app to
label the comparison value as bpchar not text, the easiest fix would be
to create an additional index on "guid::text".

=E2=80=8BOr, better, persuade the app t= o label the value "
=E2=80=8B
public.push_= guid
=E2=80=8B" since that is the column's type= =E2=80=8B...a type you haven't defined for us.=C2=A0 If you get to add = explicit casts this should be easy...but I'm not familiar with the fram= ework you are using.

David J.
--001a113de3489ec3920535673d24--