6 added 4911 characters in body
source | link

The explain plan looks like maybe the issue in with the sort column. However, the sort comes back fast (~100-200 ms) for querying on other jsonb values within that metadata block.

* \d ubdrecord *

Time: 0.113s
summarist> \d ub
Did not find any relation named ub.
Time: 0.145s
summarist> \d ubdrecord
+----------------+-----------------------------+-------------+
| Column         | Type                        | Modifiers   |
|----------------+-----------------------------+-------------|
| id             | uuid                        |  not null   |
| senderduns     | character varying(256)      |             |
| sendername     | character varying(256)      |             |
| receiverduns   | character varying(256)      |             |
| receivername   | character varying(256)      |             |
| messagetype    | character varying(100)      |             |
| metadata       | jsonb                       |             |
| sourceid       | uuid                        |  not null   |
| sourcetype     | character varying(50)       |             |
| originaltime   | timestamp without time zone |  not null   |
| createdtime    | timestamp without time zone |  not null   |
| processingmode | integer                     |             |
| ubdversion     | character varying(20)       |             |
| unitofmeasure  | character varying(256)      |             |
| quantity       | numeric                     |             |
| active         | boolean                     |             |
| documentid     | character varying(256)      |             |
+----------------+-----------------------------+-------------+
Indexes:
    "pk_ubdrecord" PRIMARY KEY, btree (id)
    "createdtimeindex" btree (createdtime)
    "deliverynotelinenumberintegerindex" btree (jsonb_extract_path_text(metadata, VARIADIC ARRAY['deliveryNoteInfo'::text, 'deliveryNoteLineNumberInteger'::text]))
    "deliverynotenumberindex" btree (jsonb_extract_path_text(metadata, VARIADIC ARRAY['deliveryNoteInfo'::text, 'deliveryNoteNumber'::text]))
    "documentidindex" btree (documentid)
    "invoicenumberindex" btree (jsonb_extract_path_text(metadata, VARIADIC ARRAY['invoiceInfo'::text, 'invoiceNumber'::text]))
    "messagetypeindex" btree (messagetype)
    "polinenumberindex" btree (jsonb_extract_path_text(metadata, VARIADIC ARRAY['lineInfo'::text, 'poLineNumber'::text]))
    "polinenumberintegerindex" btree (jsonb_extract_path_text(metadata, VARIADIC ARRAY['lineInfo'::text, 'poLineNumberInteger'::text]))
    "ponumberindex" btree (jsonb_extract_path_text(metadata, VARIADIC ARRAY['lineInfo'::text, 'poNumber'::text]))
    "processingmodeindex" btree (processingmode)
    "receiverindex" btree (receiverduns)
    "salesorderlinenumberindex" btree (jsonb_extract_path_text(metadata, VARIADIC ARRAY['salesOrderInfo'::text, 'salesOrderLineNumber'::text]))
    "salesorderlinenumberintegerindex" btree (jsonb_extract_path_text(metadata, VARIADIC ARRAY['salesOrderInfo'::text, 'salesOrderLineNumberInteger'::text]))
    "salesordernumberindex" btree (jsonb_extract_path_text(metadata, VARIADIC ARRAY['salesOrderInfo'::text, 'salesOrderNumber'::text]))
    "senderindex" btree (senderduns)
    "shipmentnumberindex" btree (jsonb_extract_path_text(metadata, VARIADIC ARRAY['shipmentInfo'::text, 'shipmentNumber'::text])) 

* Similar query *

select "receivername", "unitofmeasure", "ubdversion", "processingmode", "metadata", "senderduns", "active", "createdtime", "sourcetype",
"documentid", "id", "sendername", "messagetype", "quantity", "sourceid", "originaltime", "receiverduns" from "ubdrecord"
where
(jsonb_extract_path_text("metadata",'lineInfo','poLineNumber') = '11124124124124')
order by "createdtime" desc limit 1;

* Similar query plan *

'Limit  (cost=3776.77..3776.77 rows=1 width=4046) (actual time=0.053..0.053 rows=0 loops=1)'
'  ->  Sort  (cost=3776.77..3779.20 rows=970 width=4046) (actual time=0.050..0.050 rows=0 loops=1)'
'        Sort Key: createdtime DESC'
'        Sort Method: quicksort  Memory: 25kB'
'        ->  Bitmap Heap Scan on ubdrecord  (cost=19.95..3771.92 rows=970 width=4046) (actual time=0.043..0.043 rows=0 loops=1)'
'              Recheck Cond: (jsonb_extract_path_text(metadata, VARIADIC '{lineInfo,poLineNumber}'::text[]) = '11124124124124'::text)'
'              ->  Bitmap Index Scan on polinenumberindex  (cost=0.00..19.70 rows=970 width=0) (actual time=0.039..0.039 rows=0 loops=1)'
'                    Index Cond: (jsonb_extract_path_text(metadata, VARIADIC '{lineInfo,poLineNumber}'::text[]) = '11124124124124'::text)'
'Planning time: 0.205 ms'
'Execution time: 0.091 ms'

The real head scratcher for me is why the first query doesn't seem to use the index/sortkey as this other one does. The index for both fields look the same to me.

The explain plan looks like maybe the issue in with the sort column. However, the sort comes back fast (~100-200 ms) for querying on other jsonb values within that metadata block.

The explain plan looks like maybe the issue in with the sort column. However, the sort comes back fast (~100-200 ms) for querying on other jsonb values within that metadata block.

* \d ubdrecord *

Time: 0.113s
summarist> \d ub
Did not find any relation named ub.
Time: 0.145s
summarist> \d ubdrecord
+----------------+-----------------------------+-------------+
| Column         | Type                        | Modifiers   |
|----------------+-----------------------------+-------------|
| id             | uuid                        |  not null   |
| senderduns     | character varying(256)      |             |
| sendername     | character varying(256)      |             |
| receiverduns   | character varying(256)      |             |
| receivername   | character varying(256)      |             |
| messagetype    | character varying(100)      |             |
| metadata       | jsonb                       |             |
| sourceid       | uuid                        |  not null   |
| sourcetype     | character varying(50)       |             |
| originaltime   | timestamp without time zone |  not null   |
| createdtime    | timestamp without time zone |  not null   |
| processingmode | integer                     |             |
| ubdversion     | character varying(20)       |             |
| unitofmeasure  | character varying(256)      |             |
| quantity       | numeric                     |             |
| active         | boolean                     |             |
| documentid     | character varying(256)      |             |
+----------------+-----------------------------+-------------+
Indexes:
    "pk_ubdrecord" PRIMARY KEY, btree (id)
    "createdtimeindex" btree (createdtime)
    "deliverynotelinenumberintegerindex" btree (jsonb_extract_path_text(metadata, VARIADIC ARRAY['deliveryNoteInfo'::text, 'deliveryNoteLineNumberInteger'::text]))
    "deliverynotenumberindex" btree (jsonb_extract_path_text(metadata, VARIADIC ARRAY['deliveryNoteInfo'::text, 'deliveryNoteNumber'::text]))
    "documentidindex" btree (documentid)
    "invoicenumberindex" btree (jsonb_extract_path_text(metadata, VARIADIC ARRAY['invoiceInfo'::text, 'invoiceNumber'::text]))
    "messagetypeindex" btree (messagetype)
    "polinenumberindex" btree (jsonb_extract_path_text(metadata, VARIADIC ARRAY['lineInfo'::text, 'poLineNumber'::text]))
    "polinenumberintegerindex" btree (jsonb_extract_path_text(metadata, VARIADIC ARRAY['lineInfo'::text, 'poLineNumberInteger'::text]))
    "ponumberindex" btree (jsonb_extract_path_text(metadata, VARIADIC ARRAY['lineInfo'::text, 'poNumber'::text]))
    "processingmodeindex" btree (processingmode)
    "receiverindex" btree (receiverduns)
    "salesorderlinenumberindex" btree (jsonb_extract_path_text(metadata, VARIADIC ARRAY['salesOrderInfo'::text, 'salesOrderLineNumber'::text]))
    "salesorderlinenumberintegerindex" btree (jsonb_extract_path_text(metadata, VARIADIC ARRAY['salesOrderInfo'::text, 'salesOrderLineNumberInteger'::text]))
    "salesordernumberindex" btree (jsonb_extract_path_text(metadata, VARIADIC ARRAY['salesOrderInfo'::text, 'salesOrderNumber'::text]))
    "senderindex" btree (senderduns)
    "shipmentnumberindex" btree (jsonb_extract_path_text(metadata, VARIADIC ARRAY['shipmentInfo'::text, 'shipmentNumber'::text])) 

* Similar query *

select "receivername", "unitofmeasure", "ubdversion", "processingmode", "metadata", "senderduns", "active", "createdtime", "sourcetype",
"documentid", "id", "sendername", "messagetype", "quantity", "sourceid", "originaltime", "receiverduns" from "ubdrecord"
where
(jsonb_extract_path_text("metadata",'lineInfo','poLineNumber') = '11124124124124')
order by "createdtime" desc limit 1;

* Similar query plan *

'Limit  (cost=3776.77..3776.77 rows=1 width=4046) (actual time=0.053..0.053 rows=0 loops=1)'
'  ->  Sort  (cost=3776.77..3779.20 rows=970 width=4046) (actual time=0.050..0.050 rows=0 loops=1)'
'        Sort Key: createdtime DESC'
'        Sort Method: quicksort  Memory: 25kB'
'        ->  Bitmap Heap Scan on ubdrecord  (cost=19.95..3771.92 rows=970 width=4046) (actual time=0.043..0.043 rows=0 loops=1)'
'              Recheck Cond: (jsonb_extract_path_text(metadata, VARIADIC '{lineInfo,poLineNumber}'::text[]) = '11124124124124'::text)'
'              ->  Bitmap Index Scan on polinenumberindex  (cost=0.00..19.70 rows=970 width=0) (actual time=0.039..0.039 rows=0 loops=1)'
'                    Index Cond: (jsonb_extract_path_text(metadata, VARIADIC '{lineInfo,poLineNumber}'::text[]) = '11124124124124'::text)'
'Planning time: 0.205 ms'
'Execution time: 0.091 ms'

The real head scratcher for me is why the first query doesn't seem to use the index/sortkey as this other one does. The index for both fields look the same to me.

5 added 5 characters in body
source | link
select "receivername", "unitofmeasure", "ubdversion", "processingmode", "metadata", "senderduns", "active", "createdtime", "sourcetype",
"documentid", "id", "sendername", "messagetype", "quantity", "sourceid", "originaltime", "receiverduns"
from "ubdrecord"
where
(jsonb_extract_path_text("metadata",'lineInfo','poLineNumberInteger') = '11123')
order by "createdtime"  
desc limit 1
select "receivername", "unitofmeasure", "ubdversion", "processingmode", "metadata", "senderduns", "active", "createdtime", "sourcetype",
"documentid", "id", "sendername", "messagetype", "quantity", "sourceid", "originaltime", "receiverduns"
from "ubdrecord"
where
(jsonb_extract_path_text("metadata",'lineInfo','poLineNumberInteger') = '11123')
order by "createdtime" desc limit 1
select "receivername", "unitofmeasure", "ubdversion", "processingmode", "metadata", "senderduns", "active", "createdtime", "sourcetype",
"documentid", "id", "sendername", "messagetype", "quantity", "sourceid", "originaltime", "receiverduns"
from "ubdrecord"
where
(jsonb_extract_path_text("metadata",'lineInfo','poLineNumberInteger') = '11123')
order by "createdtime" 
desc limit 1
4 added 5 characters in body
source | link
select "receivername", "unitofmeasure", "ubdversion", "processingmode", "metadata", "senderduns", "active", "createdtime", "sourcetype",
"documentid", "id", "sendername", "messagetype", "quantity", "sourceid", "originaltime", "receiverduns"  
from "ubdrecord"
where
(jsonb_extract_path_text("metadata",'lineInfo','poLineNumberInteger') = '11123')
order by "createdtime" desc limit 1
select "receivername", "unitofmeasure", "ubdversion", "processingmode", "metadata", "senderduns", "active", "createdtime", "sourcetype",
"documentid", "id", "sendername", "messagetype", "quantity", "sourceid", "originaltime", "receiverduns" from "ubdrecord"
where
(jsonb_extract_path_text("metadata",'lineInfo','poLineNumberInteger') = '11123')
order by "createdtime" desc limit 1
select "receivername", "unitofmeasure", "ubdversion", "processingmode", "metadata", "senderduns", "active", "createdtime", "sourcetype",
"documentid", "id", "sendername", "messagetype", "quantity", "sourceid", "originaltime", "receiverduns" 
from "ubdrecord"
where
(jsonb_extract_path_text("metadata",'lineInfo','poLineNumberInteger') = '11123')
order by "createdtime" desc limit 1
3 Added table and query details to assist
source | link
2 added 2 characters in body
source | link
1
source | link