Reputation: 35
Postgres 9.5
I have a table having one of columns jsonb;
CREATE TABLE public.test
(
objectstate jsonb
)
and index on it:
CREATE INDEX "test.type"
ON public.test
USING btree
((objectstate ->> 'type'::text) COLLATE pg_catalog."default");
I also have function returning depended types.... its more complex, so i'll give an example....
CREATE OR REPLACE FUNCTION testfunc(sxtype text)
RETURNS text AS
$BODY$
BEGIN
return '{type1, type2}';
END;
$BODY$
LANGUAGE plpgsql VOLATILE
COST 100;
now what I've got:
select testfunc('type1')
gives me '{type1, type2}'
Next syntax works well and DOES uses index:
select * from test where objectstate->>'type' = ANY('{type1, type2}'::text[])
But once I'm trying to combine them, index not used
select * from test
where objectstate->>'type' = ANY((select testfunc('type1'))::text[])
Wierd thing is next query DOES USE sytax again! (but I cant use this workaround everywhere)
select * from test
where objectstate->>'type' = ANY((select testfunc('type1'))::text[])
order by objectstate->>'type'
explain analyze gives me:
"Seq Scan on test (cost=0.26..530872.27 rows=2238634 width=743) (actual time=1107.155..7992.825 rows=129 loops=1)"
" Filter: ((test ->> 'type'::text) = ANY (($0)::text[]))"
" Rows Removed by Filter: 4063727"
" InitPlan 1 (returns $0)"
" -> Result (cost=0.00..0.26 rows=1 width=0) (actual time=0.718..0.718 rows=1 loops=1)"
"Planning time: 0.319 ms"
"Execution time: 7992.870 ms"
and when order applyed:
"Index Scan using "test.type" on test (cost=0.70..545058.44 rows=2238634 width=743) (actual time=0.645..0.740 rows=129 loops=1)"
" Index Cond: ((objectstate ->> 'type'::text) = ANY (($0)::text[]))"
" InitPlan 1 (returns $0)"
" -> Result (cost=0.00..0.26 rows=1 width=0) (actual time=0.617..0.617 rows=1 loops=1)"
"Planning time: 0.300 ms"
"Execution time: 0.782 ms"
Any Ideas how can I force postgres to use Index without applying order by?
Upvotes: 2
Views: 110
Reputation: 1023
May be it is not an answer, but seems you may change the function definition from VOLATILE
to IMMUTABLE
with
CREATE OR REPLACE FUNCTION testfunc(sxtype text)
RETURNS text AS
$BODY$
BEGIN
return '{type1, type2}';
END;
$BODY$
LANGUAGE plpgsql IMMUTABLE
COST 100;
With VOLATILE function Postgres does not apply optimizations due to VOLATILE functions may change data and result of the function is not predictable. More at documentation https://www.postgresql.org/docs/9.5/static/sql-createfunction.html
Upvotes: 1