Thread: UUID index unused

I am assuming I am crazy and missing something completely obvious but I cannot get postgres (9.3.5) to use an index on
aUUID, ever.  

The main table has a natural composite key (2 uuids and a timestamp) with which it always uses the timestamp as the
indexcondition and filters on the UUIDs.  This occurs when when we do a query for a specific item comparing all 3 key
columnswith equality.  Other tables that have a single UUID column index also fail to ever utilize any available
indices;querying for a specific UUID always results in a table scan for them. 

Switching the UUID columns to text immediately solves the issues and index usage is as expected in all cases.

After hours of fiddling I figured it was time to ask.  Any ideas?

P.S.  Before it’s suggested… we have millions of independent devices generating the ids which drives our use of UUIDs.
Anythoughts of replacing them only result in us building something that’s basically a UUID to replace it. 

On 11/09/2014 10:58 AM, Kevin Wooten wrote:
> I am assuming I am crazy and missing something completely obvious but I cannot get postgres (9.3.5) to use an index
ona UUID, ever. 
>
> The main table has a natural composite key (2 uuids and a timestamp) with which it always uses the timestamp as the
indexcondition and filters on the UUIDs.  This occurs when when we do a query for a specific item comparing all 3 key
columnswith equality.  Other tables that have a single UUID column index also fail to ever utilize any available
indices;querying for a specific UUID always results in a table scan for them. 
>
> Switching the UUID columns to text immediately solves the issues and index usage is as expected in all cases.
>
> After hours of fiddling I figured it was time to ask.  Any ideas?

The only thing I could after a quick search was this:

http://stackoverflow.com/questions/22720130/how-to-use-uuid-with-postgresql-gist-index-type

Sort of a hybrid approach.
>
> P.S.  Before it’s suggested… we have millions of independent devices generating the ids which drives our use of
UUIDs. Any thoughts of replacing them only result in us building something that’s basically a UUID to replace it. 
>
>
>


--
Adrian Klaver
adrian.klaver@aklaver.com

Kevin Wooten <kdubb@me.com> writes:
> I am assuming I am crazy and missing something completely obvious but I cannot get postgres (9.3.5) to use an index
ona UUID, ever.  

Worksforme:

regression=# create table foo (f1 uuid primary key);
CREATE TABLE
regression=# explain select * from foo where f1 = 'a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11';
                                QUERY PLAN
--------------------------------------------------------------------------
 Index Only Scan using foo_pkey on foo  (cost=0.15..8.17 rows=1 width=16)
   Index Cond: (f1 = 'a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11'::uuid)
(2 rows)

> The main table has a natural composite key (2 uuids and a timestamp) with which it always uses the timestamp as the
indexcondition and filters on the UUIDs. 

This probably has little to do with the datatype as such, and much to do
with the specifics of your query, the available indexes, and the table's
statistics.  It's hard to speculate further without lots more detail
about those things.

            regards, tom lane

This affirmation that it indeed does work set me straight. I inadvertently made a previously immutable UUID function
volatile;it was providing the UUIDs in the query. 

> On Nov 9, 2014, at 2:12 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>
> Kevin Wooten <kdubb@me.com> writes:
>> I am assuming I am crazy and missing something completely obvious but I cannot get postgres (9.3.5) to use an index
ona UUID, ever.  
>
> Worksforme:
>
> regression=# create table foo (f1 uuid primary key);
> CREATE TABLE
> regression=# explain select * from foo where f1 = 'a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11';
>                                QUERY PLAN
> --------------------------------------------------------------------------
> Index Only Scan using foo_pkey on foo  (cost=0.15..8.17 rows=1 width=16)
>   Index Cond: (f1 = 'a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11'::uuid)
> (2 rows)
>
>> The main table has a natural composite key (2 uuids and a timestamp) with which it always uses the timestamp as the
indexcondition and filters on the UUIDs. 
>
> This probably has little to do with the datatype as such, and much to do
> with the specifics of your query, the available indexes, and the table's
> statistics.  It's hard to speculate further without lots more detail
> about those things.
>
>             regards, tom lane