Add possibility to use hstore based on a -> instead of @>

[msiegenthaler]

I found that when using tags (HSTORE) I get better performance when I use tags->'key' = 'value' instead of tags @> hstore('key', 'value') in the case when there are tags that are very infrequent.

With the -> variant I can create an index of the key (e.g. create index "tag_portEvent" on "journal" ((tags -> 'PortEvent'), id);) and then postgres will make use of that index:

Aggregate  (cost=140.43..140.44 rows=1 width=8) (actual time=0.014..0.014 rows=1 loops=1)
  ->  Bitmap Heap Scan on journal  (cost=9.20..140.34 rows=34 width=8) (actual time=0.013..0.013 rows=0 loops=1)
        Recheck Cond: ((((tags -> 'PortEvent'::text) = 'a/%2Fprocess-engine%2FIssueTypeUpdated'::text) AND (id >= 0)) OR (((tags -> 'PortEvent'::text) = 'a/%2Fprocess-engine%2FChildIssuesUpdated'::text) AND (id >= 0)))
        ->  BitmapOr  (cost=9.20..9.20 rows=34 width=0) (actual time=0.012..0.012 rows=0 loops=1)
              ->  Bitmap Index Scan on "tag_portEvent"  (cost=0.00..4.59 rows=17 width=0) (actual time=0.010..0.010 rows=0 loops=1)
                    Index Cond: (((tags -> 'PortEvent'::text) = 'a/%2Fprocess-engine%2FIssueTypeUpdated'::text) AND (id >= 0))
              ->  Bitmap Index Scan on "tag_portEvent"  (cost=0.00..4.59 rows=17 width=0) (actual time=0.002..0.002 rows=0 loops=1)
                    Index Cond: (((tags -> 'PortEvent'::text) = 'a/%2Fprocess-engine%2FChildIssuesUpdated'::text) AND (id >= 0))
Planning Time: 0.156 ms
Execution Time: 0.034 ms

with @> it'll do a long table scan:

Result  (cost=12.07..12.08 rows=1 width=8) (actual time=113.636..113.636 rows=1 loops=1)
  InitPlan 1 (returns $0)
    ->  Limit  (cost=0.42..12.07 rows=1 width=8) (actual time=113.633..113.633 rows=0 loops=1)
          ->  Index Scan Backward using journal_pkey on journal  (cost=0.42..20055.04 rows=1722 width=8) (actual time=113.632..113.632 rows=0 loops=1)
                Index Cond: ((id IS NOT NULL) AND (id >= 0))
                Filter: (((tags -> 'PortEvent'::text) = 'a/%2Fprocess-engine%2FIssueTypeUpdated'::text) OR ((tags -> 'PortEvent'::text) = 'a/%2Fprocess-engine%2FChildIssuesUpdated'::text))
                Rows Removed by Filter: 172253
Planning Time: 0.727 ms
Execution Time: 113.663 ms

Neither a gin nor a gist index on tags will prevent that.

I',m aware that this isn't an optimisation in all cases, that's why I made it configurable via a config flag: hstoreEquals = true // use "tags -> 'key' = value" instead of "tags @> hstore('key', 'value') for queries

[msiegenthaler]

Here's the index format I use: create index "journal_tag_portEvent" on "journal" using btree ((tags -> 'PortEvent'), id desc);