Can't select with a primitive

[CrackedP0t]

When I try to run a simple query:

select * from images where hash = 4039471674891636236;

I get this error:

ERROR:  XX000: unrecognized strategy number: 2                                                                                                                                                                   LOCATION:  bktree_inner_consistent, bktree.c:239

[fake-name]

Hmmm. Do you have a normal index on the column in question?

I'm slightly confused, as this works correctly.

Can you try

select * from images where hash = 4039471674891636236::int8;

As an alternative,

select * from images WHERE  hash <@ (4039471674891636236, 0)

will operate as a identity search (equivalent to hash = 4039471674891636236).

[CrackedP0t]

It happens both when there's only the bktree index and when there's a regular index as well

[CrackedP0t]

And yeah, the alternative with distance 0 is what I've been using

[fake-name]

What happens with the explicit cast?

[CrackedP0t]

select * from images where hash = 4039471674891636236::int8; causes the same error

[fake-name]

Ok, weird. The BK tree both provides OPERATOR 1 <@ and OPERATOR 2 =, and I'm not sure what the strategy value is referring to in this context.

Also:

deduper_db=# \d dedupitems
                                     Table "public.dedupitems"
    Column    |       Type       | Collation | Nullable |                 Default
--------------+------------------+-----------+----------+------------------------------------------
 dbid         | integer          |           | not null | nextval('dedupitems_dbid_seq'::regclass)
 fspath       | text             |           | not null |
 internalpath | text             |           | not null |
 itemhash     | text             |           |          |
 itemkind     | text             |           |          |
 imgx         | integer          |           |          |
 imgy         | integer          |           |          |
 phash        | bigint           |           |          |
 dhash        | bigint           |           |          |
 scantime     | double precision |           |          | 0
 whash        | bigint           |           |          |
Indexes:
    "dedupitems_pkey" PRIMARY KEY, btree (dbid)
    "dedupitems_name_index" UNIQUE, btree (fspath, internalpath)
    "dedup_items_phash_bk_index" spgist (phash bktree_ops)
    "dedupitems_dhash_index" btree (dhash)
    "dedupitems_ihash_index" btree (itemhash)
    "dedupitems_path_index" btree (fspath text_pattern_ops)
    "dedupitems_phash_index" btree (phash)
    "dedupitems_scantime_index" btree (scantime)
Referenced by:
    TABLE "dedupitems_plink" CONSTRAINT "dedupitems_plink_item_1_link_fkey" FOREIGN KEY (item_1_link) REFERENCES dedupitems(dbid) ON DELETE CASCADE
    TABLE "dedupitems_plink" CONSTRAINT "dedupitems_plink_item_2_link_fkey" FOREIGN KEY (item_2_link) REFERENCES dedupitems(dbid) ON DELETE CASCADE

deduper_db=# EXPLAIN ANALYZE select * from dedupitems where phash= 0;
                                                              QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on dedupitems  (cost=119.90..12223.87 rows=8237 width=257) (actual time=2.160..15.065 rows=9507 loops=1)
   Recheck Cond: (phash = 0)
   Heap Blocks: exact=6512
   ->  Bitmap Index Scan on dedupitems_phash_index  (cost=0.00..117.84 rows=8237 width=0) (actual time=1.243..1.243 rows=9508 loops=1)
         Index Cond: (phash = 0)
 Planning Time: 0.182 ms
 Execution Time: 15.553 ms
(7 rows)

Did you add a normal B-Tree index, and then ANALYZE <table>;? I wonder if the normal index wasn't being used for planning.

[fake-name]

If adding a normal index and ANALYZEing doesn't fix the issue, can you let me know what postgres version specifically you're running? I should have more time this weekend to take a look.

[CrackedP0t]

Oh, cool, ANALYZE images; after adding the regular index worked!

I'm running Postgres 11.5 by the way.

[fake-name]

Ok, so it's more of a planning issue, then.

I'm not sure why the query planner thinks this index can handle = queries, but I'm apparently doing something wrong. I either need to make the index support the equality operator, or have it convince the planner to not use it for certain queries.

[edocod1]

I am affected by this as well. But everything i try doesn't seem to convince the planner to use any other index than the bk one :(

[KDJDEV]

select * from images where hash = 4039471674891636236::int8;

I thought I would mention that I experience this exact same error. It does appear to be fixed for now when I use <@ instead of =.