I have a Shipment
table with some basic data about a shipment and a ShipmentItem
table which contains additional attributes about that shipment with a foreignKey
on the Shipment
table's primaryKey. The Shipment
to ShipmentItem
table is OneToMany
relationship.
We have a requirement to include a text search option that takes a given inputted text string and searches over 2 of the (make) Shipment
's columns in addition to three specific types
of the ShipmentItem
name column. This is my current query:
select *
from Shipment shipment
where shipment.deliveryRequestedDate >= '2019-06-09T00:00:00Z'
and shipment.deliveryRequestedDate <= '2019-12-06T23:59:59Z'
and (
shipment.identifierkeyvalues = '12345'
or shipment.carrierReferenceNumber = '12345'
or shipment.uuid in (
select shipmentItem.resultId
from ShipmentItem shipmentItem
where (
shipmentItem.type in (
'poNumber', 'deliveryNoteNumber', 'salesOrderNumber'
)
)
and shipmentItem.name = '12345'
and shipmentItem.deliveryRequestedDate >= '2019-06-09T00:00:00Z'
and shipmentItem.deliveryRequestedDate <= '2019-12-06T23:59:59Z'
)
)
limit 25
The issue I've discovered is the combination of putting a subQuery as one of the or
conditions is causing a MAJOR performance problem (even though the subQuery itself returns fast by utilizing a type_name_deliveryRequestedDate
index on that table. Although we have multiple indexes on the main table (identifierKeyValues
, carrierReferenceNumber
, and even an index over all three Shipment columns queried over, it will only use the deliveryRequestedDate
index which is extremely inefficient since the range is so large for this query.
Converting this into a JOIN has seemed to cause the same behavior. I'm just not quite sure what the best approach is at this point. We have a Java Persistence API layer above this query so would like to avoid making any major changes to the data model if possible but not sure what the best approach is. Any ideas would be greatly appreciated!
EXPLAIN PLAN:
Limit (cost=110.61..209.98 rows=25 width=1370) (actual time=119503.030..124034.809 rows=1 loops=1)
-> Index Scan using shipment_deliveryrequesteddate_idx on shipment shipment (cost=110.61..890840.18 rows=224084 width=1370) (actual time=119503.027..124034.805 rows=1 loops=1)
Index Cond: ((deliveryrequesteddate >= '2019-06-09 00:00:00'::timestamp without time zone) AND (deliveryrequesteddate <= '2019-12-06 23:59:59'::timestamp without time zone))
Filter: ((identifierkeyvalues = '12345'::text) OR (carrierreferencenumber = '12345'::text) OR (hashed SubPlan 1))
Rows Removed by Filter: 496784
SubPlan 1
-> Index Scan using "type_name_deliveryRequestedDate" on resultitem shipmentitem (cost=0.56..110.11 rows=24 width=16) (actual time=10.706..16.416 rows=1 loops=1)
Index Cond: ((type = ANY ('{poNumber,deliveryNoteNumber,salesOrderNumber}'::text[])) AND (name = '12345'::text) AND (deliveryrequesteddate >= '2019-06-09 00:00:00'::timestamp without time zone) AND (deliveryrequesteddate <= '2019-12-06 23:59:59'::timestamp without time zone))
Planning time: 3.175 ms
Execution time: 124035.006 ms
EXPLAIN PLAN removing the subquery -- Why does it use a completely different index?
Limit (cost=9.51..273.71 rows=6 width=1370) (actual time=0.052..0.053 rows=0 loops=1)
-> Bitmap Heap Scan on shipment shipment (cost=9.51..273.71 rows=6 width=1370) (actual time=0.051..0.051 rows=0 loops=1)
Recheck Cond: (((identifierkeyvalues = '12345'::text) AND (deliveryrequesteddate >= '2019-06-09 00:00:00'::timestamp without time zone) AND (deliveryrequesteddate <= '2019-12-06 23:59:59'::timestamp without time zone)) OR (carrierreferencenumber = '12345'::text))
Filter: ((deliveryrequesteddate >= '2019-06-09 00:00:00'::timestamp without time zone) AND (deliveryrequesteddate <= '2019-12-06 23:59:59'::timestamp without time zone))
Rows Removed by Filter: 2
Heap Blocks: exact=2
-> BitmapOr (cost=9.51..9.51 rows=66 width=0) (actual time=0.041..0.041 rows=0 loops=1)
-> Bitmap Index Scan on shipment_identifierkeyvalues_idx (cost=0.00..4.61 rows=4 width=0) (actual time=0.023..0.024 rows=0 loops=1)
Index Cond: ((identifierkeyvalues = '12345'::text) AND (deliveryrequesteddate >= '2019-06-09 00:00:00'::timestamp without time zone) AND (deliveryrequesteddate <= '2019-12-06 23:59:59'::timestamp without time zone))
-> Bitmap Index Scan on shipment_carrierreferencenumber_idx (cost=0.00..4.90 rows=62 width=0) (actual time=0.016..0.016 rows=2 loops=1)
Index Cond: (carrierreferencenumber = '12345'::text)
Planning time: 1.668 ms
Execution time: 0.116 ms
LIMIT
?