[GENERAL] Problem with index when using function

From: D. Dante Lorenso <dante(at)lorenso.com>
Date: Mon Dec 31 2007 - 23:15:50 EST

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

Received on Mon Dec 31 23:20:52 2007