meike.talb...@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
