We have the following indexes for our Client and Warehouse tables:
CREATE UNIQUE INDEX "idx_49921_PK__PERSONA" ON public.persona USING btree ("ID");
CREATE INDEX persona_full_name ON public.persona USING gin (((("NOMBREPERSONA" || ' '::text) || "APELLIDOPERSONA")) gin_trgm_ops, "EMAILPERSONA" gin_trgm_ops);
CREATE UNIQUE INDEX "idx_49740_PK_BODEGA" ON public.warehouse USING btree (id);
CREATE INDEX warehouse_name ON public.warehouse USING gin (name gin_trgm_ops);
And if I do the following query:
explain analyze select *
from
package as "Package"
inner join persona as "Persona" on
"Package"."customer_id" = "Persona"."ID"
left outer join "warehouse" as "Warehouse" on
"Package"."IDBODEGA" = "Warehouse"."id"
where
"Warehouse"."name" ILIKE '%test name%' or
"Persona"."EMAILPERSONA" ILIKE '%test name%' or
"Persona"."NOMBREPERSONA" || ' ' || "Persona"."APELLIDOPERSONA" ILIKE '%test name%'
It returns this:
Gather (cost=19659.56..66121.41 rows=250 width=261) (actual time=1526.310..1680.903 rows=0 loops=1)
Workers Planned: 2
Workers Launched: 2
-> Hash Left Join (cost=18659.56..65096.41 rows=104 width=261) (actual time=1502.623..1502.714 rows=0 loops=3)
Hash Cond: ("Package"."IDBODEGA" = "Warehouse".id)
Filter: (("Warehouse".name ~~* '%test name%'::text) OR ("Persona"."EMAILPERSONA" ~~* '%test name%'::text) OR ((("Persona"."NOMBREPERSONA" || ' '::text) || "Persona"."APELLIDOPERSONA") ~~* '%test name%'::text))
Rows Removed by Filter: 282885
-> Parallel Hash Join (cost=18530.66..64038.97 rows=353608 width=196) (actual time=309.438..540.409 rows=282885 loops=3)
Hash Cond: ("Package".customer_id = "Persona"."ID")
-> Parallel Seq Scan on package "Package" (cost=0.00..26217.08 rows=353608 width=154) (actual time=0.284..103.231 rows=282886 loops=3)
-> Parallel Hash (cost=12532.18..12532.18 rows=281718 width=46) (actual time=91.819..91.819 rows=225375 loops=3)
Buckets: 65536 Batches: 16 Memory Usage: 3968kB
-> Parallel Seq Scan on persona "Persona" (cost=0.00..12532.18 rows=281718 width=46) (actual time=0.103..35.280 rows=225375 loops=3)
-> Hash (cost=79.51..79.51 rows=3951 width=37) (actual time=1.216..1.216 rows=3951 loops=3)
Buckets: 4096 Batches: 1 Memory Usage: 299kB
-> Seq Scan on warehouse "Warehouse" (cost=0.00..79.51 rows=3951 width=37) (actual time=0.213..0.700 rows=3951 loops=3)
Planning Time: 0.998 ms
Execution Time: 1681.071 ms
So it’s not using either index.
But if I do this condition alone (while keeping the rest of the query):
where "Warehouse"."name" ILIKE '%test name%'
It returns this:
Gather (cost=1064.45..28219.84 rows=34 width=261) (actual time=37.400..40.770 rows=0 loops=1)
Workers Planned: 2
Workers Launched: 2
-> Nested Loop (cost=64.45..27216.44 rows=14 width=261) (actual time=0.318..0.319 rows=0 loops=3)
-> Hash Join (cost=64.03..27209.48 rows=14 width=191) (actual time=0.317..0.318 rows=0 loops=3)
Hash Cond: ("Package"."IDBODEGA" = "Warehouse".id)
-> Parallel Seq Scan on package "Package" (cost=0.00..26217.08 rows=353608 width=154) (actual time=0.196..0.196 rows=1 loops=3)
-> Hash (cost=64.02..64.02 rows=1 width=37) (actual time=0.062..0.062 rows=0 loops=3)
Buckets: 1024 Batches: 1 Memory Usage: 8kB
-> Bitmap Heap Scan on warehouse "Warehouse" (cost=60.00..64.02 rows=1 width=37) (actual time=0.061..0.062 rows=0 loops=3)
Recheck Cond: (name ~~* '%test name%'::text)
-> Bitmap Index Scan on warehouse_name (cost=0.00..60.00 rows=1 width=0) (actual time=0.060..0.060 rows=0 loops=3)
Index Cond: (name ~~* '%test name%'::text)
-> Index Scan using "idx_49921_PK__PERSONA" on persona "Persona" (cost=0.42..0.50 rows=1 width=37) (never executed)
Index Cond: ("ID" = "Package".customer_id)
Planning Time: 0.589 ms
Execution Time: 40.821 ms
It uses the warehouse_name index, and the query takes much less time.
Similarly, if I just do:
where
"Persona"."EMAILPERSONA" ILIKE '%test name%'
OR "Persona"."NOMBREPERSONA" || ' ' || "Persona"."APELLIDOPERSONA" ILIKE '%test name%'
It returns:
Gather (cost=1666.98..28849.55 rows=167 width=261) (actual time=38.049..41.531 rows=0 loops=1)
Workers Planned: 2
Workers Launched: 2
-> Nested Loop Left Join (cost=666.98..27832.85 rows=70 width=261) (actual time=1.846..1.848 rows=0 loops=3)
-> Hash Join (cost=666.70..27812.01 rows=70 width=187) (actual time=1.845..1.847 rows=0 loops=3)
Hash Cond: ("Package".customer_id = "Persona"."ID")
-> Parallel Seq Scan on package "Package" (cost=0.00..26217.08 rows=353608 width=154) (actual time=0.171..0.171 rows=1 loops=3)
-> Hash (cost=665.04..665.04 rows=133 width=37) (actual time=1.621..1.622 rows=0 loops=3)
Buckets: 1024 Batches: 1 Memory Usage: 8kB
-> Bitmap Heap Scan on persona "Persona" (cost=177.06..665.04 rows=133 width=37) (actual time=1.621..1.621 rows=0 loops=3)
Recheck Cond: (("EMAILPERSONA" ~~* '%test name%'::text) OR ((("NOMBREPERSONA" || ' '::text) || "APELLIDOPERSONA") ~~* '%test name%'::text))
-> BitmapOr (cost=177.06..177.06 rows=133 width=0) (actual time=1.619..1.620 rows=0 loops=3)
-> Bitmap Index Scan on persona_full_name (cost=0.00..88.49 rows=65 width=0) (actual time=0.981..0.981 rows=0 loops=3)
Index Cond: ("EMAILPERSONA" ~~* '%test name%'::text)
-> Bitmap Index Scan on persona_full_name (cost=0.00..88.51 rows=68 width=0) (actual time=0.637..0.637 rows=0 loops=3)
Index Cond: ((("NOMBREPERSONA" || ' '::text) || "APELLIDOPERSONA") ~~* '%test name%'::text)
-> Index Scan using "idx_49740_PK_BODEGA" on warehouse "Warehouse" (cost=0.28..0.30 rows=1 width=37) (never executed)
Index Cond: (id = "Package"."IDBODEGA")
Planning Time: 0.541 ms
Execution Time: 41.705 ms
So it also uses the index.
The problem is when I try to use both indexes at the same time.
Is there anything I can do here? The original query is much longer and takes almost 3 seconds, I made this query trying to isolate this issue.