0042364: [Performance] Create index to improve time of query to check if there are services for a product

Description

The query makes a bitmapAnd Index scan and looses a lot of time in the re-check condition in the Bitmap Heap scan making a double partial double column index accelerates this a lot and avoids the re-check.

Plan without index:
https://explain.depesz.com/s/8To9 [^]

Plan with index:
https://explain.depesz.com/s/c6B [^]

Steps To Reproduce

Add a product to a ticket in WebPOS

Proposed Solution

create index em_obpos_product_type_org on m_product (ad_org_id, producttype) where producttype = 'S' and islinkedtoproduct = 'Y';

The index is really small and affects very few rows due to producttype = 'S' and islinkedtoproduct = 'Y' clause

Tags

No tags attached.

Attached Files

Relationships [ Relation Graph ] [ Dependency Graph ]

Notes
There are no notes attached to this issue.

Issue History
Date Modified	Username	Field	Change
2019-11-21 16:56	ioritzCia	New Issue
2019-11-21 16:56	ioritzCia	Assigned To	=> Retail
2019-11-21 16:56	ioritzCia	Triggers an Emergency Pack	=> No
2020-03-02 19:51	rqueralta	Assigned To	Retail => rqueralta
2020-03-02 19:51	rqueralta	Status	new => scheduled
2020-03-02 19:53	rqueralta	Status	scheduled => acknowledged
2020-03-02 19:53	rqueralta	Assigned To	rqueralta => Retail