Re: [GENERAL] Problem with index when using function

Thanks if you started to look into this, but nevermind. I figured it out. Turns out I only needed to mark the function as STABLE:

http://www.postgresql.org/docs/8.0/static/xfunc-volatility.html

"A STABLE function cannot modify the database and is guaranteed to return the same results given the same arguments for all calls within a single surrounding query. This category allows the optimizer to optimize away multiple calls of the function within a single query. In particular, it is safe to use an expression containing such a function in an index scan condition. (Since an index scan will evaluate the comparison value only once, not once at each row, it is not valid to use a VOLATILE function in an index scan condition.)"

• Dante D. Dante Lorenso wrote:
  > All,
  >
  > I have a weird situation where my index IS used when I use a query that
  > hard-codes a value but it does NOT use the index when the value is
  > returned from a PGSQL function:
  >
  > ======================================================================
  > DOES NOT WORK
  > ======================================================================
  >
  > svcprod=# EXPLAIN SELECT
  > COALESCE(SUM(start_count), 0) AS start_count,
  > COALESCE(SUM(end_count), 0) AS end_count,
  > COALESCE(SUM(total_playtime), 0) AS total_playtime
  > FROM audio_file_stats
  > WHERE afile_id = num2id(1173, 1075, 'audio_file', 'audio_id',
  > 'afile_id')::bigint;
  >
  > QUERY PLAN
  > ----------------------------------------------------------------------
  > Aggregate (cost=118677.35..118677.36 rows=1 width=19)
  > -> Seq Scan on audio_file_stats (cost=0.00..118675.33 rows=268
  > width=19)
  > Filter: (afile_id = num2id(1173::bigint, 1075::bigint,
  > 'audio_file'::character varying, 'audio_id'::character varying,
  > 'afile_id'::character varying))
  > (3 rows)
  >
  > ======================================================================
  > WORKS
  > ======================================================================
  >
  > byoaudio=# EXPLAIN SELECT
  > COALESCE(SUM(start_count), 0) AS start_count,
  > COALESCE(SUM(end_count), 0) AS end_count,
  > COALESCE(SUM(total_playtime), 0) AS total_playtime
  > FROM audio_file_stats
  > WHERE afile_id = 48702;
  >
  > QUERY PLAN
  > ----------------------------------------------------------------------
  > Aggregate (cost=672.69..672.70 rows=1 width=19)
  > -> Index Scan using audio_file_stats_idx_afile_id on
  > audio_file_stats (cost=0.00..670.73 rows=261 width=19)
  > Index Cond: (afile_id = 48702)
  > (3 rows)
  >
  > ======================================================================
  >
  > The function I use is defined as using returning a BIGINT which is the
  > same datatype as is used by the index:
  >
  > FUNCTION "public"."num2id" (in_acct_id bigint, in_value bigint,
  > in_table_name varchar, in_input_column varchar,
  > in_output_column varchar) RETURNS bigint
  >
  > Can someone help explain what is being done wrong here? I'm using 8.2.4.
  >
  > -- Dante
  >
  >
  > ---------------------------(end of broadcast)---------------------------
  > TIP 1: if posting/reading through Usenet, please send an appropriate
  > subscribe-nomail command to majordomo@postgresql.org so that your
  > message can get through to the mailing list cleanly
  >
  

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
       choose an index scan if your joining column's datatypes do not
       match