Re: Unable to make use of "deep" JSONB index

Op 12-06-2022 om 21:31 schreef Shaheed Haque:

> Thanks Erik. Is the point that the index has to be on the JSON field
> as a whole (i.e. "snapshot") rather than deep inside it (e.g.
> "snapshot.something.further[down]")?
> 
> In my case, the snapshot is several MB in size (perhaps 10MB or even
> 20MB), dominated by the snapshot.employee (cardinality 10k, each sized
> as a dict 1-2kB as text). My expectation/guess is that an index of
> "snapshot" will itself be of a size of similar order. However the
> design as-is works very well except for this one case where to speed
> it up, in principle, the index need contain no more than one boolean
> per employee. So that's what I'd like to achieve, if possible.
> 
> I've seen no hint in the documentation that creating the index on
> "snapshot.something.further[down]" should not work, and PG certainly
> allows it to be created. Also, Tom has suggested that I should not
> look to some magical ability to infer the use of the index from a
> differently structured query, and I've taken that on board with the
> updated query + index.

If you insist on a btree/integer on pay_graph, the below stuff seems to 
work, no?

Perhaps you've missed the difference
between
   x != 0  on the one hand,
and
   x > 0  on the other,

Here are both queries to show that difference.


CREATE INDEX payrun_btree_paygr_idx ON payrun using btree 
(((snapshot->'employee'->'999'->>'pay_graph')::integer));

set enable_seqscan = 0;

SELECT id, snapshot #>'{employee,999,state,employment}'
FROM payrun
WHERE (snapshot->'employee'->'999'->>'pay_graph')::integer > 0 ;
  id |         ?column?
----+---------------------------
   2 | [["1970-01-01", null, 3]]
(1 row)

Time: 1.384 ms

explain analyze  SELECT id, snapshot #>'{employee,999,state,employment}'
FROM payrun WHERE (snapshot->'employee'->'999'->>'pay_graph')::integer > 
0 ;
                                                            QUERY PLAN 


---------------------------------------------------------------------------------------------------------------------------------
  Index Scan using payrun_btree_paygr_idx on payrun  (cost=0.14..8.98 
rows=42 width=36) (actual time=0.018..0.020 rows=1 loops=1)
    Index Cond: (((((snapshot -> 'employee'::text) -> '999'::text) ->> 
'pay_graph'::text))::integer > 0)
  Planning Time: 0.047 ms
  Execution Time: 0.048 ms
(4 rows)

Time: 0.386 ms
explain analyze  SELECT id, snapshot #>'{employee,999,state,employment}'
FROM payrun WHERE (snapshot->'employee'->'999'->>'pay_graph')::integer 
!= 0 ;
                                                         QUERY PLAN 


---------------------------------------------------------------------------------------------------------------------------
  Seq Scan on payrun  (cost=10000000000.00..10000000004.49 rows=126 
width=36) (actual time=112.182..112.186 rows=1 loops=1)
    Filter: (((((snapshot -> 'employee'::text) -> '999'::text) ->> 
'pay_graph'::text))::integer <> 0)
    Rows Removed by Filter: 3
  Planning Time: 0.050 ms
  JIT:
    Functions: 4
    Options: Inlining true, Optimization true, Expressions true, 
Deforming true
    Timing: Generation 0.636 ms, Inlining 41.761 ms, Optimization 52.033 
ms, Emission 18.228 ms, Total 112.658 ms
  Execution Time: 153.486 ms
(9 rows)

Time: 153.835 ms

So:
     0.386 ms  for x > 0   uses payrun_btree_paygr_idx
vs:
   153.835 ms  for x != 0  uses seq scan

It's hard to see for me where you encounter problems; including the fast 
searches in my earier mail, it looks to me like you have many good options.

Erik Rijkers