From: Tom Lane <tgl@sss.pgh.pa.us>
To: meike.talbach@women-at-work.org
cc: pgsql-performance@postgresql.org
Subject: Re: Index not used
In-reply-to: 
 <trinity-5d1ea92f-3085-43a8-917c-d93b522487c2-1466063926243@3capp-gmx-bs63>
References: 
 <trinity-5d1ea92f-3085-43a8-917c-d93b522487c2-1466063926243@3capp-gmx-bs63>
Comments: In-reply-to meike.talbach@women-at-work.org
	message dated "Thu, 16 Jun 2016 09:58:46 +0200"
Date: Thu, 16 Jun 2016 11:05:32 -0400
Message-ID: <20334.1466089532@sss.pgh.pa.us>
Precedence: bulk
Sender: pgsql-performance-owner@postgresql.org

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 = 'DD748CCD-B8A4-3B9F-8F60-67F1F673CFE5'
> Index Scan using push_topic_idx_topicguid on push_topic  (cost=0.42..8.44 rows=1 width=103) (actual time=0.117..0.121 rows=1 loops=1)
>   Index Cond: ((guid)::bpchar = 'DD748CCD-B8A4-3B9F-8F60-67F1F673CFE5'::bpchar)
>   Buffers: shared hit=3 read=1
> 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 = '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 = '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".

			regards, tom lane


-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance