Re: Index not used

On Thu, Jun 16, 2016 at 11:05 AM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> meike(dot)talbach(at)women-at-work(dot)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".
>

​Or, better, persuade the app to label the value "
​
public.push_guid
​" since that is the column's type​...a type you haven't defined 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.