Problem with index when using function

Поиск
Список
Период
Сортировка
От D. Dante Lorenso
Тема Problem with index when using function
Дата
Msg-id 4779BE76.8070204@lorenso.com
обсуждение исходный текст
Ответы Re: Problem with index when using function  ("D. Dante Lorenso" <dante@lorenso.com>)
Список pgsql-general
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


В списке pgsql-general по дате отправления:

Предыдущее
От: dvanatta
Дата:
Сообщение: CREATE TYPE and pgAdmin III
Следующее
От: "D. Dante Lorenso"
Дата:
Сообщение: Re: Problem with index when using function