CODEWITHSUNDEEP
postgresqlindexing
ivp
ivp

Reputation: 35

postgres index not used

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

Answers (1)

Semjon
Semjon

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

Related Questions