postgres index not used

Question

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?

Semjon · Accepted Answer · 2016-10-21 12:48:18Z

1

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

answered Oct 21, 2016 at 12:48

Semjon

1,0237 silver badges17 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

ivp Over a year ago

It seems like I found solution with your hint..... select * from test where objectstate->>'type' = ANY(testfunc2('system-protodefinition')::text[]) DOES use index..... Thank you!

Collectives™ on Stack Overflow

postgres index not used

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related